Формулы в программе Microsoft Excel

Excel — программируемый табличный калькулятор. Все расчеты в Excel выполняют формулы. Формулы предназначены для сложения, вычитания, умножения и деления значения, которые введены в ячейки рабочего стола. Формулой Excel считает все, что начинается со знака «=». Если в ячейке написать просто «1+1», Excel не будет вычислять это выражение. Однако, если написать «=1+1» и нажать Enter, в ячейке появится результат вычисления выражения — число 2. После нажатия Enter формула не пропадает, ее можно увидеть снова, если сделать двойной щелчок по ячейке, или если выделить ее и нажать F2. Также ее можно увидеть в панели инструментов «Строка формул», если опять же выделить ячейку. После двойного щелчка, нажатия F2 или после щелчка в строке формул, можно изменить формулу, и для завершения нажать клавишу Enter.

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

Итак, формулы содержат:

Арифметические операторы.

«+» — сложение (Пример: «=1+1»);

«-» — вычитание (Пример: «=1−1»);

«*» — умножение (Пример: «=2*3»);

«/» — Деление (Пример: «=1/3»);

«^» — Возведение в степень (Пример: «=2¹⁰»);

«%» — Процент (Пример: «=3%» — преобразуется в 0,03; «=37*8%» — нашли 8% от 37). То есть если мы дописываем после числа знак «%», то число делится на 100.

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

«>» — больше;

«<�» — меньше;

«>=» — больше, либо равно;

«<=» — меньше, либо равно;

«=» — равно (проверка на равенство);

«» — неравно (проверка на неравенство).

Использование ссылок в формулах.

Для того, чтобы вставить в формулу адрес ячейки (ссылку на ячейку), не обязательно писать его вручную. Проще поставить знак «=», затем левой кнопкой щелкнуть на нужной ячейке или выделить нужный диапазон ячеек. При этом Excel подставит в формулу ссылку автоматически.

Если в формуле используется несколько ссылок, то каждой из них Excel дает свой цвет. Это очень удобно.

Операторы.

Операторы в Excel бывают бинарные и унарные. Бинарные операторы работают 2 значениями. Например, оператор «*» умножает число слева от себя на число справа от себя. Если число слева или справа опустить, то Excel выдаст ошибку.

Унарные операторы оперируют одним значением. Пример унарных операторов: унарный «+» (ничего не делает), унарный «-» (меняет знак числа справа на противоположный) или знак «%» (делит число слева на 100).

Операторы ссылок:

: (двоеточие). Ставится между ссылками на первую и последнюю ячейку диапазона. Такое сочетание является ссылкой на диапазон (A1:A15);

; (точка с запятой). Объединяет несколько ссылок в одну ссылку (СУММ (A1:A15;B1:B15));

(пробел). Оператор пересечения множеств. Служит для ссылки на общие ячейки двух диапазонов (B7:D7 C6: C8).

Функции.

Стандартные функции Excel разделяются на следующие типы: арифметические, статистические, логические, текстовые, функции даты и времени. Формат функции можно уточнить с помощью команды меню Вставка / Функция. В левом окошке перечисляются типы функций, в правом — функции текущей группы. Рассмотрим наиболее употребительные стандартные функции.

К первой группе — арифметических функций — относятся ABS, SIN, COS, EXP, LOG, LOG10 и др. Они используются в основном для математических или технических расчетов.

К той же группе относятся следующие функции:

• -функция суммы СУММ, имеющая вид СУММ (r1;r2;…). Здесь r1, r2, … — аргументы. В качестве аргументов могут использоваться имена полей или диапазоны ячеек. Например, результатом функции СУММ (А2:А10) будет сумма содержимого ячеек А2-А10.
• -функция СУММЕСЛИ, имеющая вид СУММЕСЛИ (интервал1; критерий;интервал2). Функция суммирует ячейки интервала2, которым соответствуют ячейки интервала1, удовлетворяющие указанному критерию (см. Приложение 2).

Другой способ вычисления частичных сумм — с помощью команды меню Сервис / Мастер / Частичная сумма. На первом шаге указывается диапазон ячеек вида $x$n:$z$m. Здесь х и n — координаты левой верхней ячейки диапазона данных, z и m — координаты правой нижней ячейки диапазона данных, после чего щелкнуть кнопку Далее. Появляется вкладка с надписями и окошечками параметров. Первая надпись имеет вид: Задайте столбец, который нужно суммировать, под ней в окошечке Суммировать указывается требуемое поле. Вторая надпись имеет вид: Задайте анализируемый столбец, операцию сравнения и значение, с которым будет сравниваться этот столбец. В соответствующих окошечках указывается имя поля, логическое отношение и значение, после чего щелкнуть кнопку Добавить условие. При необходимости можно добавлять другие условия, в том числе и накладываемые на другие поля; по окончании формирования набора условий щелкнуть кнопку Далее. На следующем шаге предлагается копирование только результата или всей формулы; обычно выбирают первое, после чего щелкнуть кнопку Далее. На последнем этапе определяется адрес ячейки для помещения результата и следует щелкнуть кнопку Готово (см. Приложение 2).

