Основы анализа данных в Р7-Офисе

Основы анализа данных в Р7-Офисе

ЧТО МЫ БУДЕМ ДЕЛАТЬ

Здесь вы узнаете, как начать работу с данными в Р7-Офисе: импортировать их, сортировать и быстро получать базовую статистику. Это основа для всех последующих задач аналитики.

ИМПОРТ ДАННЫХ ИЗ CSV ВРУЧНУЮ

Откройте

Р7-Офис и создайте новую таблицу
Перейдите

в меню Файл > Открыть и выберите CSV-файл.
Если

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

ИСПОЛЬЗОВАНИЕ ФИЛЬТРОВ И СОРТИРОВКИ

Выделите

заголовки таблицы (например, "Дата", "Продажи").
В меню

Данные выберите Фильтр.
Появятся стрелки для фильтрации.
Нажмите

стрелку в колонке и выберите Сортировка по возрастанию или фильтр
(например, "Продажи > 100").

БЫСТРАЯ ОПИСАТЕЛЬНАЯ СТАТИСТИКА

Выделите

колонку с числами (например, "Продажи").
Посмотрите

внизу окна Р7-Офиса — в строке состояния отобразятся сумма, среднее и количество значений.

Пример: Подсчет дохода и среднего чека

Допустим, у вас есть таблица продаж:

Колонка A: "Дата";
Колонка B: "Продажи" (в рублях).

Выделите колонку B и посмотрите сумму в строке состояния — это общий доход.
Среднее значение — это средний чек.
В ячейке C1 введите =SUM(B:B) для итоговой суммы, а в D1 — =AVERAGE(B:B) для среднего.

Инструменты

Фильтры и сортировка из меню Данные.
Функции: SUM, AVERAGE, COUNT.

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

Очистка данных вручную

ЧТО МЫ БУДЕМ ДЕЛАТЬ

Очистка данных — первый шаг к их анализу. В этой главе вы научитесь удалять дубликаты, разделять текст и заполнять пропуски, используя только базовые функции Р7-Офиса.

УДАЛЕНИЕ ДУБЛИКАТОВ С ПОМОЩЬЮ ФИЛЬТРОВ И COUNTIFS

Выделите

колонку с данными (например, список email).
В меню

Данные выберите Фильтр.
В соседней

колонке (например, B) введите формулу: =COUNTIFS(A:A, A1) — она покажет,
сколько раз значение встречается.
Отфильтруйте

строки, где значение больше 1, и удалите лишние вручную.

РАЗДЕЛЕНИЕ ТЕКСТОВ НА СТОЛБЦЫ

Выделите

колонку с данными (например, список email).
В меню

Данные выберите Фильтр.
В соседней

колонке (например, B) введите формулу: =COUNTIFS(A:A, A1) — она покажет,
сколько раз значение встречается.
Отфильтруйте

строки, где значение больше 1, и удалите лишние вручную.

ПОИСК И ЗАМЕНА ПРОПУЩЕННЫХ ЗНАЧЕНИЙ

Выделите

колонку с пропусками (например, "Возраст").
В соседней

колонке введите: =IF(ISBLANK(A1), "Не указано", A1) — это заменит пустые ячейки на текст.
Скопируйте

значения и вставьте их вместо исходной колонки.

Пример: Очистка списка e-mail

У вас есть список email: "ivan@example.com", "anna@example.com", "ivan@example.com", некоторые ячейки пусты.

Используйте COUNTIFS, чтобы найти повторения (ivan@example.com встретится 2 раза).
Отфильтруйте и удалите дубликат.
Пустые ячейки заполните "Нет данных" с помощью IF и ISBLANK.

Инструменты

Фильтры из меню Данные.
Функции: COUNTIFS, IF, ISBLANK.

Цель
Вы научились приводить данные в порядок без сложных инструментов. Теперь они готовы к анализу!

Простая классификация с вероятностями

ЧТО МЫ БУДЕМ ДЕЛАТЬ

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

ПОДСЧЁТ ЧАСТОТ С COUNTIFS И СВОДНЫМИ ТАБЛИЦАМИ

Возьмите

таблицу с отзывами: колонка A — "Текст", колонка B — "Оценка" (1–5).
Создайте

сводную таблицу: в меню Вставка выберите Сводная таблица.
Укажите

"Оценка" в строках, а "Количество" в значениях — это покажет частоты.

ВЫЧИСЛЕНИЕ УСЛОВНЫХ ВЕРОЯТНОСТЕЙ

Добавьте

колонку C: "Положительный" (1 — если оценка ≥ 4, 0 — если < 4).
Формула:

