Автор статьи: Андрей Роженцов
Введение
Электронные таблицы давно стали незаменимым инструментом для анализа данных в учёбе, работе и повседневной жизни. Однако многие пользователи применяют лишь базовые возможности, не подозревая о мощных функциях, способных автоматизировать рутину и решать сложные задачи за минуты. На практических примерах мы разберём ключевые инструменты — от сортировки и фильтрации до функции ВПР. Освоив их, вы не только упростите себе работу с данными, но и сможете уверенно решать задание № 3 ЕГЭ по информатике. Оно полностью ориентировано на владение этими навыками.
Сортировка
Рассмотрим таблицу, в которой информация о товарах распределена по категориям «Отдел», «Наименование» и «Цена за упаковку».
Отсортируем таблицу таким образом, чтобы записи шли по возрастанию цены. Для этого выделяем таблицу и нажимаем кнопку «Сортировка и фильтр».
Нажимаем «Настраиваемая сортировка».
В открывшемся окне мы можем выбрать, по какому столбику сортировать таблицу и в каком порядке (для чисел — по возрастанию или по убыванию, для текста — по алфавиту или в обратном порядке).
В качестве столбика выбираем «Цена за упаковку», порядок — по возрастанию.
Теперь записи в таблице идут по возрастанию цены.
Для сортировки данных по двум критериям одновременно в настройках сортировки необходимо добавить дополнительный уровень.
Тогда записи будут отсортированы по названию отдела в алфавитном порядке, а при совпадении отделов записи сортируются по возрастанию цены.
Фильтр
Теперь, когда мы умеем упорядочивать данные, давайте научимся отбирать их по заданным критериям.
Цель: оставить только товары из отдела «Макароны».
Первый шаг похож: выделяем таблицу (можно только первую строку с названиями столбцов), нажимаем кнопку «Сортировка и фильтр», выбираем «Фильтр».
В заголовках столбцов появятся кнопки-стрелки. Нажимаем на кнопку в том столбце, по которому хотим отфильтровать данные. Нас интересует «Отдел».
В появившемся окне выбираем галочками те названия отделов, которые хотим оставить.
При большом количестве вариантов искомое значение можно ввести в текстовое поле, этот вариант рассмотрим позже.
Результат:
Обратите внимание на нумерацию строк: 1, 7, 8. Строки, которые не подходят под фильтр, никуда не пропадают, они просто скрываются. Если убрать фильтр, то эти строки снова станут видимыми.
При копировании таблицы со скрытыми ячейками будут скопированы только видимые!
Перенесём данные о товарах из отдела «Макароны» на новый лист. Выделяем всю таблицу, нажимаем на неё правой кнопкой мыши $\to$ «Копировать» (или сочетание клавиш CTRL + C).
В левой нижней части экрана нажимаем на значок + и создаём новый лист.
Выделяем любую ячейку, нажимаем на неё правой кнопкой мыши $\to$ «Вставить» (или используем сочетание клавиш CTRL + V).
Результат:
Мы перенесли на новый лист информацию о товарах из отдела «Макароны». Обратите внимание на нумерацию строк: 1, 2, 3. Здесь нет скрытых ячеек! Скопировались только те записи, которые были видимыми.
Основные функции
После фильтрации или упорядочивания данных нужно научиться с ними работать и высчитывать различные показатели.
При написании формулы или использовании функции первым символом в ячейке идёт значок =.
СУММ()суммирует диапазон ячеек
Сложим стоимости всех товаров:
Результат:
В функцию СУММ можно передавать несколько диапазонов для суммирования через точку с запятой; в качестве диапазона может выступать одна ячейка.
СЧЁТ()считает количество ячеек с числами
Найдём количество товаров, сосчитав количество цен на них в таблице.
Результат:
Если выбрать диапазон для подсчёта C1:C8, то результат не изменится, т. к. в ячейке C1 записано не число, а текст.
СРЗНАЧ()находит среднее арифметическое
Результат:
ВАЖНЫЙ МОМЕНТ
Функции СУММ(), СЧЁТ() и СРЗНАЧ() работают и со скрытыми с помощью фильтров ячейками. Рассмотрим подробнее.
Исходная таблица:
Скроем фильтром все товары из отдела «Печенье».
Запишем формулу СУММ(), в качестве диапазона выделим ячейки с ценой.
Результат:
ОШИБКА!
Стоимость конфет должна была получиться $100 + 85 + 55 = 240$, но мы выбрали диапазон суммирования C2:C6, значит, сложились значения всех ячеек из этого диапазона, даже скрытых.
Существует два простых способа найти правильный ответ.
- Выделим ячейки, числа в которых хотим сложить, и в правой нижней части экрана появится краткая информация об этом диапазоне. В этом случае учитываются только видимые ячейки.
Это строка состояния. Отображаемые показатели можно изменять, нажав сюда правой кнопкой мыши (например, добавить вывод максимального или минимального числа).
- Скопируем таблицу на новый лист — скрытых ячеек в нём не окажется, и мы сможем воспользоваться функцией
СУММ().
Результат:
Функция ВПР
Рассмотрим пример.
В продуктовом магазине распечатан прайс-лист с информацией обо всех товарах. Во время работы продавщица должна вести учёт проданной продукции, но из-за большого количества покупателей она успевает записывать только названия проданных товаров, а после закрытия магазина дописывает к ним цены.
Вид таблицы к концу рабочего дня:
Продавщице надоело тратить время на заполнение таблицы, и она решила оптимизировать процесс. Для этого ей нужно понять алгоритм собственных действий.
- Посмотреть на название проданного товара
- Найти товар с тем же название в таблице «Прайс-лист»
- В найденной строке посмотреть на столбик «Цена за упаковку» — это и будет искомое значение
- Записать это число в таблицу с продажами
Какое совпадение: у функции ВПР() аналогичный метод работы!
Синтаксис:
ВПР (искомое значение; таблица; номер_столбца; [интервальный просмотр])\
ВПР() — это функция, которая находит заданное значение в крайнем левом столбце указанного диапазона и возвращает соответствующее значение из любого другого столбца в этой же строке.
Применим ВПР к нашей задаче.
В качестве искомого значения указываем сыр «Брынза».
Второй параметр — таблица, в которой ищем. Выделяем столбики A:E.
Третий параметр — номер столбца в выделенной таблице, из которого нужно взять информацию. Столбцы нумеруются слева направо начиная с 1. «Название товара» — 1, «Отдел» — 2, «Единица измерения» — 3, «Количество в упаковке» — 4, «Цена за упаковку» — 5. Нам нужна цена, поэтому указываем 5.
Четвёртый параметр — интервальный просмотр. ИСТИНА, если первый столбец отсортирован по возрастанию, ЛОЖЬ, если нет. Указываем ВСЕГДА ЛОЖЬ и не задумываемся.
Результат:
Чтобы протянуть формулу вниз, дважды щёлкаем на квадратик, расположенный в правом нижнем углу ячейки. Для автоматического растяжения необходимо, чтобы слева был столбец с данными. Если он будет пустым, формула не протянется.
Результат:
Важный нюанс: при указании таблицы в первой формуле мы выделили полностью столбцы A:E, поэтому при протягивании формулы вниз во всех ячейках этот параметр остался тем же. Но если вы выделите таблицу A2:E9, не забудьте закрепить её символами «$», иначе при растяжении формулы таблица съедет.
Теперь мы умеем сортировать таблицы и пользоваться функциями, а ещё знаем все тонкости фильтрации записей. Рассмотрим задание № 3 ЕГЭ.
Задача 1: применяем фильтры
Скачиваем и открываем файл.
Вся информация о продажах и поставках находится на первом листе, «Движение товаров», но здесь мы не видим ни названий товаров (только их артикулы), ни адресов магазинов (только их ID).
Найдём сыр бри на вкладке «Товар». Для этого выберем фильтр в столбце «Наименование товара» и, чтобы не искать вручную среди множества вариантов, введём «Бри» в строку поиска. У неподходящих вариантов снимем галочки.
Результат:
Запомним его артикул: 127.
Аналогично на вкладке «Магазин» наложим фильтр на столбец «Адрес». В строке поиска введём «Почтовый». По условию нам нужен именно Почтовый бульвар, а не переулок, поэтому снимем галочки с неподходящих полей.
Результат:
Запомним ID магазинов: М78, М147.
Теперь на основном листе «Движение товаров» мы можем фильтром выбрать сыр бри (артикул 127) и магазины, находящиеся на Почтовом бульваре, — ID магазинов М78 и М147.
Фильтр по артикулу:
Фильтр по ID магазина:
Согласно условию нас интересуют именно операции по продаже сыра, поэтому тип операции выставляем «Продажа».
В таблице осталось только две записи, по дате они обе подходят, поэтому здесь фильтр ставить не нужно.
Чтобы определить суммарный проданный вес, сосчитаем количество проданных упаковок. Выделяем ячейки и в нижней части экрана видим сумму значений. Скрытые ячейки не учитываются!
Получилось $80$ упаковок. Перейдём на лист «Товар» и увидим, что в одной упаковке $100$ граммов.
Тогда $80 \cdot 100 = 8000$ грамм, $8000 : 1000 = 8$ кг.\
Ответ: $8$
Задача 2: применение ВПР
На листе «Товар» выберем все виды сметаны.
Нашли артикулы: 20, 21, 22, 23, 24.
На листе «Магазин» выставим фильтр на адрес.
Нашли ID магазинов: М13, М15.
На листе «Движение товаров» выставим фильтр по артикулам 20, 21, 22, 23, 24 и по ID магазинов М13, М15.
Тип операции — продажа.
По условию задачи, дата — с 1 по 15 октября включительно, в таблице есть всего два подходящих дня: 7 и 14 октября.
Вид таблицы после наложения всех фильтров:
Осталось сосчитать сумму продажи всех этих товаров. У упаковки сметаны разной жирности своя цена, поэтому мы перенесём на этот лист стоимость одной упаковки.
По артикулу можем найти стоимость товара. Укажем его в качестве искомого значения.
Таблица: столбики A:F на листе «Товар».
Нас интересует цена за упаковку, номер столбца — 6. Интервальный просмотр — ЛОЖЬ.
Результат:
Двойным щелчком мыши по квадратику растягиваем формулу вниз.
Теперь для каждой продажи посчитаем её суммарную стоимость, ведь каждый раз было продано несколько упаковок. Для этого умножим количество упаковок на цену одной и полученную формулу растянем до конца.
Выделим все стоимости операций и внизу экрана увидим их сумму: 371 455.
Ответ: 371 455
Главное, что нужно запомнить, чтобы решить задачу и получить балл!
- ФИЛЬТР — ваш лучший друг
Фильтр только скрывает строки, а не удаляет. При копировании данных сохраняются только видимые ячейки.
В задании ЕГЭ № 3 начинаем с фильтров. Нашли артикулы товаров и ID магазинов на отдельных листах, потом отфильтровали ими основную таблицу с операциями.
- СУММ, СРЗНАЧ, СЧЁТ видят всё
Ошибка: если отфильтровать таблицу и посчитать =СУММ(C2:C100), то сложатся все числа в диапазоне, включая скрытые фильтром.
Есть два способа сложить только видимое:
• выделить нужные ячейки и посмотреть сумму в строке состояния внизу окна\
• скопировать отфильтрованную таблицу на новый лист —копируются только видимые ячейки
- ВПР — всегда «ЛОЖЬ»
Синтаксис: =ВПР( что искать; где искать; номер столбца; ЛОЖЬ)
В задании ЕГЭ всегда ставим четвёртый аргумент ЛОЖЬ для точного поиска. Если таблицу для поиска (A2:D10) задаём вручную, то закрепляем её ссылку —$A$2:$D$10.
Используя комбинации фильтров и функций, можно справиться с любой задачей № 3 ЕГЭ, а потренироваться в этом вы можете, решив подборку задач.