БСВойтиСохранить прогресс
← Все статьи
Задание 3Базы данных

Работа в LibreOffice Calc

Работа с электронными таблицами в LibreOffice Calc

LibreOffice Calc позволяет хранить данные в таблицах, выполнять вычисления и отбирать записи по условиям. Разберём основные возможности на небольшой базе данных. Создадим три листа: «Магазин», «Товар» и «Движение товаров». На листе «Магазин» укажем, в каком районе находится каждый магазин. Буквы сверху обозначают столбцы, числа слева — номера строк.

AB1ID магазинаРайон2М1Нагорный3М2Центральный4М3Нагорный\begin{array}{c|c|c} & A & B \\ \hline 1 & \text{ID магазина} & \text{Район} \\ \hline 2 & \text{М1} & \text{Нагорный} \\ 3 & \text{М2} & \text{Центральный} \\ 4 & \text{М3} & \text{Нагорный} \end{array}

На листе «Товар» запишем названия товаров и цены одной упаковки. Артикул — уникальный код, по которому можно определить товар.

ABC1АртикулНазвание товараЦена, руб.241Бумага туалетная90342Салфетки бумажные40443Мыло60\begin{array}{c|c|c|c} & A & B & C \\ \hline 1 & \text{Артикул} & \text{Название товара} & \text{Цена, руб.} \\ \hline 2 & 41 & \text{Бумага туалетная} & 90 \\ 3 & 42 & \text{Салфетки бумажные} & 40 \\ 4 & 43 & \text{Мыло} & 60 \end{array}

На листе «Движение товаров» запишем поступления и продажи. Каждая строка описывает одну операцию. Все даты в примере относятся к одному году.

ABCDE1ДатаМагазинАртикулУпаковокОперация202.10М14110Продажа304.10М3425Поступление406.10М2418Продажа510.10М34212Продажа616.10М1437Продажа\begin{array}{c|c|c|c|c|c} & A & B & C & D & E \\ \hline 1 & \text{Дата} & \text{Магазин} & \text{Артикул} & \text{Упаковок} & \text{Операция} \\ \hline 2 & 02.10 & \text{М1} & 41 & 10 & \text{Продажа} \\ 3 & 04.10 & \text{М3} & 42 & 5 & \text{Поступление} \\ 4 & 06.10 & \text{М2} & 41 & 8 & \text{Продажа} \\ 5 & 10.10 & \text{М3} & 42 & 12 & \text{Продажа} \\ 6 & 16.10 & \text{М1} & 43 & 7 & \text{Продажа} \end{array}

Например, строка 2 означает: 2 октября в магазине М1 продали 10 упаковок товара с артикулом 41. По таблице «Товар» определим, что это туалетная бумага, а по таблице «Магазин» — что магазин находится в Нагорном районе. Так информация из разных таблиц связывается по общим значениям.

Поставим задачу: найти выручку от продажи туалетной бумаги и бумажных салфеток в магазинах Нагорного района с 1 по 15 октября включительно.

Ячейки и формулы

Адрес ячейки состоит из буквы столбца и номера строки. Например, на листе «Движение товаров» в D2 записано число 10, а в D5 — число 12. Запись D2:D6 обозначает диапазон: все ячейки столбца D со строки 2 по строку 6 включительно.

Формула начинается со знака равенства. Например, =D2+D5 сложит значения двух ячеек и вернёт 22. Для умножения используется знак *, для деления — /, для вычитания — минус. Скобки позволяют изменить порядок действий.

ФормулаЧто вычисляетРезультат=D2+D5Сумму двух значений22=D2*90Стоимость 10 упаковок по 90 рублей900=D5-D3Разность двух значений7=(D2+D5)/2Среднее двух значений11\begin{array}{l|l|c} \text{Формула} & \text{Что вычисляет} & \text{Результат} \\ \hline \text{=D2+D5} & \text{Сумму двух значений} & 22 \\ \text{=D2*90} & \text{Стоимость 10 упаковок по 90 рублей} & 900 \\ \text{=D5-D3} & \text{Разность двух значений} & 7 \\ \text{=(D2+D5)/2} & \text{Среднее двух значений} & 11 \end{array}

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

Основные функции

