Основы работы с электронными таблицами Microsoft Excel

      Иногда в формуле  требуется указать адрес конкретной  ячейки, который не должен меняться  при перемещении ячейки с формулой. Для этого используется абсолютная  адресация ячеек. Чтобы адрес ячейки не менялся при копировании формулы, перед адресом строки или столбца нужно поставить символ «$». Если мы запишем нашу формулу так: =СУММ($А$1:$А$3), при переносе этой формулы в любую другую ячейку ссылки на ячейки не изменятся. Естественно можно сделать абсолютным только часть адреса, например, $А1. В этом случае при переносе будет меняться только адрес строки, а адрес столбца будет неизменным.

Для удобства работы с таблицей, можно  присвоить ячейке или группе ячеек  собственное имя.

 Это имя может быть произвольным, но с некоторыми ограничениями. Если ячейке присвоено имя, то адресовать эту ячейку можно как обычным способом, так и по этому имени. Имена можно присваивать ячейкам, диапазонам, формулам и константам. Имена могут заменять собою ссылки, что существенно облегчает задачу адресации в электронных таблицах. Преимущество от использования имен заключается в том, что формулы благодаря именам становятся более наглядными и понятными, например, формула =СУММ(доходы_за_1_квартол) лучше воспринимается, чем =СУММ(А1:В1).

Программа предлагает вам 12 категорий  функций. Если вам нужны математические расчеты, выбирайте категорию Математические. Среди функций этой группы вы найдете  тригонометрические функции, округление, вычисление степеней и логарифмов, и так далее. Часто

при работе с таблицами используют текстовые функции. Чтобы выбрать  функцию из этой группы, выберите категорию  Текстовые. Среди текстовых функций  есть функции поиска строк, изменения  регистра символов, и так далее. Для  сравнения различных величин используются логические функции. Выберите категорию Логические, и вы сможете выбрать функции логического сложения, умножения и функции проверки условий. Если вы хотите использовать функции работы с датами, выбирайте категорию Дата и время. С помощью функций этой категории вы можете узнать текущую дату, время, день недели и так далее. Много полезных функций находится в категории Финансовые. Если вы не представляете себе, к какой области относится нужная вам функция, выберите категорию Полный алфавитный перечень.

     После того, как функция  найдена, щелкните на ней мышью  в списке Выберите функцию.  Обратите внимание, что ниже списка  отображается синтаксис вы

бранной функции, то есть правило ее написания и набор аргументов. Еще ниже находится описание функции, помогающее определить ее назначение. Нажмите кнопку ОК, и первый диалог мастера будет закрыт. Появится следующий диалог, который предназначен для конкретизации параметров функции, определенной ранее. Количество и вид параметров зависят от типа выбранной функции. Может быть один параметр, а может быть несколько.

 Если вы допустили ошибку  при вводе или редактировании  формулы, программа сообщит вам  об этом. Если в ячейке с  формулой отображается текст,  начинающийся с символа «#»,  значит, в формуле есть ошибка. Если в ячейке вы увидите #####, ширина ячейки недостаточна для отображения результата. Чтобы устранить ошибку, нужно расширить ячейку или изменить формат числа. Когда в ячейке появляется сообщение #ИМЯ?, значит программа не распознала имя функции или имя ячейки, используемое в формуле. Сообщение #ЗНАЧ! говорит о попытке некорректного использования функции.  Сообщение #ЧИСЛО! появляется, если возникли проблемы с представлением или с использованием чисел. Не исключено, что в функции с числовым аргументом используется аргумент нечислового формата. Возможно также, что в ячейку введена формула,

возвращающая слишком большое  значение. Если в ячейке появилось  сообщение #ССЫЛКА!  значит, есть проблема с адресацией ячеек, участвующих в формуле.

Возможно, что формула содержит ссылку на ячейку, которая уже удалена, или ссылку на ячейку, в которую скопировано содержимое других ячеек. Сообщение #ДЕЛ/0! возникает при попытке деления на ноль. Такая ситуация чаще возникает не из-за того, что в ячейку записано явное деление на ноль, а как следствие использования в качестве делителя ссылки на пустую ячейку или ячейку, содержащую нулевое значение. Сообщение #ПУСТО! говорит о появлении ошибки в случае задания в ссылке пустого множества ячеек. Возможно, что вы ошиблись в определении пересечения двух диапазонов. Сообщение #Н/Д говорит о наличии неопределенных данных.

        Удобной возможностью  программы является добавление  к ячейкам различных комментариев. При этом ячейка будет содержать  особую отметку, а при выборе  этой ячейки на экране будет отображаться окно с комментарием.

