Сегодня уже никого не удивишь электронными таблицами. Они встречаются везде: от школьных ведомостей до бухгалтерских отчётов и планирования семейного бюджета. Но если открыть пустой лист LibreOffice Calc, легко подумать, что это просто сетка из клеточек, куда можно записывать числа и текст. Но на деле же, электронные таблицы – это крайне полезный инструмент для работы с данными, анализа и автоматизации.
В прошлой статье мы говорили о том, что такое базы данных, зачем нужны таблицы и уникальные идентификаторы, а также познакомились с реляционным подходом – когда данные хранятся не в одной громоздкой таблице, а распределены по нескольким и связаны ключами. Теперь самое время перейти от теории к практике. Мы посмотрим, как такие же приёмы работы с данными можно применять в LibreOffice Calc.
Представим себе ситуацию. У вас есть список учеников с их оценками по разным предметам. На другом листе – список преподавателей и предметов, которые они ведут. А ещё где-то лежит таблица с итоговыми средними баллами класса. Всё это вроде бы отдельные данные, но нам хочется собрать их воедино: быстро находить нужную информацию, сравнивать результаты, строить отчёты.
Вот здесь и начинается самое интересное. Calc позволяет не просто хранить цифры и тексты, а обрабатывать их с помощью функций и инструментов. Мы можем:
- искать данные по ключам с помощью функции ВПР() (аналог Excel, только с некоторыми особенностями);
- отбирать только нужные строки через фильтры;
- использовать простые, но полезные функции вроде СЧЁТ(), СРЗНАЧ(), ЕСЛИ() для анализа информации;
- комбинировать таблицы и превращать хаос в понятные и структурированные отчёты.
Зачем всё это нужно? Потому что работать «вручную» с большими таблицами не только долго, но и рискованно. Одна ошибка в копировании – и итоговые данные уже неверны. А грамотное использование функций избавляет от рутины и позволяет сосредоточиться на сути задачи.
В этой статье мы разберём основные приёмы работы с данными в LibreOffice Calc. Начнём с функции ВПР(), которая помогает находить и подтягивать значения из других таблиц. Затем посмотрим, как устроены фильтры, и научимся оставлять в таблице только то, что нам нужно. А в завершение потренируемся использовать другие полезные функции Calc на простых и понятных примерах.
Функция ВПР()
Когда у нас есть несколько таблиц, которые связаны между собой, часто возникает необходимость «подтянуть» данные из одной таблицы в другую. Делать это вручную – значит каждый раз искать глазами нужную информацию, копировать ей и вставлять в нужное место. Но представьте, что у нас не парочка строк, а целая тысяча – такая работа быстро превратится в кошмар.
Чтобы этого избежать, в LibreOffice Calc есть специальная функция ВПР() (в английской версии – VLOOKUP). Её задача проста: найти в одной таблице значение по ключу и вернуть соответствующую информацию. Другими словами, это инструмент для «вертикального поиска» (поиск значения в одном столбце и выдача результата из другого столбца этой же строки).
Это экономит массу времени и исключает ошибки ручного копирования. Особенно полезно при больших таблицах со значительным количеством строк.
В Calc синтаксис функции выглядит так:
=ВПР(Искомое_значение; Таблица; Номер_столбца; [Интервальный_просмотр])
Разберём каждый аргумент по отдельности.
– Искомое_значение / Критерий поиска
Это то, что мы ищем – ключ, по которому будем сопоставлять записи. Значение берётся именно из первого (левого) столбца указанного диапазона поиска.
– Таблица / Массив
Диапазон ячеек, в котором Calc будет искать. Искомое значение ищется строго в первом столбце этого диапазона. Например, если таблица «Ученики» расположена в диапазоне «A3:C6», то поиск будет происходить по столбцу «A» этого диапазона.
При записи диапазона лучше фиксировать его абсолютными ссылками – «$A$3:$C$6», чтобы при протягивании формулы вниз диапазон не смещался. Чтобы не расставлять знаки «$» вручную, нажмите один раз на кнопку F4 на клавиатуре сразу после выделения диапазона (когда часть формулы с диапазоном подсвечивается синим в строке ввода формул).
– Номер_столбца / Индекс
Число, которое указывает, из какого по счёту столбца указанного диапазона вернуть значение. Например, если диапазон таблицы – «A3:C6», то «номер_столбца = 2» вернёт данные из столбца «B» этого диапазона, 3 – из столбца «C» и т.д.
То есть значение мы ищем в самом первом столбце, а возвращаем из любого нужного!
– Интервальный_просмотр / Сортированный диапазон поиска (необязательный)
Логическое значение: ИСТИНА / ЛОЖЬ или 1 / 0. Определяет тип поиска:
- ИСТИНА (приближённый поиск) – Calc допускает ближайшее меньшее или равное значение, если точного совпадения нет. Для такого режима диапазон поиска должен быть отсортирован по возрастанию в первом столбце, иначе результат будет неверен.
- ЛОЖЬ (точный поиск) – Calc ищет только точное совпадение; если его нет – возвращается ошибка #Н/Д (N/A). Для идентификаторов, артикулов, паспортов и всех случаев, где нужно точное соответствие, всегда используйте значение ЛОЖЬ или 0!
Давайте немного попрактикуемся в использовании функции ВПР(). Вспомним реляционную базу данных из прошлой статьи. У нас есть 3 таблицы:
Таблица «Ученики»
Таблица «Предметы»
И таблица «Оценки»
Каждая из них находятся на своём, одноимённом листе.
В последнюю таблицу попадают оценки в течение года. Но представьте ситуацию, что вам нужно сформировать рейтинг по оценкам учеников и поощрить отличников и хорошистов.
Но вот беда, в таблице «Оценки» нет имён учеников, только их идентификаторы (ID)! Как раз в такой ситуации нам и пригодится функция ВПР().
Итак, наша задача состоит в том, чтобы перенести в таблицу «Оценки» все имена учеников из таблицы «Ученики» (второй столбец, «B») в соответствии с их идентификаторами из второго столбца «ID ученика» таблицы «Оценки».
Для этого давайте добавим еще один столбец справа от столбца «Оценка» и назовём его «Имя ученика».
В первую же строку этого столбца («E3») впишем такую формулу:
=ВПР(B3;$Ученики.$A$3:$C$6;2;0)
Разберём аргументы этой функции:
- «B3» – искомое значение (ID ученика в строке с оценкой)
- «Ученики.$A$3:$C$6» – где искать (фиксируем диапазон с $, чтобы при протягивании он не менялся)
- «2» – возвращаем столбец с именем (2-й столбец внутри диапазона «A:C»)
- «0» – точный поиск
После ввода этой функции нажимаем Enter и растягиваем значение ячейки «E3» вниз до конца таблицы. Для этого еще можно дважды кликнуть на маленький синий квадрат справа внизу выделенной ячейки «E3».
В итоге в столбце «Имя ученика» у нас перечислены имена в соответствии с их идентификаторами.
Аналогичным образом можем перенести названия предметов из таблицы «Предметы». Создаём справа новый столбец, в ячейке «F3» прописываем формулу:
=ВПР(C3;$предметы.$A$3:$C$6;2;0)
То есть в качестве критерия поиска указываем ячейку столбца «С» той же строки, что и наша формула (третья строка – «C3»).
Далее указываем массив. Это будет таблица «Предметы» с диапазоном от «A3» до «C6». Закрепим значения строк с помощью знака «$», чтобы они не изменялись при растягивании формулы вниз.
В качестве индекса столбца выбираем второй, где и находятся имена. Интервальный просмотр ставим в значение ЛОЖЬ, проще всего тут прописать эквивалентное значение – 0.
В итоге получаем столбец «F», в котором перечислены названия предметов в соответствии с их идентификаторами.
Фильтрация значений
Когда таблица становится большой, глазами уже трудно выцепить нужные данные. И тут на помощь приходят фильтры – инструмент, который позволяет временно скрыть ненужные строки и оставить только те, что соответствуют нашим условиям.
В LibreOffice Calc фильтры работают очень просто: вы выбираете столбец, по которому хотите отобрать данные, включаете автофильтр, и Calc показывает только те строки, что подходят под заданные параметры. Остальное скрывается, но никуда не исчезает – данные остаются в таблице, просто их не видно (обратите особое внимание на этот момент!).
Предположим, мы хотим узнать, кто у нас отличники – то есть у кого стоят только пятёрки. Для этого достаточно отфильтровать столбец «Оценка» и оставить в нём только числа 5.
Для фильтрации значений мы выделяем строку с заголовками таблицы. Далее вызываем в панели сверху функцию «Автофильтр» (значок воронки с молнией).
После этого возле каждого заголовка таблицы появляется иконка со стрелкой вниз. Нажимаем на такую иконку в ячейке «Оценка» («D3») и в выпадающем меню выбираем только значение 5.
В итоге иконка сменит свой цвет на синий, а в таблице останутся только те строки, в которых значение в столбце «Оценка» равняется 5.
Таким образом, мы отсортировали таблицу по значениям оценок учеников.
Аналогично можем оставить только «четвёрки» в таблице.
Обратите внимание, что номера строк слева стали синими и теперь идут не по порядку. Об этом мы и говорили в начале: лишние строки не удалились с листа, а просто скрылись.
Это может сыграть с вами злую шутку, например, если вы захотите подсчитать количество «отличников» в отфильтрованной таблице.
Для того чтобы подсчитать количество «пятёрок» можно к исходной таблице применить функцию СЧЁТЕСЛИ(), которая поможет подсчитать количество строк, в которых удовлетворяется заданное условие.
Синтаксис у функции СЧЁТЕСЛИ() такой:
=СЧЁТЕСЛИ(диапазон; условие)
- диапазон – это группа ячеек, по которым функция будет искать соответствие условию.
- условие – это критерий, по которому выбираются ячейки. Условие можно задать как число, текст, логическое выражение или ссылку на ячейку
В нашем случае, функции СЧЁТЕСЛИ() передадим диапазон значений оценок и в качестве условия счёта поставим значение 5.
В любой понравившейся ячейке (у нас это «D12») напишем такую формулу:
=СЧЁТЕСЛИ(D3:D10;5)
Здесь «D3:D10» – это диапазон значений для счёта, а второй аргумент – 5 – это как раз те значения, которые мы будем считать. В результате работы этой функции получим число 4 – именно столько «пятёрок» у нас в таблице.
Теперь предположим, что нам нужно подсчитать количество «пятёрок» только по информатике. Что же, функция для подсчёта уже написана, давайте просто отфильтруем таблицу?
К столбцу «Предмет» применим фильтрацию и оставим только строки с информатикой.
Но количество «пятёрок» по-прежнему 4! Это как раз и происходит потому, что фильтрация лишь скрывает для вас некоторые строки, но функции не исключают данные этих строк из своих вычислений.
То есть фильтр никак не влияет на расчёты стандартных функций вроде СУММ(), СРЗНАЧ(), СЧЁТ() и так далее.
Но все же подсчитать количество «пятёрок» без учёта скрытых строк мы можем. Для этого следует использовать функцию АГРЕГАТ(), работу с которой разберём в следующей статье.
Работа с функциями
Итак, мы уже научились переносить данные из разных таблиц по ключам с помощью ВПР() и фильтровать значения в таблице. Теперь давайте разберём некоторые функции для работы с данными в Calc.
Вспомним изначальную задачу в нашем примере: нужно собрать рейтинг отличников и хорошистов для поощрения.
Создадим таблицу на новом листе в Calc. В неё будем переносить имена учеников и вычислять их средний балл: подсчитаем количество оценок каждого ученика, сложим все его оценки и поделим сумму оценок на их количество. В конце отсортируем таблицу с учениками по убыванию среднего балла.
Начнём с имён. Для этого просто скопируем все имена учеников с одноимённой таблицы.
Теперь для каждого ученика подсчитаем количество его оценок. В этом нам снова поможет функция СЧЁТЕСЛИ().
В нашем случае диапазоном будут ячейки от «E3» до «E10» таблицы «Оценки», в которых указаны имена учеников. Условием же для подсчёта будем само имя, расположенное в столбце «А» нашей новой таблицы. В итоге получим такую формулу в ячейке «B2»:
=СЧЁТЕСЛИ($Оценки.$E$3:$E$10;A2)
Обратите внимание, что диапазон подсчёта мы закрепили знаком «$». Осталось лишь растянуть значение ячейки «B2» до конца таблицы.
Переходим к суммированию оценок. Для этого будем использовать функцию СУММЕСЛИ(). Она суммирует значения в диапазоне, которые соответствуют заданному критерию. Используется, когда нужно посчитать, например, общую сумму оценок по конкретному предмету или количество баллов, набранных учеником.
Синтаксис у этой функции следующий:
=СУММЕСЛИ(диапазон_условий; условие; [диапазон_суммирования])
- диапазон_условий – диапазон ячеек, к которым применяется условие.
- условие – критерий, по которому выбираются значения для суммирования. Может быть числом, текстом, логическим выражением или ссылкой на ячейку.
- диапазон_суммирования (необязательный) – диапазон ячеек, значения которых будут суммироваться. Если этот аргумент не указан, суммируются значения из «диапазон_условий».
Диапазоном условий у нас будет все тот же диапазон ячеек с именами (от «E3» до «E10»). В нём будем искать имя из столбца «А». Пока что все, как и в прошлом примере про СЧЁТЕСЛИ().
Но третьим аргументом функции СУММЕСЛИ() нам надо указать диапазон, значения которого и будут суммироваться. В нашем случае таким диапазоном является столбец с оценками – от «D3» до «D10».
Получаем такую формулу в ячейке «C2», которую необходимо растянуть на весь столбец таблицы:
=СУММЕСЛИ($Оценки.$E$3:$E$10;A2;$Оценки.$D$3:$D$10)
Теперь вычислим средний балл для каждого ученика. Здесь все очень просто: делим значение из столбца «C» на значение из столбца «B». Для ячейки «D2» будет такая формула:
=C2/B2
Растягиваем её вниз, и наша рейтинговая таблица готова.
Осталось лишь отсортировать строки таблицы по убыванию значений в столбце «D». Для этого применяем автофильтр к заголовку таблицы и выбираем пункт «Сортировка по убыванию» для столбца «D».
В итоге получаем такую таблицу:
Наш рейтинг готов, теперь можно смело раздавать благодарности лучшим ученикам!
Итоги
В этой статье мы подробно разобрали работу с данными в LibreOffice Calc. Мы начали с изучения функции ВПР(), которая позволяет переносить данные из одной таблицы в другую по уникальному идентификатору, что особенно полезно при работе с большими сводными таблицами и связанными данными.
Далее мы рассмотрели фильтрацию: научились выделять только нужные значения, скрывая остальные строки, и поняли, что функции Calc учитывают скрытые строки при подсчёте или суммировании.
Также мы освоили работу с функциями СЧЁТЕСЛИ() и СУММЕСЛИ(), которые позволяют считать количество элементов, удовлетворяющих условию, или суммировать значения по заданным критериям.
И, конечно, мы вспомнили про сортировку, которая помогает упорядочить данные и работать с ними более наглядно.
Всё это – фундаментальные навыки работы с данными в Calc. В следующей статье мы применим их на практике и научимся решать задание 3 ЕГЭ по информатике, используя полученные знания о функциях, фильтрах и работе с таблицами.