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

Если в формуле используется текст, то он обязательно должен быть заключен в двойные кавычки. Если написать формулу «=мама», Excel выдаст ошибку, а если написать «="мама"» — все ок, корректная формула.

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

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

Если в формуле используется несколько ссылок, то каждой из них Excel дает свой цвет. Это очень удобно. Пример: напишите в какой либо ячейке формулу «=A1+D1», нажмите Enter, затем два раза щелкнете по ячейке. В ячейке вы увидите формулу с разноцветными ссылками, а вокруг ячеек A1 и D1 будут прямоугольники соответствующих цветов. Гораздо проще найти, куда указывет ссылка, по цвету прямоугольника, чем просматривать буквы столбцов и номера строк. Наведите курсор мыши на один из разноцветных прямоугольников и перетащите левой кнопкой за границу в другое место. Вы увидите, что при этом меняются и адреса ячеек в формуле — часто это самый быстрый способ подправить адреса в формуле, особенно после копирования маркером автозаполнения.

] Операторы

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

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

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

• «+» — сложение (Пример: «=1+1»);
• «-» — вычитание (Пример: «=1-1»);
• «*» — умножение (Пример: «=2*3»);
• «/» — Деление (Пример: «=1/3»);
• «^» — Возведение в степень (Пример: «=2^10»);
• «%» — Процент (Пример: «=3 %» — преобразуется в 0,03; «=37*8 %» — нашли 8 % от 37). То есть если мы дописываем после числа знак «%», то число делится на 100.

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

Логические операторы

• ">" — больше;
• "<" — меньше;
• ">=" — больше, либо равно;
• "<=" — меньше, либо равно;
• "=" — равно (проверка на равенство);
• "<>" — неравно (проверка на неравенство).

Оператор объединения 2-х строк текста в одну

Оператор «&» (амперсанд) служит для «склеивания» между собой двух текстовых строк. Например, в ячейке A1 текст «мама», в ячейке A2 текст «мыла раму». В A3 пишем формулу «=A1 & A2». В результате в ячейке A3 появится текст «мамамыла раму». Как видим, пробел между двумя строками автоматически не ставится. Чтобы вставить этот пробел, нужно изменить формулу вот так: «=A1 & " " & A2». Точно так же работает оператор "СЦЕПИТЬ", выглядеть формула с его участием будет так: «=Сцепить(A1;" ";A2)».

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

• : (двоеточие). Ставится между ссылками на первую и последнюю ячейку диапазона. Такое сочетание является ссылкой на диапазон (A1:A15);
• ; (точка с запятой). Объединяет несколько ссылок в одну ссылку (СУММ(A1:A15;B1:B15));
• (пробел). Оператор пересечения множеств. Служит для ссылки на общие ячейки двух диапазонов (B7:D7 C6:C8).

Выражения

Выражения в Excel бывают арифметические и логические. Арифметическое выражение (например, «=2*(2+5)», результат — 14) в результате дает числовое значение (положительное, отрицательное, дробное число). Логическое выражение (например, «=3>5», результат — логическое значение «ЛОЖЬ»)в результате может дать лишь 2 значения: «ЛОЖЬ» или «ИСТИНА» (одно число либо больше другого, либо не больше, других вариантов нет).

Функции в Microsoft Excel

В формулах Microsoft Excel можно использовать функции. Сам термин «функция» здесь используется в том же значении, что и «функция» в программировании. Функция представляет собой готовый блок (кода), предназначенный для решения каких-то задач.

Все функции в Excel характеризуются:

• Названием;
• Предназначением (что, собственно, она делает);
• Количеством аргументов (параметров);
• Типом аргументов (параметров);
• Типом возвращаемого значения.

В качестве примера разберем функцию «СТЕПЕНЬ»

• Название: СТЕПЕНЬ;
• Предназначение: возводит указанное число в указанную степень;
• Количество аргументов: РАВНО два (ни меньше, ни больше, иначе Excel выдаст ошибку!);
• Тип аргументов: оба аргумента должны быть числами, или тем, что в итоге преобразуется в число. Если вместо одного из них вписать текст, Excel выдаст ошибку. А если вместо одно из них написать логические значения «ЛОЖЬ» или «ИСТИНА», ошибки не будет, потому что Excel считает «ЛОЖЬ» равно 0, а истину — любое другое ненулевое значение, даже −1 равно «ИСТИНА». То есть логические значения в итоге преобразуются в числовые;
• Тип возвращаемого значения: число — результат возведения в степень.

Пример использования: «=СТЕПЕНЬ(2;10)». Если написать эту формулу в ячкейке и нажать Enter, в ячейке будет число 1024. Здесь 2 и 10 — аргументы (параметры), а 1024 — возвращаемое функцией значение.

