Excel 9. Формулы

В Excel имеется очень мощный аппарат для вычислений. То есть в должной мере реализована такая операция, как «Формула». Я не сказала «функция», потому что в программе лента называется «Формула», а вот операция вставки формул называется «Вставить функцию». Скорее всего разработчики имели в виду, что формула может состоять из нескольких функций.

Список вкладок ленты «Формула» я дала на уроке «Excel 3. Введение в Excel».

По окончании этого урока вы сможете:

1. Перечислить и показать арифметические операторы для создания формулы
2. Вводить вручную формулы
3. Использовать встроенные функции
4. Выбирать нужные функции из контекстного меню

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

Все эти символы вводятся с клавиатуры. Рассмотрим на примере.

Шаг 1. Щелкаем по ячейке А1, куда будет введена формула.

Шаг 2. Вводим формулу «=75+32+44»:

Обратите внимание, что в строке формул записывается вводимая нами формула.

Шаг 3. Нажимаем Enter. Активной станет ячейка А2.

Шаг 4. Делаем активной ячейку А1.

Алгоритм ввода формулы при помощи арифметических операторов:

1. Щелкаем на ячейке, в которой должны появиться результаты формулы.
2. Вводим сначала знак равенства (=), а потом − первое значение.
3. Вводим арифметический оператор, а затем − второе значение.
4. После того, как вся формула введена, нажимаем Enter, чтобы завершить ввод.

2. Формула с использованием данных, которые находятся в ячейках

При расчете плотности вязания мне часто приходится вычислять пропорцию:

То есть формула будет выглядеть так: «=18*50/10». Это в том случае, если мы решим вводить её вручную.

Шаг 1. Щелкаем на ячейке, в которой должны появиться результаты формулы:

Шаг 2. Вводим сначала знак равенства «=», а затем щелкаем на ячейке B2, которая содержит первое значение 18:

Шаг 3. Вводим арифметический оператор «*», а потом щелкаем на ячейке A3, которая содержит второе значение 50:

Шаг 4. Вводим арифметический оператор «/», а потом щелкаем на ячейке А2, которая содержит третье значение 10:

Шаг 5. Завершаем ввод, нажав клавишу Enter.

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

3. Формула с использованием встроенной функции и данных, которые находятся в ячейках

Используем пример с петлями (если хотите, можете подсчитать во что обойдется килограмм икры, если её покупать в баночках по 130 грамм при стоимости баночки 740 р.).

Шаг 1. Отмечаем ячейку B3 и открываем диалоговое окно «Аргументы функции» (лента Формулы → группа команд Библиотека функций → функция ПРОИЗВЕД из списка выпадающего меню):

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

Знакомимся с диалоговым окном «Аргументы функции»:

1. Поля, в которые вносятся адреса ячеек. По умолчанию Число 1 имеет адрес ячейки, которая находится как раз над ячейкой с будущей формулой. Полей всего два.
2. Значение произведения. В данном случае значение ячейки B2
3. Пояснение к формуле. Советую на первых порах читать внимательно. Слово «Возвращает» всегда немного напрягало. Все-таки по-русски правильно было сказать: результат вычислений. А дальше пояснение, что перемножать можно до 255 чисел. Полей, напомню, всего два.
4. Значение произведения. Зачем дублировать – загадка.

Шаг 2. Не выходя из диалогового окна «Аргументы функции», отмечаем ячейку B2 (на всякий случай) и щелкаем ЛМ в поле «Число 2»

Шаг 3. Не выходя из диалогового окна «Аргументы функции», отмечаем ячейку A2:

Вот и появилось поле «Число 3». Контролируем полученное значение. Уже 900.

Дальше надо решить проблему с делением. Но функции ДЕЛЕНИЕ нет! Кстати, и функции ВЫЧИТАНИЕ тоже нет. На помощь придёт знание арифметики. Деление – это умножение на дробь «1/а».

Шаг 4. Вводим с клавиатуры в поле «Число 3» − ««1/» и, не выходя из диалогового окна «Аргументы функции», отмечаем ячейку A1:

Все время контролируем полученное значение. Уже 90. А заодно внимательно посмотрим на строку формул. Формула записалась с соблюдением синтаксиса Excel.