=IF(B1>=4, 1, 0).
Используйте

SUMPRODUCT: =SUMPRODUCT(C1:C100)/COUNT(C1:C100) — доля положительных отзывов.

КЛАССИФИКАЦИЯ ВРУЧНУЮ С IF

В колонке

D введите: =IF(B1>=4, "Положительный", "Отрицательный").
Скопируйте

формулу вниз.

Пример: Классификация отзывов

У вас есть 10 отзывов с оценками 1–5.

Сводная таблица показывает: 3 оценки = 5, 2 = 4, 5 < 4.
Вероятность положительного отзыва: (3+2)/10 = 50%.
Формула IF классифицирует каждый отзыв.

Инструменты

Сводные таблицы.
Функции: COUNTIFS, SUMPRODUCT, IF.

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

Сегментация данных с кластеризацией

ЧТО МЫ БУДЕМ ДЕЛАТЬ

Сегментация делит данные на группы. Мы сделаем это вручную, используя средние значения и расстояния.

ГРУППИРОВКА ДАННЫХ ЧЕРЕЗ СВОДНЫЕ ТАБЛИЦЫ

Возьмите таблицу:

колонка A — "Клиент", B — "Покупки" (количество), C — "Сумма" (рубли).
Создайте

сводную таблицу с "Клиент" в строках, "Покупки" и "Сумма" в значениях (среднее).

РУЧНАЯ КЛАСТЕРИЗАЦИЯ

Найдите

общее среднее для "Покупки" и "Сумма" с AVERAGE.
В колонке D

введите: =SUMPRODUCT((B1-$B$100)^2, (C1-$C$100)^2) — расстояние до среднего (где B100, C100 — средние).
Отсортируйте

по D и выделите группы (например, "выше среднего", "ниже среднего").

Пример: Сегментация покупок вина

У вас 5 клиентов:

Клиент 1: 10 покупок, 5000 руб.
Клиент 2: 2 покупки, 1000 руб.

Среднее: 6 покупок, 3000 руб.
Расстояния: Клиент 1 — больше среднего, Клиент 2 — меньше.
Разделите на 2 группы вручную.

Инструменты

Сводные таблицы.
Функции: AVERAGE, SUMPRODUCT.

Цель
Вы научились сегментировать данные без сложных алгоритмов. Это полезно для анализа клиентов или продуктов.

Анализ связей через матрицы

ЧТО МЫ БУДЕМ ДЕЛАТЬ

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

ПОСТРОЕНИЕ МАТРИЦЫ СМЕЖНОСТИ С COUNTIFS

Возьмите таблицу:

колонка A — "Клиент", колонка B — "Товар".
Создайте список

уникальных товаров в колонке C
(вручную или через фильтр).
В ячейке D2

введите: =COUNTIFS(A:A, $A$1, B:B, C2) — сколько раз клиент купил товар.
Скопируйте

формулу, чтобы построить матрицу.

ПОДСЧЕТ СВЯЗЕЙ МЕЖДУ ОБЪЕКТАМИ

Внизу матрицы

добавьте: =SUM(D2:D10) — общее количество покупок каждого товара.
Отсортируйте

по убыванию, чтобы выделить популярные товары.

ВИЗУАЛИЗАЦИЯ ЧЕРЕЗ БАЗОВЫЕ ГРАФИКИ

Выделите

матрицу (например, C1:D5).
В меню

Вставка выберите Диаграмма > Столбцы.
Настройте

подписи осей (клиенты и товары).

Пример: Анализ покупок вина

У вас таблица: Клиент 1 купил "Вино А" и "Вино Б", Клиент 2 — "Вино А".

Матрица: Клиент 1 — 1 для А, 1 для Б; Клиент 2 — 1 для А.
Сумма: "Вино А" куплено 2 раза, "Вино Б" — 1 раз.
График показывает популярность товаров.

Инструменты

Функции: COUNTIFS, SUM.
Базовые графики из меню Вставка.

Цель
Вы научились выявлять связи без сложных инструментов. Это полезно для анализа предпочтений или зависимостей.

Линейная регрессия вручную

ЧТО МЫ БУДЕМ ДЕЛАТЬ

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

ПОСТРОЕНИЕ ФИКТИВНЫХ ПЕРЕМЕННЫХ

Возьмите таблицу:

колонка A — "Реклама" (тыс. руб.), B — "Продажи" (тыс. руб.).
Если есть категории

(например, "Тип рекламы"), добавьте колонку C с IF: =IF(A1>50, 1, 0) — фиктивная переменная.