Пример формулы для вычисления длины окружности, содержащую функцию ПИ():

Синтаксис записи функции

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

Общий синтаксис записи любой функции в Excel:

имя_функции([аргумент_1; аргумент_2; … ; аргумент_N])

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

• Некоторые функции вообще не принимают аргументов. Например, функция ПИ() просто возвращает в ячеку значение константы «3,1415…», а функция СЕГОДНЯ() вставляет в ячейку текущую дату. Однако, даже если функция не принимает аргументов, пустые круглые скобки писать обязательно, иначе Excel выдаст ошибку!

• Некоторые функции принимают РОВНО ОДИН аргумент. Например функции sin(число), cos(число) и т. п.

• Некоторые функции принимают больше, чем один аргумент. В таком случае аргументы разделяются между собой точкой с запятой «;».

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

Ввод функций вручную

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

К недостаткам набора формул вручную можно отнести:

• Руками ставить «=»;
• набирать имя функции;
• открывать/закрывать круглые скобки;
• расставлять точки с запятой;
• следить за порядком вложенности
• заключать текст в двойные кавычки;
• не иметь возможность посмотреть промежуточные расчеты;
• и т. п.

Слишком большая вероятность допустить ошибку, набирая вручную сложные и длинные формулы, и на это уходит много времени.

Ввод функции с помощью кнопки "сигма"

Одно из средств облегчить и ускорить работы с функциями — кнопка на панели инструментов «Стандартная». В ней разработчики Microsoft «спрятали» пять часто используемых функций:

• СУММ(минимум один, максимум 30 аргументов). Суммирует свои аргументы.

Полезный совет: Чтобы быстро узнать сумму значений в диапазоне, выделяем его и смотрим на строку состояния — там должна отображаться сумма;

• СРЗНАЧ(минимум один, максимум 30 аргументов). Находит среднее арифметическое аргументов;
• СЧЁТ(минимум один, максимум 30 аргументов). Подсчитывает количество чисел в списке аргументов (используется для подсчета количества ячеек с числами, пустые ячейки и текст игнорируются);
• МАКС(минимум один, максимум 30 аргументов). Возвращает максимальный аргумент;
• МИН(минимум один, максимум 30 аргументов). Возвращает минимальный аргумент.

Принцип работы:

1. активизируем ячейку, где должен быть результат (просто щелкаем);
2. Нажимаем на стрелочку справа от кнопки "сигма";
3. Выбираем нужную функцию. После выбора Excel сам вставит знак «=», имя функции, круглые скобки, и даже попытается угадать диапазон, который мы ходим выделить (хотя он редко угадывает);
4. Выделяем связный диапазон ячеек. Excel вставит в круглые скобки адрес выделенного диапазона;
5. Если нужно, например, проссуммировать числа из несвязных диапазонов, зажимаем Ctrl, и выделяем нужное количество диапазонов. Excel сам поставит точку с запятой, и вставит ссылку на другой диапазон;
6. Когда выделили все нужные диапазоны, для завершения нажимаем Enter;

В процессе выделения можно нажимать F4 для изменения типа ссылки и F3 для вставки именованных ссылок.

Работа с мастером функций

] Общие сведения. Способы запуска

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

Существует 3 способа запуска мастера функций:

1. С помощью кнопки в строке формул;
2. С помощью команды "Другие функции..." кнопки ;
3. С помощью пункта меню "Вставка" —> "Функция";

Первый шаг

После выполнения одного из этих действий откроется окно мастера функций:

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

Второй шаг

Открывается следующее окно:

Вставка вложенной функции

В одну функцию можно вставить другую функцию. Допускается до 7-ми уровней вложения функций (в Office 2007 — до 64). Конечно, функцию можно записать вручную (писать название вложенной функции, открывать скобки, ставить точки с запятой). Однако это противоречит самой идеологии мастера функций, который должен облегчать написание формул, защищать пользователя от ошибок и свести к минимуму ручную работу. Существует более удобный способ вложить функцию — специальная кнопка на панели "Строка формул":

После выбора нужной функции из выпадающего списка Excel вставит называние фукнции и круглые скобки в указанное место в формуле (в активное текстовое поле аргумента). После этого окно мастера функций для предыдущей функции (в этом примере "СУММ") сменится на окно для вставляемой функции ("СТЕПЕНЬ"), и ее название в формуле сделается жирным:

] Переключение на другую функцию в формуле

Чтобы опять вернуться к окну для функции "СУММ", достаточно просто щелкнуть в строке формул на ее названии, и окно для степени сменится на окно для "СУММ". После этого функция "СУММ" в названии станет жирной, показывая, что в данный момент окно открыто именно для нее.