Функции — готовые команды для вычислений. Например, чтобы сложить значения всех ячеек диапазона, не обязательно перечислять их через плюс: можно использовать СУММ. После названия функции в круглых скобках указываются данные, с которыми она работает.

В диапазоне D2:D6 находятся числа 10, 5, 8, 12 и 7. Посмотрим, какие результаты дадут разные функции.

ФормулаЧто находитРезультат=СУММ(D2:D6)Сумму значений42=МИН(D2:D6)Наименьшее значение5=МАКС(D2:D6)Наибольшее значение12=СРЗНАЧ(D2:D6)Среднее арифметическое8,4=СЧЁТ(D2:D6)Количество числовых ячеек5=СЧЁТЗ(E2:E6)Количество непустых ячеек5\begin{array}{l|l|c} \text{Формула} & \text{Что находит} & \text{Результат} \\ \hline \text{=СУММ(D2:D6)} & \text{Сумму значений} & 42 \\ \text{=МИН(D2:D6)} & \text{Наименьшее значение} & 5 \\ \text{=МАКС(D2:D6)} & \text{Наибольшее значение} & 12 \\ \text{=СРЗНАЧ(D2:D6)} & \text{Среднее арифметическое} & 8{,}4 \\ \text{=СЧЁТ(D2:D6)} & \text{Количество числовых ячеек} & 5 \\ \text{=СЧЁТЗ(E2:E6)} & \text{Количество непустых ячеек} & 5 \end{array}

Важно различать сумму значений и количество записей. СУММ вернула 42 — сумму количеств упаковок, а СЧЁТ вернула 5 — количество ячеек с числами. При этом число 42 пока не отвечает на нашу задачу: мы сложили все операции, включая поступление и продажи, которые не подходят по условию.

Функция ЕСЛИ проверяет условие и выбирает один из двух результатов. Например:

=ЕСЛИ(E2="Продажа";D2;0)

Эта запись означает: если в E2 указана «Продажа», взять количество из D2, иначе записать 0. Для строки 2 получится 10. Если скопировать формулу в строку 3, получится 0, поскольку там находится поступление. Части функции разделяются точкой с запятой, а текст заключается в двойные кавычки.

Фильтрация данных

Фильтр оставляет на экране нужные строки, а остальные временно скрывает. Чтобы включить его, выделим таблицу вместе с заголовками и выберем «Данные → Автофильтр». В заголовках появятся кнопки, через которые можно выбирать значения.

Сначала на листе «Магазин» откроем фильтр столбца «Район» и оставим только «Нагорный». Получим:

ID магазинаРайонМ1НагорныйМ3Нагорный\begin{array}{c|c} \text{ID магазина} & \text{Район} \\ \hline \text{М1} & \text{Нагорный} \\ \text{М3} & \text{Нагорный} \end{array}

Затем на листе «Товар» оставим туалетную бумагу и бумажные салфетки. Их артикулы — 41 и 42. Теперь знаем, какие магазины и товары нужно выбрать в таблице операций.

На листе «Движение товаров» установим следующие фильтры:

СтолбецЧто оставляемДатаС 1 по 15 октября включительноМагазинМ1 и М3Артикул41 и 42ОперацияПродажа\begin{array}{l|l} \text{Столбец} & \text{Что оставляем} \\ \hline \text{Дата} & \text{С 1 по 15 октября включительно} \\ \text{Магазин} & \text{М1 и М3} \\ \text{Артикул} & \text{41 и 42} \\ \text{Операция} & \text{Продажа} \end{array}

В результате останутся две записи:

ДатаМагазинАртикулУпаковокОперация02.10М14110Продажа10.10М34212Продажа\begin{array}{c|c|c|c|c} \text{Дата} & \text{Магазин} & \text{Артикул} & \text{Упаковок} & \text{Операция} \\ \hline 02.10 & \text{М1} & 41 & 10 & \text{Продажа} \\ 10.10 & \text{М3} & 42 & 12 & \text{Продажа} \end{array}

Операция 4 октября не подходит, потому что это поступление. Продажа 6 октября произошла в магазине другого района. Операция 16 октября находится за пределами периода и относится к другому товару.