ВЫЧИСЛЕНИЕ НАКЛОНА И ПЕРЕСЕЧЕНИЯ

В ячейке C1:

=SLOPE(B:B, A:A) — наклон линии.
В D1:

=INTERCEPT(B:B, A:A) — пересечение с осью.
Прогноз:

=C$1*A2+D$1 — продажи для каждой строки.

ПРОГНОЗЫ С БАЗОВЫМИ ФОРМУЛАМИ

Скопируйте

формулу прогноза вниз.
Проверьте

точность, вычитая прогноз из реальных продаж: =B2-E2.

Пример: Прогноз продаж

Реклама: 10, 20, 30; Продажи: 50, 60, 70.

SLOPE ≈ 1, INTERCEPT ≈ 40.
Прогноз для 25 тыс. руб.: 1*25 + 40 = 65 тыс. руб.

Инструменты

Функции: IF, SLOPE, INTERCEPT, SUMPRODUCT.

Цель
Вы освоили базовую регрессию для простых прогнозов, например, влияния рекламы на продажи.

Ансамбли простых решений

ЧТО МЫ БУДЕМ ДЕЛАТЬ

Мы создадим простую модель ансамбля (bagging) для классификации, используя случайность и подсчет голосов.

СОЗДАНИЕ СЛУЧАЙНЫХ ВЫБОРОК С RAND

Таблица:

колонка A — "Клиент", B — "Покупка" (1 — да, 0 — нет).
В колонке C:

=RAND() — случайное число для каждой строки.
Отсортируйте по C

и возьмите первые 5 строк как выборку.

ПОДСЧЕТ ГОЛОСОВ ЧЕРЕЗ COUNTIFS И SUM

Повторите выборку

3 раза (копируйте и сортируйте заново).
Для каждой

выборки в колонке D: =IF(B1=1, "Да", "Нет").
Подсчитайте голоса:

=COUNTIFS(D:D, "Да") и =COUNTIFS(D:D, "Нет").

ПРОСТОЙ BAGGING ВРУЧНУЮ

Итог:

если "Да" больше "Нет", классифицируйте как 1.
Проверьте

на исходных данных.

Пример: Классификация покупок

5 клиентов: 3 купили, 2 нет.

Выборка 1: 2 "Да", 1 "Нет".
Выборка 2: 1 "Да", 2 "Нет".
Итог: "Да" побеждает (2 голоса против 1).

Инструменты

Функции: RAND, COUNTIFS, SUM, IF.

Цель
Вы научились улучшать классификацию с помощью простого ансамбля.

Простое прогнозирование спроса

ЧТО МЫ БУДЕМ ДЕЛАТЬ

Мы спрогнозируем спрос с помощью экспоненциального сглаживания и случайных сценариев.

ЭКСПОНЕНЦИАЛЬНОЕ СГЛАЖИВАНИЕ (SES)

Таблица:

колонка A — "Месяц", B — "Спрос".
В C2:

=0.3*B1+0.7*(C1) (0.3 — коэффициент сглаживания, C1 — предыдущий прогноз).
Скопируйте

вниз для прогноза.

РУЧНОЙ РАСЧЕТ СКОЛЬЗЯЩЕГО СРЕДНЕГО

В D2:

=AVERAGE(B1:B3) — среднее за 3 месяца.
Скопируйте

вниз.

СЛУЧАЙНЫЕ СЦЕНАРИИ С RAND

В E2:

=NORM.INV(RAND(), C2, 10) — сценарий с разбросом ±10.

Пример: Прогноз спроса на мечи

Спрос: 100, 110, 105.

SES: C2 = 100, C3 = 103, C4 ≈ 104.
Скользящее среднее: 105.
Сценарий: 104 ± 10.

Инструменты

Функции: IF, SUM, AVERAGE, RAND, NORM.INV.

Цель
Вы освоили базовое прогнозирование для планирования спроса.

Оптимизация вручную

ЧТО МЫ БУДЕМ ДЕЛАТЬ

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

ПОДСЧЕТ ЗАТРАТ И ОГРАНИЧЕНИЙ С SUMPRODUCT

Таблица:

колонка A — "Продукт", B — "Количество", C — "Стоимость".
В D1:

=SUMPRODUCT(B:B, C:C) — общая стоимость.
Добавьте ограничение

(например, бюджет): в E1 введите =IF(D1<=1000, "ОК", "Превышение").

РУЧНАЯ НАСТРОЙКА ПАРАМЕТРОВ

Изменяйте значения

в колонке B вручную (например, 5, 10, 15).
Проверяйте

