Лекция: Вычисления в Excel
Выполнение всех математических расчетов выполняется с помощью формул. В формулах используются адреса ячеек, состоящие из буквы столбца и номера строки, например, А2, В7, С34 и т. д. При вводе формул надо соблюдать следующие правила:
· все формулы начинаются со знака «=»;
· в адресе ячеек используют только латинские буквы;
· в адресе ячеек можно указывать как одну ячейку, так и диапазон, при этом используются символы «:» – диапазона и «;» – объединения;
· арифметические операции обозначаются символами: «*» – умножение, «/» – деление, «+» – сложение, «–» – вычитание, «^» – возведение в степень;
· для отделения целой части числа от дробной используют запятую;
· аргументы функций разделяются между собой символом «;».
Например:
= A2*2,2+СУММА(С1: С10)
= МАКС(A1:D4;F1:H4)
Формулы можно копировать и перемещать обычным способом, при этом автоматически меняются адреса ячеек.
Ссылки на ячейки в формулах. Существуют относительные, абсолютные и смешанные ссылки. По умолчанию для указания адреса ячейки в Excel применяется относительная ссылка. При перемещении или копировании формулы относительная ссылка изменяется, исходя из той позиции, куда переносится формула.
При необходимости ввода в формулу значения из фиксированной ячейки используется абсолютная ссылка. Абсолютные ссылки обозначаются знаком доллара «$» перед буквой столбца или (и) цифрой строки, которые должны оставаться неизменными. Например, запись $А$4 означает, что независимо от того, где будет находиться формула, она всегда будет искать значение, помещенное в ячейку А4.
Ссылки могут быть также смешанными. Если нужно зафиксировать столбец, то знак $ ставится перед буквой столбца, например, $А7. Если необходимо зафиксировать строку, то знак $ ставится перед номером строки, например, А$7.
Кроме ссылок на ячейки текущего рабочего листа, в формулах и функциях могут быть ссылки на ячейки других рабочих листов текущей рабочей книги (внутренние ссылки) или другой рабочей книги (внешние ссылки). Ссылка на данные другого рабочего листа имеет вид:
Имя рабочего листа! Имя ячейки
Например: Лист1!A1
Объемные ссылки – это ссылки на ячейки диапазона листов в книге. Например, формула: =СУММ(Лист1: Лист6! В1: В20) вычисляет сумму всех значений из диапазонов В1: В20, расположенных на шести листах.
Если в формуле нужно использовать данные из другой рабочей книги, необходимо, чтобы оба файла были предварительно открыты. Общий вид ссылки в данном случае такой:
[Имя книги]Имя рабочего листа! Имя ячейки
Например: [Книга2]Лист2!D5
Если нужно обратиться к ячейке не открытого файла, то в ссылке необходимо указать полный путь доступа к папке, где хранится книга:
“C:\Имя папки\[Имя книги.xls]Лист5”!$A$3
Изменение типа ссылок.Чтобы изменить ссылку с относительнойна абсолютную или на смешенную, нужно нажать клавишу F4.Причемкаждое очередное нажатие изменяет тип ссылки.
Использование встроенных функций. Для выполнения вычислений в Excel можно использовать встроенные функции: математические, логические, финансовые, текстовые, даты и времени и др. Чтобы вставить встроенную функцию, надо активизировать ячейку и нажать кнопку (Вставка функции) на панели Стандартная. В появившемся окне выбрать категорию функции, имя и щелкнуть кнопку Ок. Выбранная функция внесется в активную ячейку и откроется следующее диалоговое окно, в котором надо ввести диапазон ячеек (или выделить диапазон ячеек с помощью мыши) и нажать Ок.
Одна из самых простых и часто используемых в Excel функций – это функция автоматического суммирования. Кнопка Автосумма находится на панели Стандартная.
Логические функции. Предназначены для проверки выполнения условия или для проверки нескольких условий. К ним относятся функции:ЕСЛИ, И, НЕ,ИЛИ.
Функция ЕСЛИ используется для выбора направления вычислений. Например: = ЕСЛИ (Е3>2; 0,5*D3; 0)
Здесь, если условие Е3>2 выполняется, то содержимое ячейки, в которой приведена эта формула, равно 0,5*D3. Если условие не выполняется, то содержимое ячейки равно .
Сообщения об ошибках. Если формула в ячейке не может быть правильно вычислена, Excel выводит сообщение об ошибке.
#ИМЯ? – Excel не смог распознать имя, использованное в формуле;
#ДЕЛ/0! – в формуле делается попытка деления на нуль;
#ЗНАЧ! – использован недопустимый тип аргумента;
#Н/Д – такое сообщение может появиться, если в качестве аргумента задана ссылка на пустую ячейку;
#ПУСТО! – неверно указано пересечение двух областей, которые не имеют общих ячеек;
#ССЫЛКА! – в формуле задана ссылка на несуществующую ячейку;
#ЧИСЛО! – нарушены правила задания операторов, принятые в математике.
Сортировка и фильтрация данных. Содержимое выделенных интервалов можно сортировать с помощью кнопок Сортировка по возрастанию/убыванию на панели Стандартная или с помощью команды Данные/Сортировка….
При использовании команды Данные/Фильтр/Автофильтр в верхнем ряду данных таблицы появляются небольшие кнопки, нажав на которые можно задать условия выборки данных. При записи условий выборки для текста надо учитывать, что один неизвестный символ обозначается – «?», а несколько – «*».
В Excel 2007 можно сортировать данные по цветам и по уровню (вплоть до 64). Можно также фильтровать данные по цветам или датам, отображать более 1000 элементов в раскрывающемся списке Автофильтр, выделять несколько элементов для фильтрации и фильтровать данные в сводных таблицах.