В состав программы входит очень  много встроенных функций, и бывает сложно разобраться, что делает каждая из них. Так, в мастере ввода функций  вы найдете 12 категорий функций: математические, функции сравнения, текстовые функции, функции работы с датой и временем, функции работы со ссылками и так далее.

Очень часто при составлении  таблиц используют математические функции.

Например, функция СУММ(число1; число2;...) используется для суммирования содержимого указанного интервала ячеек. Максимально в функции может быть до 30 аргументов. В качестве аргументов этой функции используются числа, адреса ячеек, диапазон ячеек. При этом если в диапазоне ячеек встречаются пустые ячейки, ячейки содержащие текст, который нельзя преобразовать в число, при вычислении суммы они игнорируются. Если вам нужно суммировать ячейки по какому-либо условию, можно воспользоваться функцией  СУММЕСЛИ(диапазон; критерий; диапазон суммирования). Аргумент диапазон определяет диапазон вычисляемых ячеек. Аргумент критерий представляет собой число, выражение или текст, по которому идет сравнение результата суммирования ячеек, определяемых первым аргументом. Аргумент диапазон суммирования определяет адреса ячеек, сумма значений в которых будет вычисляться, если сумма значений в ячейках первого аргумента удовлетворяет критерию.

Иногда бывает нужно использовать целую часть дробного числа. В  этом случае можно воспользоваться функцией ЦЕЛОЕ(число), где число — это аргумент,  определяющий число, которое нужно округлить. В качестве аргумента функции может выступать численная константа или ссылка на ячейку, содержащую числовой результат. Обратите внимание, что результатом выполнения функции будет округление до ближайшего меньшего целого числа, например, результатом  формулы =ЦЕЛОЕ(3,3) будет число J, а результатом =ЦЕЛОЕ(3,99) также будет

число 3. Так, что правильнее назвать  действие данной функции не округлением, а взятием ближайшего меньшего целого. Если вам нужно изменить разрядность числа, воспользуйтесь функцией округления ОКРУГЛ(число; число разрядов), которая округляет число до указанного

количества десятичных разрядов. Аргумент число содержит округляемое число, а аргумент число разрядов - количество десятичных разрядов, до которого нужно округлить число. Если число разрядов больше нуля, число округляется до указанного количества десятичных разрядов справа от десятичной запятой. Если число разрядов равно нулю, число округляется до ближайшего целого, а если число разрядов меньше нуля, число округляется слева от десятичной запятой.

Для округления чисел также можно  воспользоваться функцией НЕЧЁТ(число), которая возвращает число, округленное до ближайшего нечетного целого. Аргумент число — это округляемое значение. Если аргумент функции не является числом, то функция возвращает значение ошибки #ЗНАЧ!. Независимо от знака числа, округление всегда производится с избытком. Если число является

нечетным целым, то округления не происходит.

Для нахождения абсолютной величины числа, то есть числа без знака, можно  использовать функцию АВS(число), где число — действительное число, абсолютную величину которого требуется найти.

С помощью функции ОСТАТ(делимое; делитель) вы можете определить остаток при делении чисел. Функция возвращает остаток от деления делимого на делитель. Результат имеет такой же знак, как и делитель. Аргумент делимое — это число, остаток от деления которого определяется, а делитель — число, на которое нужно разделить. Если делитель равен нулю, функция возвращает значение

ошибки #ДЕЛ/0!.

Иногда бывает нужно преобразовать  арабские цифры в римские. Для  этого используйте функцию РИМСКОЕ(число; форма), где число - преобразуемое число в арабской записи, а форма — число от нуля до четырех, задающее нужную форму римской записи чисел. Чем меньше число, определяющее форму, тем более упрощенной будет римская запись числа.

      Для вычисления  сумм в столбцах списка в  зависимости от разных условий  следует применять функцию БДСУММ(база данных; поле; критерий). Данная функция суммирует числа в столбце списка или базы данных, которые удовлетворяют заданным условиям. Аргумент база данных представляет собой интервал ячеек, в котором расположен список. Базой данных называют список связанных данных, в котором строки данных являются записями, а столбцы — полями. Верхняя строка списка содержит названия всех столбцов. Аргумент поле определяет столбец, используемый функцией. Аргумент может быть задан в виде текста с названием столбца в двойных кавычках, например «Доход» или «Расход», или как число, определяющее номер столбца в списке, начиная с единицы. Аргумент критерий определяет интервал ячеек, который содержит задаваемые условия.