D1 и E1, чтобы стоимость не превышала 1000.
Отфильтруйте

строки с "ОК" для допустимых вариантов.

ПРОСТЫЕ БИНАРНЫЕ ИНДИКАТОРЫ

В колонке F:

=IF(B1>0, 1, 0) — выбрано или нет.
В G1:

=SUM(F:F) — общее количество выбранных продуктов.

Пример: Оптимизация смеси сока

Продукты: Апельсин (50 руб.), Лимон (30 руб.), бюджет 1000 руб.

Количество: 15 и 20, стоимость = 1550 + 2030 = 1350 (превышение).
Попробуйте 10 и 15: 1050 + 1530 = 950 (ОК).
Индикаторы: 2 продукта выбрано.

Инструменты

Функции: SUMPRODUCT, IF, SUM.
Фильтры из меню Данные.

Цель
Вы научились находить оптимальные решения вручную, что полезно для планирования ресурсов.

Поиск аномалий

ЧТО МЫ БУДЕМ ДЕЛАТЬ

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

ВЫЧИСЛЕНИЕ КВАРТИЛЕЙ С QUARTILE

Таблица:

колонка A — "Время ответа" (мин).
В B1:

=QUARTILE(A:A, 1) — Q1, в C1: =QUARTILE(A:A, 3) — Q3.
Межквартильный

размах (IQR): в D1 — =C1-B1.

ПРОВЕРКА НОРМАЛЬНОСТИ С NORM.DIST

Среднее:

=AVERAGE(A:A),
стандартное отклонение: =STDEV.S(A:A).
В колонке E:

=NORM.DIST(A1, $B$2, $C$2, TRUE) — вероятность значения.

ОПРЕДЕЛЕНИЕ ВЫБРОСОВ ЧЕРЕЗ TUKEY’S FENCES

Нижняя граница:

=B1-1.5*D1, верхняя: =C1+1.5*D1.
В F:

=IF(OR(A1<$E$1, A1>$F$1), "Выброс", "ОК").

Пример: Аномалии в техподдержке

Время ответа: 5, 6, 7, 20.

Q1 = 5.5, Q3 = 13.5, IQR = 8.
Границы: -6.5 и 25.5.
20 — выброс.

Инструменты

Функции: QUARTILE, NORM.DIST, AVERAGE, STDEV.S, IF, OR.

Цель
Вы научились находить аномалии, что важно для контроля качества данных.

Расширение возможностей Р7-Офиса

ЧТО МЫ БУДЕМ ДЕЛАТЬ

Мы узнаем, как выйти за рамки Р7-Офиса, экспортируя данные и используя базовые графики для подготовки к более сложным инструментам.

ЭКСПОРТ ДАННЫХ В CSV

Выделите

таблицу и выберите Файл > Сохранить как.
Формат — CSV,

укажите разделитель (запятая).
Откройте файл

в другом ПО (например, Google Sheets).

ИСПОЛЬЗОВАНИЕ БАЗОВЫХ ГРАФИКОВ

Выделите данные

(например, "Месяц" и "Продажи").
В Вставка > Диаграмма

выберите Линии или Столбцы
Настройте

подписи для презентации.

Рекомендации по другим инструментам

Google Sheets: бесплатные функции вроде QUERY.
LibreOffice Calc: поддержка макросов для автоматизации.

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

Экспортируйте таблицу продаж в CSV и постройте график в Р7-Офисе для предварительного анализа.
Инструменты

Экспорт CSV.
Базовые графики.

Цель
Вы подготовили данные для перехода к другим инструментам, сохраняя простоту работы.

Откройте

Перейдите

Если

Выделите

В меню

Нажмите

Выделите

Посмотрите

Выделите

В меню

В соседней

Отфильтруйте

Выделите

В меню

В соседней

Отфильтруйте

Выделите

В соседней

Скопируйте

Возьмите

Создайте

Укажите

В колонке

Скопируйте

Возьмите таблицу:

Создайте

Найдите

В колонке D

Отсортируйте

Возьмите таблицу:

Создайте список

В ячейке D2

Скопируйте

Выделите

В меню

Настройте

В ячейке C1:

В D1:

Прогноз:

Скопируйте

Проверьте

Повторите выборку

Для каждой

Подсчитайте голоса:

Итог:

Проверьте

Таблица:

В C2:

Скопируйте

В E2:

Таблица:

В D1:

Добавьте ограничение

В колонке F:

В G1:

Среднее:

В колонке E:

Нижняя граница:

В F:

Выделите

Формат — CSV,

Откройте файл

Выделите данные

В Вставка > Диаграмма

Настройте