8 мин чтенияExcelтаблицыформулыВПРсортировкаКЕГЭ
Электронные таблицы и анализ данных
Формулы, абсолютные и относительные ссылки, функции ЕСЛИ, СУММЕСЛИ, ВПР, сортировка и фильтрация, сводные таблицы.
Открываем тему…
Формулы и ссылки
- Относительная ссылка A1 смещается при копировании; абсолютная 1 — нет; смешанные 1 фиксируют столбец или строку.
- Функции: СУММ, СРЗНАЧ, МИН, МАКС, СЧЁТ, СЧЁТЕСЛИ, СУММЕСЛИ, ЕСЛИ, И, ИЛИ.
- Остаток: ОСТАТ(a; b); целая часть: ЦЕЛОЕ(x); округление: ОКРУГЛ(x; n).
- Текст: ЛЕВСИМВ, ПРАВСИМВ, ПСТР, ДЛСТР, СЦЕПИТЬ.
ПримерУсловный подсчёт
В диапазоне A2:A1001 записаны значения. Сколько ячеек, больше 50 и чётных?
Решение
=СУММПРОИЗВ((A2:A1001>50)*(ОСТАТ(A2:A1001;2)=0)). Альтернатива: добавить столбец B с формулой =ЕСЛИ(И(A2>50;ОСТАТ(A2;2)=0);1;0) и просуммировать.
ПримерЧто получится при копировании формулы
В ячейке B1 записано число 10, в диапазоне A2:A5 — числа 1, 2, 3, 4. В ячейку B2 ввели формулу =A2*2+B1 и скопировали её в B3:B5. Чему равно значение B5?
Решение
Ссылки относительные, поэтому в каждой следующей строке формула ссылается на свою ячейку столбца A и на ячейку B строкой выше: B2 = 1·2 + 10 = 12; B3 = 2·2 + 12 = 16; B4 = 3·2 + 16 = 22; B5 = 4·2 + 22 = 30. Ответ — 30.
Анализ больших таблиц
Работа с данными подразумевает несколько шагов: очистка (убираем пустые строки и дубли), добавление вспомогательных столбцов, фильтрация, подсчёт. Для групповых итогов используйте сводные таблицы или функции СУММЕСЛИ и СЧЁТЕСЛИ. При поиске значения по ключу применяйте ВПР или ИНДЕКС с ПОИСКПОЗ.
- ВПР(ключ; диапазон; номер_столбца; 0) — точный поиск в первом столбце диапазона.
- Сортировка по нескольким полям: сначала главное поле, затем уточняющие.
- Графики: круговая — доли, гистограмма — сравнение, линейный — динамика.
ПримерГрупповые вычисления: СРЗНАЧЕСЛИ и СЧЁТЕСЛИМН
В диапазоне A2:A101 записаны названия отделов, в B2:B101 — зарплаты. Как найти среднюю зарплату в отделе «Склад» и количество сотрудников этого отдела с зарплатой больше 50 000?
Решение
Средняя зарплата: =СРЗНАЧЕСЛИ(A2:A101; "Склад"; B2:B101).
Число сотрудников: =СЧЁТЕСЛИМН(A2:A101; "Склад"; B2:B101; ">50000").
Для суммы по условию — СУММЕСЛИМН(B2:B101; A2:A101; "Склад"). Условия записываются в кавычках, знак сравнения и число — внутри одной строки.
ПримерПорядковые статистики и число уникальных значений
В диапазоне A2:A1001 записаны числа. Как найти второе по величине, третье по возрастанию и количество различных значений?
Решение
Второе по величине: =НАИБОЛЬШИЙ(A2:A1001; 2). Третье по возрастанию: =НАИМЕНЬШИЙ(A2:A1001; 3). Количество различных значений: =СУММПРОИЗВ(1/СЧЁТЕСЛИ(A2:A1001; A2:A1001)) — каждое значение, встречающееся k раз, даёт k слагаемых по 1/k, то есть ровно 1.
ПримерУсловия по строкам (шаблон для задания 9)
В каждой строке таблицы A2:F1001 записано шесть чисел. Нужно посчитать строки, в которых наибольшее число встречается ровно два раза.
Решение
В столбце G для каждой строки: =ЕСЛИ(СЧЁТЕСЛИ(A2:F2; МАКС(A2:F2)) = 2; 1; 0). Формулу протягивают вниз; ответ — =СУММ(G2:G1001). Тот же подход обобщается на любые условия по строке: сначала пишем формулу для одной строки, проверяем её на 2–3 строках вручную, затем копируем.