Любой интервал, который содержит, по крайней мере, одно название столбца и, по крайней мере, одну ячейку под названием столбца с условием, может быть использован в качестве аргумента критерий.

  Кроме математических функций,  часто используются функции для  работы с текстом. Если вам  нужно объединить несколько текстовых строк в одну строку, следует воспользоваться функцией СЦЕПИТЬ(текст1;текст2;...), где текст!, текст2, и так далее — от одного до 30 текстовых строк или адресов ячеек, содержащих текстовые строки. Например, если в ячейки A3 и СЗ вы введете строки Баланс и 2002, тогда результатом функции =СЦЕПИТЬ(АЗ;СЗ), будет строка Баланс 2002.

      Если вам нужно  преобразовать число в текст,  следует воспользоваться функцией ТЕКСТ(значенш; формат), которая преобразует значение в текст в заданном числовом формате. Аргумент значение определяет число, которое будет преобразовываться, и должен быть либо значением, либо формулой, либо ссылкой на ячейку, содержащую числовое значение. Аргумент формат — это один из числовых форматов, выбираемый при форматировании ячейки. Мы уже рассматривали числовые форматы, поэтому не будем на них останавливаться. Отметим лишь, что формат задается в виде текстовой строки.

      Если вам нужно  узнать, сколько знаков в строке, воспользуйтесь функцией ДЛСТР(текст), которая возвращает количество знаков в текстовой строке. Аргумент текст — это текстовая строка, длину которой нужно вычислить. Обращаем внимание, что пробелы в тексте также учитываются при определении длины.

         Для сравнения  текстовых строк используется  функция СОВПАД(текст1; текст2), которая сравнивает две строки текста и возвращает значение ИСТИНА, если они в точности совпадают, или ЛОЖЬ в противном случае. Функция учитывает регистр, но игнорирует различия в форматировании. Аргумент текст1 — это первая текстовая строка, а текст2 — вторая текстовая строка.

      Если нужно найти  какие-либо символы в текстовой  строке, можно использовать функцию  ПОИСК(искомый текст; текст для  поиска; начальная позиция). Эта функция  просматривает строку слева направо  и возвращает номер символа,  начиная с которого в тексте есть искомая фраза.

Если искомый текст не найден, возвращается значение ошибки #ЗНАЧ!.

   Для сравнения различных  данных в ячейках используют  логические функции. Например, если  вам нужно проверить содержимое  ячеек и в зависимости от этого содержимого выполнить различные действия, удобно использовать функцию ЕСЛИ(логическое выражение; значение если истина; значение если ложь). Эта функция возвращает одно значение, если заданное условие выполняется, и другое значение, если условие не выполняется.

Кроме функции ЕСЛИ, в программе есть еще ряд условных функций, например СУММЕСЛИ или СЧЁТЕСЛИ.

В программе есть ряд функций, облегчающих  работу со ссылками. С помощью встроенных функций можно узнать номер строки или столбца, на которые указывает ссылка и другие значения. Функция СТРОКА(ссылка) возвращает номер строки, определяемой ссылкой. Аргумент ссылка указывает ячейку или интервал ячеек, для которых определяется номер строки. Если ссылка опущена, то предполагается, что это ссылка на ячейку, в которой находится сама функция.

Аналогичная функция есть и для  столбцов — СТОЛБЕЦ (ссылка). Эта функция возвращает номер столбца по заданной ссылке. Единственный аргумент  ссылка описывает адрес ячейки или интервал ячеек, для которых определяется номер столбца. Если ссылка опущена, предполагается, что это ссылка на ячейку, в которой находится сама функция.

В программе есть ряд функций  для работы со списками.

Списками называются таблицы, которые  содержат связанные между собой  данные. Если нужно найти какое-либо значение в столбце списка и выдать значение из указанной ячейки в найденной строке, используйте функцию ВПР(искомое значение; таблица; номер столбца; интервальный просмотр). Эта функция ищет значение в крайнем левом столбце таблицы и возвращает значение в той же строке из указанного столбца таблицы.

Аналогично функции ВПР работает функция ГПР. Она ищет значение в верхней строке списка и возвращает значение в том же столбце из заданной строки списка. Функция используется, когда сравниваемые значения расположены в верхней строке списка, а возвращаемые значения расположены на несколько строк ниже. Если сравниваемые значения расположены в столбце слева от искомых данных, то следует использовать функцию ВПР. Назначение аргументов этих функций одинаково.