Несколько выбранных значений одного столбца означают «ИЛИ»: подходит магазин М1 или магазин М3. Условия разных столбцов выполняются одновременно: должны подходить и магазин, и товар, и дата, и тип операции.

ВПР: как получить цену по артикулу

Для вычисления выручки нужны количество проданных упаковок и цена одной упаковки. Количество уже есть в таблице операций, а цена находится на листе «Товар». Чтобы автоматически найти её по артикулу, используем ВПР.

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

Вернём отображение всех операций и добавим столбец F «Цена». В ячейку F2 введём формулу:

=ВПР(C2;Товар.$A$2:$C$4;3;0)\text{=ВПР(C2;Товар.A\2:C\4;3;0)}

Разберём её по частям:

Часть формулыЧто означаетC2Артикул, который ищемТовар.$A$2:$C$4Диапазон, в котором ищем3Номер столбца с результатом внутри диапазона0Ищем точное совпадение\begin{array}{l|l} \text{Часть формулы} & \text{Что означает} \\ \hline \text{C2} & \text{Артикул, который ищем} \\ \text{Товар.A\2:C\4} & \text{Диапазон, в котором ищем} \\ 3 & \text{Номер столбца с результатом внутри диапазона} \\ 0 & \text{Ищем точное совпадение} \end{array}

В C2 находится артикул 41. Функция просматривает первый столбец диапазона A2:C4 на листе «Товар» и находит строку с этим артикулом:

Первый столбецВторой столбецТретий столбец41Бумага туалетная90\begin{array}{c|c|c} \text{Первый столбец} & \text{Второй столбец} & \text{Третий столбец} \\ \hline 41 & \text{Бумага туалетная} & 90 \end{array}

Третий аргумент равен 3, поэтому ВПР берёт значение из третьего столбца найденной строки. Получаем цену 90 рублей. Для артикула 42 аналогично получится 40 рублей, для артикула 43 — 60 рублей.

Номер столбца считается внутри выбранного диапазона. Например, в диапазоне A:C столбец C будет третьим, а в диапазоне B:C — вторым. При этом артикулы обязательно должны находиться в первом столбце области поиска.

Последний аргумент 0 задаёт точное совпадение. Это необходимо, потому что нам нужна цена конкретного товара. Если указанный артикул не найден, функция вернёт ошибку #Н/Д\text{\#Н/Д}.

Чтобы получить цены остальных товаров, протянем формулу вниз: выделим F2 и потянем за маленький маркер в правом нижнем углу ячейки до F6.

Относительная и абсолютная адресация

При копировании формулы обычные ссылки изменяются. Например, C2 превращается в C3, затем в C4. Это относительная адресация. Она позволяет каждой строке использовать собственные данные.

Посмотрим на простой пример. В G2 записана формула:

=D2*F2\text{=D2*F2}

При протягивании вниз получим:

ЯчейкаФормулаG2=D2*F2G3=D3*F3G4=D4*F4\begin{array}{c|l} \text{Ячейка} & \text{Формула} \\ \hline \text{G2} & \text{=D2*F2} \\ \text{G3} & \text{=D3*F3} \\ \text{G4} & \text{=D4*F4} \end{array}

В каждой строке количество умножается на цену из этой же строки. Нам не приходится вручную менять номера ячеек.

Однако диапазон справочника в ВПР не должен смещаться. Если записать A2:C4 без закрепления, при копировании на строку ниже получится A3:C5. Товар из строки 2 больше не попадёт в область поиска.

Чтобы адрес не менялся при копировании, перед буквой столбца и номером строки ставят знак доллара. Такая ссылка называется абсолютной:

$A$2\text{A\2}

Здесь закреплены и столбец A, и строка 2. Если скопировать формулу вниз или вправо, ссылка останется прежней.

В нашей ВПР закреплён весь диапазон справочника:

$A$2:$C$4\text{A\2:C\4}

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

Исходная ссылкаНа одну строку внизНа один столбец вправоA2A3B2$A$2$A$2$A$2$A2$A3$A2A$2A$2B$2\begin{array}{l|l|l} \text{Исходная ссылка} & \text{На одну строку вниз} & \text{На один столбец вправо} \\ \hline \text{A2} & \text{A3} & \text{B2} \\ \text{A\2} & \text{A\2} & \text{A\2} \\ \text{A2} & \text{\A3} & \text{\$A2} \\ \text{A2} & \text{A\2} & \text{B\$2} \end{array}

