Инструменты анализа данных и информации: Excel, Power BI, SQL, Python и что выбрать под задачу

Инструменты анализа данных (и шире — анализа информации) — это программы и языки, которыми аналитик достает данные из источников, очищает их, считает показатели и показывает результат. Основной набор в 2026 году: Excel с Power Query для таблиц до сотен тысяч строк, SQL для выборок из баз данных, Python с библиотекой pandas для повторяемой обработки и статистики, BI-системы (Power BI, Yandex DataLens и аналоги) для дашбордов и веб-аналитика (Яндекс Метрика, Google Analytics 4) для данных о поведении на сайте.

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

Мини-словарь: что с чем не путать

Инструменты часто сравнивают «в лоб», хотя они решают разные шаги одной цепочки.

• Источник данных — где данные рождаются: CRM, база заказов, счетчик на сайте, выгрузка из 1С.
• Хранилище — где данные лежат для анализа: база данных (PostgreSQL, ClickHouse, SQL Server) или просто файлы CSV/XLSX.
• Инструмент обработки — чем данные достают, чистят и считают: SQL, Power Query, pandas.
• Инструмент визуализации (BI) — чем результат показывают людям: сводная диаграмма, дашборд Power BI или DataLens.

Упрощенно цепочка выглядит так: источник -> хранилище -> обработка -> визуализация -> решение. В маленькой задаче все шаги помещаются в один файл Excel, в большой — это разные системы и разные люди.

Таблица выбора: задача -> инструмент

Цифра «100 тыс. строк» — ориентир для комфортной работы, а не лимит. Жесткий предел листа Excel — 1 048 576 строк, но формулы и сводные тормозят заметно раньше, и зависит это от компьютера и сложности книги.

Excel и Power Query

Excel остается первым инструментом аналитика: в нем быстро посмотреть выгрузку, отфильтровать, построить сводную таблицу и диаграмму. Минимальный набор: сводные таблицы, ВПР (VLOOKUP) или ПРОСМОТРX (XLOOKUP), СУММЕСЛИМН, условное форматирование, удаление дубликатов. ПРОСМОТРX есть только в Excel 2021, 2024 и Microsoft 365: в Excel 2019 и старше, а также при открытии файла в них формула не работает, там остается ВПР или ИНДЕКС+ПОИСКПОЗ.

Слабое место Excel — ручные действия. Если каждую неделю вы копируете три выгрузки на один лист и чистите их руками, ошибка рано или поздно попадет в отчет. Эту работу забирает Power Query: в Excel он находится на вкладке «Данные -> Получить данные» и встроен начиная с Excel 2016.

Power Query записывает шаги обработки (удалить столбец, изменить тип, объединить таблицы) и повторяет их по кнопке «Обновить». Под капотом шаги хранятся на языке M. Источники — файлы, папки с файлами, базы данных, веб-страницы; конкретный список коннекторов зависит от версии и редакции Office.

Важно различать: Power Query готовит данные, Power Pivot строит модель из нескольких таблиц и считает меры на языке DAX. Это две разные надстройки, хотя их часто называют вместе.

BI-системы: Power BI, DataLens и аналоги

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

• Power BI (Microsoft) — Power Query для подготовки, DAX для мер, публикация в облачный сервис. Desktop-версия бесплатна, совместная работа в облаке требует лицензий.
• Yandex DataLens — BI в Yandex Cloud, подключается к ClickHouse, PostgreSQL, Метрике и другим источникам.
• Metabase, Apache Superset — open source, ставятся на свой сервер, дашборды строятся поверх SQL-запросов.

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

SQL: выборки из баз данных

Если данные компании лежат в базе, SQL — основной способ их достать. Запрос описывает, что нужно получить, а СУБД сама решает, как это посчитать. Для работы с запросами используют DBeaver, DataGrip или встроенные редакторы BI-систем.

Пример ниже работает в SQLite (учебная среда, не требует сервера; проверено в SQLite 3.51). Те же запросы без изменений выполнятся в PostgreSQL (проверено на PostgreSQL 16): суммы совпадут, но AVG по целому столбцу там вернет тип numeric и покажет 4000.0000000000000000 — для отчета округляют через ROUND(AVG(amount), 2). Задача: выручка, число заказов и средний чек по городам.

CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    city     TEXT NOT NULL,
    amount   INTEGER NOT NULL  -- сумма заказа в рублях
);
CREATE TABLE payments (
    payment_id INTEGER PRIMARY KEY,
    order_id   INTEGER NOT NULL REFERENCES orders(order_id),
    paid       INTEGER NOT NULL  -- оплата частями: у заказа может быть несколько платежей
);
INSERT INTO orders VALUES
    (1, 'Москва', 5000), (2, 'Москва', 3000),
    (3, 'Казань', 4000), (4, 'Казань', 1000);
INSERT INTO payments VALUES
    (1, 1, 2500), (2, 1, 2500),   -- заказ 1 оплачен двумя платежами
    (3, 2, 3000), (4, 3, 4000);   -- заказ 4 еще не оплачен

SELECT city,
       COUNT(*)    AS orders,
       SUM(amount) AS revenue,
       AVG(amount) AS avg_check
FROM orders
GROUP BY city
ORDER BY revenue DESC;

Результат (sqlite3 -header -column):

city    orders  revenue  avg_check
------  ------  -------  ---------
Москва  2       8000     4000.0
Казань  2       5000     2500.0

GROUP BY сворачивает заказы по городу, SUM и AVG считают итог внутри каждой группы. В Москве два заказа на 8000, средний чек 4000 — сверяется в уме по исходным данным.

Частая ошибка: JOIN размножает строки

Теперь нужно добавить, сколько по заказам уже оплачено. Первое, что приходит в голову, — присоединить платежи и сложить:

-- неверно: JOIN размножает строки заказа по числу платежей
SELECT o.city, SUM(o.amount) AS revenue, SUM(p.paid) AS paid
FROM orders o
LEFT JOIN payments p ON p.order_id = o.order_id
GROUP BY o.city
ORDER BY o.city;

city    revenue  paid
------  -------  ----
Казань  5000     4000
Москва  13000    8000

Выручка Москвы выросла с 8000 до 13000, хотя заказы не менялись. Причина: у заказа 1 два платежа, после JOIN он превратился в две строки, и его 5000 посчитались дважды. Запрос не падает, цифра просто неверная — поэтому ошибка опасна.

Исправление — сначала свести платежи до одной строки на заказ, потом объединять:

-- верно: сначала свести платежи до одной строки на заказ
WITH paid_by_order AS (
    SELECT order_id, SUM(paid) AS paid
    FROM payments
    GROUP BY order_id
)
SELECT o.city,
       SUM(o.amount)                AS revenue,
       SUM(COALESCE(pb.paid, 0))    AS paid
FROM orders o
LEFT JOIN paid_by_order pb ON pb.order_id = o.order_id
GROUP BY o.city
ORDER BY o.city;

city    revenue  paid
------  -------  ----
Казань  5000     4000
Москва  8000     8000

Практическое правило: перед JOIN проверьте, сколько строк на один ключ в каждой таблице. Если «один ко многим» и вы суммируете поле со стороны «одного» — агрегируйте «многих» заранее. Та же ловушка есть в Power Query (слияние запросов) и в модели Power BI.

Python и pandas: повторяемый анализ

Python берут, когда расчет нужно повторять, проверять и развивать: склеить десятки файлов, посчитать статистику, построить прогноз, проверить A/B-тест. Основная библиотека для таблиц — pandas, для графиков — matplotlib или seaborn, для статистики — SciPy и statsmodels.

Тот же отчет и та же ловушка на pandas (проверено на Python 3.14 и pandas 3.0):

import pandas as pd

orders = pd.DataFrame({
    "order_id": [1, 2, 3, 4],
    "city":     ["Москва", "Москва", "Казань", "Казань"],
    "amount":   [5000, 3000, 4000, 1000],
})
payments = pd.DataFrame({
    "order_id": [1, 1, 2, 3],
    "paid":     [2500, 2500, 3000, 4000],
})

# 1. Выручка по городам - то же, что GROUP BY в SQL
report = (
    orders.groupby("city")
    .agg(orders=("order_id", "count"),
         revenue=("amount", "sum"),
         avg_check=("amount", "mean"))
    .sort_values("revenue", ascending=False)
)
print(report)

# 2. Ошибка: merge размножает заказ 1 по числу платежей
wrong = orders.merge(payments, on="order_id", how="left")
print(wrong.groupby("city")["amount"].sum())

# 3. Исправление: сначала одна строка оплаты на заказ
paid = payments.groupby("order_id", as_index=False)["paid"].sum()
fixed = orders.merge(paid, on="order_id", how="left",
                     validate="one_to_one")