В Excel 2007 наряду с мастером суммирования используется функция СЧЕТЕСЛИМН имеющая формат СЧЕТЕСЛИМН (диапазон1;условие1; диапазон2;условие2;…) (см. Приложение 3).

Из второй группы функций — статистических — наибольшее значение имеют следующие:

• -МАКС (диапазон) — результатом является максимальное значение в указанном диапазоне. Например, если в ячейках А4-А7 расположены соответственно числа 1, 3, 5, 2, то результатом МАКС (А4:А7) будет число 5.
• -МИН (диапазон) — результатом является минимальное значение в указанном диапазоне.

ВНИМАНИЕ! При применении функций МИН и МАКС к данным вида ДАТА, могут возникать ошибки. В этом случае следует перевести данные из типа ДАТА в числовой формат, а затем применить функции МИН или МАКС.

• -СРЗНАЧ (диапазон) — вычисляет среднее арифметическое указанного диапазона. Так, для вычисления среднего балла студентов (см. Приложение 2), можно использовать функцию вида СРЗНАЧ (В2:D2), которую затем копируем в другие ячейки столбца. Быстрое вычисление среднего арифметического можно получить с помощью строки состояния.
• -СЧИТАТЬПУСТОТЫ (диапазон) — подсчитывает количество пустых ячеек в диапазоне.
• -СЧЕТЗ (диапазон) — напротив, подсчитывает количество непустых ячеек в диапазоне.
• -СЧЕТЕСЛИ (диапазон; критерий) — подсчитывает количество ячеек в диапазоне, удовлетворяющих критерию. В качестве диапазона можно задавать имя поля (см. Приложение 3).

С помощью функций СУММЕСЛИ и СЧЕТЕСЛИ можно вычислить среднее значение некоторой величины для данных, удовлетворяющих какому-либо одному условию (см. Приложение 3).

В Excel 2007 существует функция СРЗНАЧЕСЛИ, заменяющая указанную конструкцию. Она имеет формат СРЗНАЧЕСЛИ (диапазон1; «условие»;диапазон2).

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

Другой способ вычисления средней величины для данных, удовлетворяющих некоторому одному условию — с использованием команды меню Данные / Итоги. Вначале необходимо отсортировать данные по указанному критерию, затем перейти в меню Данные / Итоги (см. Приложение 3).

Третью группу функций — логических — представляют функции:

— ЕСЛИ (логическое выражение;значение1[;значение2]).

Здесь знак [ ] обозначает необязательный параметр. Значение 1 — это значение функции в том случае, если логическое выражение имеет значение ИСТИНА, значение2 — если ЛОЖЬ. Логическое выражение может состоять только из логического отношения или включать в себя другие логические функции.

Пример:

=ЕСЛИ (А2>10;А2²;0).

Значением функции будет квадрат значения ячейки А2, если оно больше 10, и 0 в противном случае.

Если функция ЕСЛИ содержит только логическое отношение, то удобно использовать команду меню Вставка / Функция / Логические / Если с указанием соответствующих параметров.

— И (логическое значение1;логическое значение2;…) — содержит от 1 до 30 логических выражений (чаще отношений). Функция имеет значение ИСТИНА, если все аргументы истинны и ЛОЖЬ, если хотя бы один имеет значение ЛОЖЬ.

Если пусть в ячейку А2 записано число 5, а в ячейку В3 — значение 10. Тогда функция =И (А2>3;B33;B3>12) — значение ЛОЖЬ.

• -ИЛИ (логическое значение1;логическое значение2;…) также содержит до 30 значений. Она принимает значение ИСТИНА, если хотя бы один из аргументов имеет значение ИСТИНА, и ЛОЖЬ, если все аргументы ложны.
• -НЕ (аргумент) — изменяет значение логического аргумента на противоположное (ЛОЖЬ на ИСТИНА и наоборот).

Функции И, ИЛИ, НЕ используются в основном как вложенные функции в функции ЕСЛИ (см. Приложение 4).

Четвертая группа функций — функции даты и времени. К ним относятся:

• -ВРЕМЗНАЧ (время) — преобразует время в обычном формате в число от 0 до Например, 12:20 переходит в число 0,51.
• -ВРЕМЯ (часы; минуты; секунды) — аналогичное действие.

ГОД (дата) — выделяет из даты год в виде числа.

• -МЕСЯЦ (дата) — выделяет из даты месяц в виде числа.
• -ДЕНЬ (дата) — выделяет из даты день в виде числа.
• -ДЕНЬНЕД (дата; тип) — переводит дату из числового или обычного формата в день недели (от 1 до 7). Для привычной для нас нумерации с понедельника следует указать в качестве типа число 2.
• -ДАТАМЕС (дата) — определяет дату, отстоящую от указанной даты на заданное число месяцев.
• -СЕГОДНЯ — функция без аргументов, определяет текущую дату.
• -ТДАТА — без аргументов, действует аналогично предыдущей, но кроме даты определяет еще и время.

Например, если в ячейках В2-В25 записаны даты рождения сотрудников, требуется вычислить возраст каждого из них. Формула принимает вид.

=ГОД (СЕГОДНЯ ())-ГОД (В2).

после чего формула копируется вниз.

Чаще всего в таких вычислениях используются логические функции (см. Приложение 5).