Последние две записи называются смешанными ссылками. В $A2\text{\$A2} закреплён только столбец, а в A$2\text{A\$2} — только строка. Знак доллара фиксирует именно ту часть адреса, перед которой стоит.

Таким образом, при протягивании нашей ВПР меняется ячейка с искомым артикулом, а диапазон справочника остаётся прежним:

ЯчейкаФормулаF2=ВПР(C2;Товар.$A$2:$C$4;3;0)F3=ВПР(C3;Товар.$A$2:$C$4;3;0)F4=ВПР(C4;Товар.$A$2:$C$4;3;0)\begin{array}{c|l} \text{Ячейка} & \text{Формула} \\ \hline \text{F2} & \text{=ВПР(C2;Товар.A\2:C\4;3;0)} \\ \text{F3} & \text{=ВПР(C3;Товар.A\2:C\4;3;0)} \\ \text{F4} & \text{=ВПР(C4;Товар.A\2:C\4;3;0)} \end{array}

Вычисляем выручку

После добавления цен снова применим фильтры и скопируем подходящие строки на новый лист. Используем специальную вставку только значений, чтобы перенести готовые цены, а не формулы со ссылками. Заголовки разместим в строке 1, две выбранные операции — в строках 2 и 3.

Добавим столбец G «Выручка». В G2 введём формулу и протянем её на следующую строку:

=D2*F2\text{=D2*F2}

Для удобства ниже показаны только столбцы, участвующие в расчёте:

DFG1УпаковокЦенаВыручка2109090031240480\begin{array}{c|c|c|c} & D & F & G \\ \hline 1 & \text{Упаковок} & \text{Цена} & \text{Выручка} \\ \hline 2 & 10 & 90 & 900 \\ 3 & 12 & 40 & 480 \end{array}

Сложим значения столбца G:

=СУММ(G2:G3)\text{=СУММ(G2:G3)}

Получим:

10⋅90+12⋅40=1380 руб.10 \cdot 90 + 12 \cdot 40 = 1380 \text{ руб.}

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

Если нужно вычислить сумму непосредственно в отфильтрованной таблице, используется функция ИТОГ:

=ИТОГ(9;G2:G6)\text{=ИТОГ(9;G2:G6)}

Она сложит значения столбца G, пропуская строки, скрытые фильтром. Число 9 означает суммирование. Перед этим выручка должна быть рассчитана в столбце G для всех операций.

Что считать в разных задачах

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

Что требуетсяПример вычисленияКоличество упаковок10+12=22Выручка10⋅90+12⋅40=1380 руб.Масса 22 упаковок по 0,5 кг22⋅0,5=11 кгУвеличение: поступило 30, продано 2230−22=8Конечный запас: было 15, поступило 30, продано 2215+30−22=23\begin{array}{l|l} \text{Что требуется} & \text{Пример вычисления} \\ \hline \text{Количество упаковок} & 10+12=22 \\ \text{Выручка} & 10\cdot90+12\cdot40=1380 \text{ руб.} \\ \text{Масса 22 упаковок по 0,5 кг} & 22\cdot0{,}5=11 \text{ кг} \\ \text{Увеличение: поступило 30, продано 22} & 30-22=8 \\ \text{Конечный запас: было 15, поступило 30, продано 22} & 15+30-22=23 \end{array}

Если масса каждой упаковки одинаковая, достаточно умножить общее количество на эту массу. Когда у товаров разные массы или цены, нужную характеристику удобно получить через ВПР. Если масса указана в граммах, а ответ требуется в килограммах, результат делим на 1000.

Увеличение запаса и запас на конец периода — разные величины. Чтобы найти увеличение, вычитаем продажи из поступлений. Чтобы узнать, сколько товара осталось, дополнительно учитываем начальный запас.

При решении задания сначала определяем нужные магазины и товары, затем отбираем операции по всем условиям и выполняем расчёт. Перед записью ответа проверяем границы периода, тип операции и единицы измерения.