fixed["paid"] = fixed["paid"].fillna(0).astype("int64")
print(fixed.groupby("city").agg({"amount": "sum", "paid": "sum"}))

Вывод:

        orders  revenue  avg_check
city                              
Москва       2     8000     4000.0
Казань       2     5000     2500.0
city
Казань     5000
Москва    13000
Name: amount, dtype: int64
        amount  paid
city                
Казань    5000  4000
Москва    8000  8000

Цифры совпадают с SQL, включая неверные 13000. Полезная защита — параметр validate у merge: если в ошибочный вариант добавить validate="one_to_one", pandas остановит расчет с MergeError: Merge keys are not unique in right dataset; not a one-to-one merge. Строка fillna(0) нужна, потому что у неоплаченного заказа 4 после объединения нет платежа: столбец paid становится дробным (float64) с пропуском NaN, и без замены пропуска приведение astype("int64") упадет.

Веб-аналитика и продуктовая аналитика

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

• Яндекс Метрика — бесплатна, источники трафика, цели, Вебвизор (запись сессий), выгрузка сырых данных через Logs API.
• Google Analytics 4 — модель на событиях; прежний Universal Analytics перестал обрабатывать данные в 2023 году, старые инструкции по нему неактуальны.
• Продуктовая аналитика (Mixpanel, Amplitude, AppMetrica) — воронки, удержание и когорты по событиям внутри продукта.

Отчеты в интерфейсе — это агрегаты с правилами системы (сессии, атрибуция, иногда семплирование). Для точных расчетов данные выгружают в базу и считают SQL или pandas. Цифры двух систем на одном сайте почти никогда не совпадают до единицы: они по-разному определяют визит и фильтруют роботов.

С чего начать: порядок освоения

1. Excel: сводные таблицы, ПРОСМОТРX (в Excel 2019 и старше — ВПР), чистка данных. Артефакт — отчет по открытой выгрузке с выводом в одну фразу.
2. SQL: SELECT, WHERE, GROUP BY, JOIN, CTE. Артефакт — 10 запросов к учебной базе, у каждого проверен результат.
3. BI: дашборд из 3-5 графиков поверх вашей базы в DataLens, Metabase или Power BI.
4. Python и pandas: повторить отчет из шага 1 скриптом и добавить то, что в Excel неудобно (склейка файлов, статистика).

Шаги 1-2 закрывают большую часть вакансий начального уровня; шаги 3-4 нужны, когда отчеты становятся регулярными и сложными. Сроки у всех разные и зависят от часов в неделю.

Если не получилось:

• Сумма в отчете больше, чем в исходнике — проверьте JOIN или слияние на дубли ключей (раздел выше).
• Excel «зависает» на обновлении — вынесите обработку в Power Query или в базу, оставьте в книге только итог.
• Цифры Метрики и CRM расходятся — сравнивайте одинаковые сущности: визиты с визитами, заказы с заказами, за один и тот же часовой пояс.
• В pandas столбец с суммами стал object — в данных есть текст или пробелы; приведите тип через pd.to_numeric(..., errors="coerce") и посмотрите, какие строки стали пропусками.

Выводы

• Инструменты анализа данных решают разные шаги цепочки: обработка (Excel, Power Query, SQL, pandas), визуализация (BI), сбор поведения (веб- и продуктовая аналитика).
• Выбирать стоит по задаче: разовый отчет — Excel, регулярная чистка файлов — Power Query, данные в базе — SQL, повторяемый расчет и статистика — Python.
• Один и тот же расчет дает одинаковый результат в SQL и pandas, если правильно понимать группировку и объединение.
• Самая частая тихая ошибка — JOIN или merge по ключу «один ко многим» перед суммированием; лечится агрегацией до объединения и проверкой validate.

Где применяется / связь с практикой

FAQ

Можно ли обойтись только Excel?
Для разовых отчетов по небольшим выгрузкам — да. Когда данные лежат в базе или отчет обновляется регулярно, без SQL и Power Query ручная работа начинает съедать время и порождать ошибки.

Что учить первым: SQL или Python?
Обычно SQL: он короче в освоении, и почти любая аналитическая задача начинается с выборки из базы. Python добавляют, когда нужна автоматизация и статистика.

Чем анализ данных отличается от Data Mining?
Анализ данных отвечает на заданный вопрос (сколько, где, почему упало), Data Mining ищет в больших массивах закономерности, о которых заранее не спрашивали: кластеры, ассоциации, аномалии.