Нажимаем ОК:

В строке формул – полностью прописанная формула с одной функцией, а в ячейке – результат вычислений.

Изменяем значение в любой ячейке, которая участвует в формуле, например в ячейке B2:

Формула вычислила новое значение

Обратите внимание, что при работе с диалоговым окном «Аргументы функции» нет необходимости вводить в вычисляемую ячейку знак «=».

4. Формула, записанная вручную

Удалите в ячейке В3 формулу. Записываем формулу заново.

Шаг 1. Вводим знак «=» с клавиатуры и набираем «про»:

Выскакивает выпадающее меню, где представлен список функций, начинающихся на «про».

Шаг 2. Двойной щелчок ЛМ по названию функции «ПРОИЗВЕД» и дальше повторяем действия по вводу адресов ячеек, не забывая разделять адреса знаком «точка с запятой ;»:

Если вы дружите с английской раскладкой клавиатуры, то можете набрать формулу вручную с клавиатуры. Если вы наберете «А» или «В» в русской раскладке, то Excel вас просто не поймёт, формула не заработает, и вы увидите:

При щелчке по зеленому треугольнику ЛМ появится значок с восклицательным знаком, а уже при щелчке ЛМ по этому значку появится выпадающее меню:

Разбираться с этим меню вы будете весь световой день, но ошибку не найдёте. Так что лучше не делать ошибок в синтаксисе формул. Самое лучшее в этом случае щелкнуть ЛМ по строке формул:

Правильно введённые адреса акцентированы цветом, а ошибочный адрес (русская буква «А») – чёрным цветом. Если вы исправите русскую букву «А» на английскую, то ошибка исчезнет.

5. Суммирование строк и столбцов

Шаг 1. Создаем ряд чисел в столбце и делаем активной ячейку суммирования.

Шаг 2. Повторяем Шаги 1÷2 из пункта 4:

1. Знак «=» и набираем «сум»
2. Выбираем функцию «СУММА» из предложенного списка

Шаг 3. Щелкаем по первой ячейке ряда чисел, которые следует просуммировать, и протягиваем курсор вниз, то есть выделяем диапазон ячеек.

Шаг 1. Нажимаем Enter

Обратите внимание, что диапазон ячеек обозначен двоеточием «:» (Excel 5).

Есть более простой способ: воспользоваться кнопкой Автосумма (AutoSum) на ленте Главная (Лента Главная → группа команд Редактирование → команда Автосумма). Эта кнопка продублирована на ленте Формулы.

Если открыть выпадающее меню, то можно увидеть другие функции, которые полезно иметь под рукой.

Щелкаем ЛМ на первой пустой ячейке, находящейся ниже столбца или справа от строки, содержимое в ячейках которых нужно сложить.

Шаг 1. Нажимаем кнопку Автосумма на ленте Главная:

Диапазон ячеек, который суммируется, будет обведен «бегущей» границей, а в строке формул появится формула «=СУММ(С1:С4)».

Шаг 2. Нажимаем Enter

Но по жизни часто надо просуммировать расходы, которые записаны по разным столбцам (например, при определении общих семейных расходов). То есть необходимо узнать сумму по разным диапазонам.

Создаем ряд чисел по двум столбцам и делаем активной ячейку суммирования.

Шаг 1. Повторяем Шаги 1÷2 из пункта 4:

1. Знак «=» и набираем «сум»
2. Выбираем функцию «СУММА» из предложенного списка

Шаг 7. Выбираем первый диапазон (1), затем второй диапазон (2) простым перетаскиванием ЛМ:

6. Далеко идущие выводы

1. Формула введенная вручную

2. Формула с использованием данных, которые находятся в ячейках

3. Формула с использованием встроенной функции и данных, которые находятся в ячейках

4. Формула с использованием встроенной функции и данных, которые находятся в двух диапазонах

Теперь вы можете:

1. Перечислить и показать арифметические операторы для создания формулы
2. Вводить вручную формулы
3. Использовать встроенные функции
4. Выбирать нужные функции из контекстного меню

На этом занятии вы познакомились с базой, на которой построены все возможности мощного вычислительного аппарата Excel.