Подготовка и проверка данных

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

Подготовка данных — приведение таблицы к виду, пригодному для расчётов: удаление дубликатов, заполнение или учёт пропусков, согласование типов и форматов значений.

Есть инженерное правило: «мусор на входе — мусор на выходе» (англ. garbage in, garbage out). Самая красивая формула выдаст ерунду, если в столбце «цена» половина чисел записана как текст, а один город указан тремя разными способами. Поэтому грамотный расчёт всегда начинается с уборки.

Зачем это нужно

Реальные таблицы почти всегда грязные: данные собирали разные люди, копировали из разных источников, что-то ввели вручную с опечатками. Если не вычистить их, СЧЁТЕСЛИ «не увидит» строки с лишним пробелом, а СУММ пропустит числа-как-текст. На экзамене за это снимают баллы; в работе — это неверные отчёты и решения.

Три главные проблемы

Дубликаты

Повторяющиеся строки завышают количество и суммы. В редакторе их убирают командой «Удалить дубликаты» (выделить таблицу → меню «Данные»). Найти повторы помогает СЧЁТЕСЛИ по самому столбцу: если значение встречается чаще одного раза — это дубль.

=СЧЁТЕСЛИ(A$2:A$1001; A2)

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

Пропуски

Пустые ячейки искажают среднее. Здесь важно различать: СРЗНАЧ пустые ячейки игнорирует (не считает их нулями), а вот если вместо пропуска стоит ноль — он войдёт в среднее и занизит его. Посчитать пропуски можно через СЧИТАТЬПУСТОТЫ(диапазон). Решение зависит от смысла: иногда пропуск — это «нет данных» (исключаем), иногда — «ноль» (заполняем).

Типы и форматы

Самая коварная беда — число, записанное как текст (например, импортировано с пробелом или с запятой вместо точки в десятичной части). Внешне «1000» и «1000» неотличимы, но текст не суммируется. Признак: число-текст по умолчанию прижато влево, настоящее число — вправо. Лечится сменой формата ячейки и заменой запятых на точки через «Найти и заменить».

Проверка данных и выпадающие списки

Лучше не чистить мусор потом, а не пускать его сразу. Для этого есть «Проверка данных» (Data Validation): на ячейку накладывают правило — например, разрешить только числа от 0 до 100 или только значения из заранее заданного списка. Выпадающий список гарантирует, что город выберут из «Берёзовка / Дубки / Сосновка», а не наберут с опечаткой. Это превращает свободный ввод в выбор из множества и исключает разнобой.

Как это работает

Сравним «грязный» и «чистый» расчёт на примере: в данных есть дубликат и пропуск. Покажем, как меняется среднее, если их не учесть. Для наглядности возьмём только корректные числа и сравним с наивной суммой:

raw = ["88", "", "72", "88", "90", " 81 "]  # пустая строка и пробелы — мусор
# наивно: длина включает пустые, текст не превратить в число напрямую
clean = []
for x in raw:
    s = x.strip()
    if s != "":
        clean.append(int(s))
# уберём дубликат значения 88, оставив одно вхождение
unique = []
for v in clean:
    if v not in unique:
        unique.append(v)
avg_all = sum(clean) / len(clean)
avg_unique = sum(unique) / len(unique)
print("Чистых значений:", len(clean))
print("Среднее со всеми:", round(avg_all, 1))
print("Среднее без дубля:", round(avg_unique, 1))

Вывод:

Чистых значений: 5
Среднее со всеми: 83.8
Среднее без дубля: 80.75

Видно сразу: пустую ячейку и пробелы пришлось отсеять (strip убрал пробелы, пустые отброшены), иначе значения вообще не превратились бы в числа. А удаление дубликата заметно изменило среднее — с 83,8 до 80,75. Та же логика и в таблице: пока данные грязные, любой агрегат врёт. Формально среднее зависит и от состава, и от количества элементов:

$$ \bar{x} = \frac{1}{n}\sum_{i=1}^{n} x_i $$

лишний дубль увеличивает и числитель, и $n$, но непропорционально — поэтому результат смещается.

Частые ошибки

Считают по грязным данным. Сначала уборка, потом формулы — не наоборот.
Путают пропуск и ноль. Пустая ячейка в среднее не входит, а ноль входит и занижает его. Решайте по смыслу данных.
Не замечают число-текст. Если СУММ «потеряла» значения, проверьте формат: текст прижат влево.
Лишние пробелы. «Берёзовка » с пробелом на конце — для машины не то же, что «Берёзовка». Помогает функция СЖПРОБЕЛЫ.
Удаляют дубликаты на глаз. Глаз пропускает повторы в большой таблице — используйте «Удалить дубликаты» или СЧЁТЕСЛИ.

Итоги

«Мусор на входе — мусор на выходе»: расчёт точен ровно настолько, насколько чисты данные.
Три врага — дубликаты, пропуски, неверные типы (число-текст, запятые, пробелы).
Дубликаты ищите через СЧЁТЕСЛИ и убирайте командой «Удалить дубликаты»; пробелы — СЖПРОБЕЛЫ.
Пропуск и ноль — разные вещи: пустую ячейку среднее игнорирует, ноль — нет.
Проверка данных и выпадающие списки не дают мусору попасть в таблицу с самого начала.