Microsoft Excel устроен таким образом, чтобы написать формулу один раз и растянуть на необходимые ячейки для расчета. Для этого требуется понимать, как использовать относительные и абсолютные ссылки.

По умолчанию ссылка на ячейку является относительной. Например, если в ячейке C1 записано =A1+B1, то при растягивании вниз программа сама изменит формулу на =A2+B2, =A3+B3 и так далее…

Абсолютная ссылка на ячейку наоборот сохраняет исходный вид при копировании. Для фиксации поставьте перед названиями столбца и строки знак доллара $. Формула будет ссылаться на одну и ту же ячейку при растягивании, если написать =$A$ 1.

Рассмотрим применение абсолютных ссылок на простом практическом примере. Сделаем таблицу умножения. Таблица умножения перемножает значения первой строки и первого столбца. Поэтому в ячейке B2 нужна формула =B$ 1*$A2. Теперь растягивая формулу вниз и вправо в каждой ячейке сохраняется логика таблицы умножения.

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

Применять его стоит не для красоты, а там где визуальная подсветка улучшает понимание данных:

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

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

Когда в таблице столько строк, что сложно обработать вручную, приходится анализировать данные по частям. Для этого в электронных таблицах существует фильтрация данных.

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

Если требуется работать с отфильтрованными данными в дальнейших расчетах, применяется фильтр с помощью формул. Функция ФИЛЬТР () создает новый лист на основе заданных условий. Настроенный с помощью формул фильтр обновляется автоматически при изменении исходных данных. Таким образом, можно быстро получать ответы на конкретные вопросы, не копируя данные вручную.

Если уже и фильтры не спасают, то придется взяться за тяжелую артиллерию и создать сводную таблицу.

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

Конечно, при крепких навыках написания формул можно сводную таблицу и вручную создать, но как же прекрасно, что в Microsoft Excel эта задача упрощена. Достаточно указать, какие поля будут строками, какие столбцами, а какие значениями. Перетаскивая поля мышкой, можно за секунды перестроить отчет и посмотреть на данные под другим углом.

По сути сводная таблица — это верх уплотнения данных и граница применимости табличных редакторов... но до этой границы нужно еще добраться в своей практике анализа данных.

Microsoft Excel позволяет создать диаграмму или график. При этом вы можете выбрать тип диаграммы из обширного списка: гистограммы, графики, круговые, кольцевые, линейчатые, пузырьковые, лепестковые… их очень много.

Обычно люди используют этот функционал примитивно и не правильно. Показательный пример приведен на рисунке. Нельзя на кольцевой диаграмме показывать проценты. Угол сектора в круге и так показывает проценты. Лучше отобразить абсолютное значение — 30 диаграмм похожи на пакмена, а 170 не похожи на пакмена.

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

Кроме того встроенные в Microsoft Excel диаграммы ограничены в настройках и лучше использовать для визуализации другие сервисы.

Когда исходные данные в таблице заполняют разные люди, каждый вводит данные по-своему. Если ввести лишний пробел, точку вместо запятой, текст вместо числа или другой формат даты, то формулы начинают работать неправильно.

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

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

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

При попытке сделать ссылку на ячейку, в которой пишите формулу, вы увидите сообщение о циклической ссылке. Иногда такой цикл — это и есть правильная модель.

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

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

Итерации нужны там, где без них модель теряет смысл. Если задачу можно выразить формулой без обратных ссылок, так будет надежнее и быстрее. Сам я не использую итеративные вычисления и считаю, что это показатель того, что пора переходить решению задачи с помощью программирования.

В Google Таблицах для этого появилась экспериментальная функция.

AI ()
Создает краткие пересказы, группирует контент по категориям или генерирует текст с помощью Gemini, учитывая значение в указанном диапазоне.

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

GOOGLEFINANCE ()
Помогает получить текущие или архивные сведения о ценных бумагах из сервиса Google Финансов.

GOOGLETRANSLATE ()
Переводит текст с одного языка на другой с помощью сервиса Google Переводчик.

А с другой стороны, она открывает совершенно новый пласт возможностей. Google Таблицы превращаются мощный аналитический инструмент для работы с текстом. Теперь можно работать и с некачественными текстовыми данными:

• категорировать комментарии,
• нормализовать формулировки,
• описать свойства предметов,
• генерировать поясняющий текст.

Важно помнить, что функция экспериментальная. Это не серебряная пуля и не замена мышлению. Зато как инструмент для первичной обработки текстовых данных, наведения порядка в комментариях и ускорения рутинных операций — она отлично вписывается в логику электронных таблиц.