В столбцах таблицы «Сумма» и «Результат операции» должны быть введены формулы для расчета значений. При этом формула столбца «Результат операции» будет основываться на функции «ЕСЛИ» из категории логических. Переименуйте «Результат операции» в «Денежный поток».
2.1. Проанализируем, из чего должна состоять формула для расчета суммы. Сумма в данном случае - это количество единиц товара, умноженное на цену товара. Формула для вычисления суммы соответственно будет состоять из относительного адреса ячейки, которая содержит количество, арифметической операции умножения, и относительного адреса ячейки, которая содержит цену. В начале формулы должен стоять знак "=". Например, для строки 4:
==E4*G4
Рис. 3 - Заполненная данными входная часть таблицы
Для того, чтобы ввести эту формулу, произведите следующие действия:
- разместите курсор в ячейке с адресом H4;
- введите с помощью клавиатуры знак "=";
- выберите курсором мыши ячейку с адресом E4;
- введите с помощью клавиатуры знак "*";
- выберите курсором мыши ячейку с адресом G4;
- нажмите клавишу Enter.
Содержание ячейки H4 формула ==E4*G4, оно отображается в строке формул. На листе электронной таблицы отображается результат вычисления формулы 50 000,00 ₽.
Сумма имеет денежный формат автоматически, так как такой формат имеет один из операндов.
Так как формула должна быть воспроизведена для всех строк таблицы (всех товаров), ее нужно автозаполнить. При этом относительные адреса ячеек будут соответственно изменяться: номер строки будет наращиваться на 1 (инкремент) относительно неизменного заголовка столбца, например, для цены G5, G6, G7 … G23.
Выполните автозаполнение формулы:
- наведите курсор мыши на правый нижний угол ячейки с адресом H4;
- когда вид курсора изменится, нажмите левую кнопку мыши и протяните его вниз по столбцу к ячейке с адресом H23.
Если после автозаполнения некоторые ячейки отображают следующие символы "#######", это значит, что результат формулы не вмещается в ячейку. Нужно или растянуть столбец с этой ячейкой к оптимальной ширине, или выполнить двойной щелчок левой кнопкой мыши между заглавиями столбцов (ширина будет автоматически изменена соответственно ширине содержимого).
2.2. Проанализируем, из чего должна состоять формула для расчета результата операции, или денежного потока. Денежный поток - это или сумма со знаком "+" (в случае поступления товара), или сумма со знаком "-" (в случае отгрузки товара). Для того, чтобы создать универсальную формулу, которую можно автозаполнять для всех строк с товарами, нужная функция, которая будет анализировать содержимое ячейки с видом операции (поступление или отгрузка), и в зависимости от результата анализа выдавать сумму с соответствующим знаком как результат своей работы. Это можно сделать с помощью функции "ЕСЛИ" из категории логических функций.
Функция "ЕСЛИ" нуждается в заполнении трех обязательных аргументов (входных выражений или значений). Первый аргумент - это условие, чаще всего это логическое выражение. В любом случае (выражение или значение этот аргумент) результат анализа первого аргумента - это логические значения "ИСТИНА" или "ЛОЖЬ".
Логическое выражение данного примера - это сравнение содержимого ячейки с адресом D4 и текстового значения (текстовой константы) "Поступление". Если содержимое ячейки совпадает с текстовой константой, результат анализа первого аргумента будет "ИСТИНА", если отличается хотя бы на один символ, результат анализа первого аргумента будет "ЛОЖЬ".
Первый аргумент:
D4= "Поступление"
Второй аргумент (результат работы функции, если результат анализа первого аргумента будет "ИСТИНА":
+H4
Третий аргумент (результат работы функции, если результат анализа первого аргумента будет "ЛОЖЬ":
-H4
Аргументы, согласно синтаксису функций, разделяются точкой с запятой; и размещаются в круглых скобках (). То есть полная запись функции будет
= Если (D4= "Поступление"; +H4; -H4)
Адреса ячеек в аргументах для данного примера нужны относительные. При автозаполнении они будут инкрементироваться (наращиваться на 1). В каждой строке формула с функцией "ЕСЛИ" будет выдавать разный результат, в зависимости от значений аргументов. То есть она будет универсальным инструментом вычисления результата операции для всех строк с товарами.
Чтобы правильно ввести такую формулу, нужно произвести следующие действия:
- разместите курсор в ячейке с адресом I4;
- вызовите мастер функций. Варианты: с помощью кнопки «Вставить функцию» ленты «Формулы» или кнопки категории функций этой ленты; либо, нажмите на кнопку вставки функции в строке формул. Существует еще несколько способов вызова мастера функций. Например, нажать сбоку на стрелку кнопку со списком "Автосумма" из ленты "Главная". В списке, который откроется, выбрать вариант "Другие функции" и на экране появится окно первого шага мастера функций для выбора категории и названия функции. Другие варианты – ручной ввод, ввод как вложение функций в мастере и др.
Когда мастер функций тем или иным способом вызван, на первом его шаге в поле со списком "Категория" выберите категорию "Логические", потом в списке выберите функцию "ЕСЛИ". Нажмите на кнопку "ОК".
На втором шаге мастера функций необходимо заполнить все аргументы функции "ЕСЛИ".
Для заполнения аргументов функции "ЕСЛИ" (см. рис. 4):
- нажмите на кнопку сворачивания мастера, которая находится справа в поле "Лог_выражение";
- выберите курсором мыши ячейку с адресом D4, нажмите для вставки;
- нажмите на кнопку разворачивания мастера;
- введите с помощью клавиатуры знак "=" и в двойных кавычках текстовую константу "Поступление";
- в поле "Значение_если_истина" введите с помощью клавиатуры знак "+" и нажмите на кнопку сворачивания мастера, которая находится справа;
- выберите курсором мыши ячейку с адресом H4, нажмите для вставки;
- нажмите на кнопку разворачивания мастера;
- в поле "Значение_если_ложь" введите с помощью клавиатуры знак "-" и нажмите на кнопку сворачивания мастера, которая находится справа;
- выберите курсором мыши ячейку с адресом H4, нажмите для вставки;
- нажмите на кнопку разворачивания мастера;
- нажмите на кнопку "ОК".
Для того, чтобы результат операции имел денежный формат и отрицательные суммы при этом отображались красным цветом, выполните соответствующие настройки в формате чисел столбца.
Выполните автозаполнение формулы:
- наведите курсор мыши на правый нижний угол ячейки с адресом I4;
- когда вид курсора изменится, нажмите левую кнопку мыши и протяните его вниз по столбцу к ячейке с адресом I23.
При необходимости растяните столбец I к оптимальной ширине.
Рис. 4 - Ввод аргументов функции "ЕСЛИ"