Задачи с таблицей в Excel

Дан файл на сотни строк, нужно посчитать средние, доли, медианы и проверить утверждения. Сначала найдите вопрос в таблице «что спрашивают», потом — подробности ниже.

Что спрашивают → что писать

Буквы столбцов — для примера (как их читать, см. ниже). Подставьте свои.

Что спрашивают	Формула или действие
Сколько строк (объектов) с условием	`=COUNTIFS(B:B, "Казань", F:F, 1)=СЧЁТЕСЛИМН(B:B; "Казань"; F:F; 1)`
Среднее в группе	`=AVERAGEIFS(D:D, B:B, "Казань")=СРЗНАЧЕСЛИМН(D:D; B:B; "Казань")`
Среднее, но нули = «нет данных»	`=AVERAGEIFS(D:D, B:B, "Казань", D:D, ">0")=СРЗНАЧЕСЛИМН(D:D; B:B; "Казань"; D:D; ">0")`
Доля / среднее в группе «выше медианы»	в M1: `=MEDIAN(D2:D1000)=МЕДИАНА(D2:D1000)`, затем `=AVERAGEIFS(H:H, D:D, ">"&$M$1)=СРЗНАЧЕСЛИМН(H:H; D:D; ">"&$M$1)`; для «ниже» — `"<"&$M$1`. Знак `&` склеивает «>» и число из M1 в условие вида «>3,25».
Нормальное распределение: доля ниже x	`=NORM.DIST(x, среднее, σ, TRUE)=НОРМ.РАСП(x; среднее; σ; ИСТИНА)`; выше — `=1-NORM.DIST(…)=1-НОРМ.РАСП(…)`
Число сочетаний C(n, k)	`=COMBIN(n, k)=ЧИСЛКОМБ(n; k)`; с порядком — `=PERMUT(n, k)=ПЕРЕСТ(n; k)`
Значима ли разница долей двух групп	8 готовых ячеек: n, доли, z, p-value — подробнее
Доля, когда в столбце «Да / Нет»	`=COUNTIFS(B:B, "Казань", J:J, "Да")/COUNTIF(B:B, "Казань")=СЧЁТЕСЛИМН(B:B; "Казань"; J:J; "Да")/СЧЁТЕСЛИ(B:B; "Казань")` — подробнее
Время вида `2024-03-15T10:45:07` не вычитается	сначала проверка и удаление испорченных, потом `DATE(…)+TIME(…)ДАТА(…)+ВРЕМЯ(…)` — подробнее
Посчитать по формуле из условия (ln, √, min/max)	столбец-помощник — таблица перевода
Эффект «до / после» с контрольной группой	4 × `AVERAGEIFSСРЗНАЧЕСЛИМН`, потом `=(Z4-Z3)-(Z2-Z1)` — подробнее
Сколько разных сочетаний двух столбцов	`=ROWS(UNIQUE(C2:D3000))=ЧСТРОК(УНИК(C2:D3000))`, не COUNTA — подробнее
Сумма / сравнение столбцов в строке	`=D2+E2+F2`; `=IF(E2>D2, "выше", …)=ЕСЛИ(E2>D2; "выше"; …)` — подробнее
«При пороге 0,4 модель верно предсказывает…»	столбец `=IF(P2>$M$2, 1, 0)=ЕСЛИ(P2>$M$2; 1; 0)` → 4 счёта TP, FP, FN, TN — подробнее
Много строк на объект (оценки пользователей) + таблица объектов	`=AVERAGEIF(Отзывы!A:A, A2, Отзывы!B:B)=СРЗНАЧЕСЛИ(Отзывы!A:A; A2; Отзывы!B:B)` для каждого объекта — подробнее
Свойства объекта повторяются в каждой строке (фильм в каждой оценке)	сводная «один объект — одна строка» → корреляция + значимость — подробнее
Дата числом `240701` или текстом `1/20/2013`	`=DATE(2000+INT(A2/10000), …)=ДАТА(2000+ЦЕЛОЕ(A2/10000); …)`, год: `=VALUE(RIGHT(A2, 4))=ЗНАЧЕН(ПРАВСИМВ(A2; 4))` — подробнее
Таблица «на 1-е число»: за апрель–май, за квартал, среднемесячный темп	`=(C5/B5)^(1/n)-1` — подробнее
Вопрос про команды, а строки — участники	копия листа → «Удалить дубликаты / Remove Duplicates» по команде — подробнее
Среднее по коллегам / своей группе без себя	`(SUMIFS(…) - G2) / (COUNTIFS(…) - 1)(СУММЕСЛИМН(…) - G2) / (СЧЁТЕСЛИМН(…) - 1)` — подробнее
Какая акция рискованнее	только торговые дни → мера из условия (коэффициент вариации цен `=STDEV.S(…)/AVERAGE(…)=СТАНДОТКЛОН.В(…)/СРЗНАЧ(…)`), иначе разброс доходностей `=B3/B2-1` → `STDEV.SСТАНДОТКЛОН.В` — подробнее
Мог ли получиться такой график	такая же сводная и сравнить — подробнее
Доля (в столбце 0/1)	среднее столбца: `=AVERAGEIFS(H:H, F:F, 1)=СРЗНАЧЕСЛИМН(H:H; F:F; 1)` → формат «%»
Медиана в группе / без нулей	`=MEDIAN(FILTER(D2:D1000, D2:D1000>0))=МЕДИАНА(ФИЛЬТР(D2:D1000; D2:D1000>0))` — подробнее
«В какой группе больше / меньше всего»	сводная таблица → «Среднее / Average» → сортировка — подробнее
Минимум / максимум в группе	`=MINIFS(D:D, B:B, "Казань")=МИНЕСЛИ(D:D; B:B; "Казань")`, `=MAXIFS(…)=МАКСЕСЛИ(…)`
Стандартное отклонение в группе	`=STDEV.S(FILTER(D2:D1000, B2:B1000="Казань"))=СТАНДОТКЛОН.В(ФИЛЬТР(D2:D1000; B2:B1000="Казань"))`
Коэффициент вариации	`=STDEV.S(D2:D1000)/AVERAGE(D2:D1000)=СТАНДОТКЛОН.В(D2:D1000)/СРЗНАЧ(D2:D1000)`
Квартиль, процентиль	`=QUARTILE.INC(D2:D1000, 1)=КВАРТИЛЬ.ВКЛ(D2:D1000; 1)`, `=PERCENTILE.INC(D2:D1000, 0.9)=ПРОЦЕНТИЛЬ.ВКЛ(D2:D1000; 0,9)`
Сколько уникальных (без повторов)	`=COUNTA(UNIQUE(A2:A1000))=СЧЁТЗ(УНИК(A2:A1000))`
Корреляция двух столбцов	`=CORREL(C2:C1000, D2:D1000)=КОРРЕЛ(C2:C1000; D2:D1000)` — подробнее
Наклон и свободный член прямой y = a + b·x	`=SLOPE(y, x)=НАКЛОН(y; x)`, `=INTERCEPT(y, x)=ОТРЕЗОК(y; x)` (сначала y!)
Значима ли разница средних двух групп	`=T.TEST(диапазон1, диапазон2, 2, 3)=СТЬЮДЕНТ.ТЕСТ(диапазон1; диапазон2; 2; 3)` → p-value; < 0,05 — значимо
Значима ли корреляция r при n строках	`=rSQRT(n-2)/SQRT(1-r^2)=rКОРЕНЬ(n-2)/КОРЕНЬ(1-r^2)` → \|t\| > 1,96 — значима
p-value по готовому z	`=2(1-NORM.S.DIST(ABS(z), TRUE))=2(1-НОРМ.СТ.РАСП(ABS(z); ИСТИНА))`
Подтянуть значение из другой таблицы	`=XLOOKUP(B2, Лист2!A:A, Лист2!B:B)=ПРОСМОТРX(B2; Лист2!A:A; Лист2!B:B)` — подробнее
Разница во времени, в минутах	`=(B3-B2)*1440` — подробнее
Средний темп роста за период	`=(конец/начало)^(1/число_периодов)-1`
Последняя запись по каждому ID	сортировка «от новых к старым / Newest to Oldest» → «Удалить дубликаты / Remove Duplicates» по ID — подробнее

Как читать формулы на этой странице

В примерах таблица покупок с такими столбцами. В вашей задаче буквы будут другими — подставьте свои.

A	B	C	D	E	F	G	H	I
ID	Город	Возраст	Сумма покупки	Скидка	Карта лояльности (0/1)	Доставка (0/1)	Возврат (0/1)	Дата

D2:D1000 означает «от первой строки данных до последней». Последнюю строку найдите, нажав Ctrl+↓ в столбце. На Mac вместо Ctrl обычно Cmd.

Английские и русские формулы

Каждая формула дана в двух видах: для английского Excel — =AVERAGEIFS(D:D, B:B, "Казань") — и для русского — =СРЗНАЧЕСЛИМН(D:D; B:B; "Казань"). Переключатель «Обе · EN · RU» — в верхней панели, он всегда на виду.
Какой у вас Excel, видно по любой готовой формуле: СУММ — русский, SUM — английский. Чужие имена Excel не понимает (будет #ИМЯ? / #NAME?).
Запятая или точка с запятой зависит от региона компьютера, а не от языка Excel. Обычно английский Excel — запятая между аргументами и точка в дробях (0.9), русский — точка с запятой и запятая в дробях (0,9). Если Excel не принимает формулу — поменяйте разделители.
Переключить Excel на английский: Mac — «Системные настройки» → «Основные» → «Язык и регион» → внизу «Приложения» → «+» → Microsoft Excel → English, потом перезапустить Excel; Windows — «Файл» → «Параметры» → «Язык» → язык интерфейса Office → English.

План решения любой задачи с файлом

Прочитать описание столбцов. Что значит 0? Что делать с повторными строками?
Сделать копию листа (правый клик по ярлыку листа → «Переместить или скопировать / Move or Copy» → «Создать копию / Create a copy»). Чистку делайте в копии, чтобы можно было вернуться.
Очистить данные строго по описанию.
На каждый вопрос — одна формула или одна сводная таблица.
Проверить результат на здравый смысл и округлить, как просят.

Осмотреться за минуту

Фильтр: встаньте в любую ячейку шапки → Ctrl+Shift+L (на Mac Cmd+Shift+F), или «Данные / Data» → «Фильтр / Filter». Нажмите стрелку у столбца «Город»: вы увидите список всех значений. Если там два «Казань» — где-то лишний пробел.
Строка состояния: выделите столбец (клик по букве). Внизу окна Excel показаны количество, среднее и сумма. Это самая быстрая проверка любых расчётов.
Листы внизу окна. Часто первый лист — «Описание данных», а сами данные лежат на втором. Прочитайте описание, потом переходите к данным.
Сколько строк: =COUNTA(A:A)-1=СЧЁТЗ(A:A)-1 (минус шапка).
Что внутри каждого нужного столбца: числа, текст, «Yes / No», даты? Посмотрите несколько строк и список значений в фильтре. От этого зависит, какую формулу писать (см. «Файл не в том виде»).
Числа, сохранённые как текст: зелёный треугольник в углу ячейки или выравнивание по левому краю. Такие ячейки формулы пропускают. Исправить: выделить → жёлтый значок «!» → «Преобразовать в число / Convert to Number».

Очистка: три ловушки

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

Ловушка 1: лишние пробелы в тексте

«Москва» и «Москва␣» для Excel — разные города: формула с условием "Москва" пропустит вторую.

Вставьте пустой столбец справа от «Город»: правый клик по букве C → «Вставить / Insert».
В C2 напишите =TRIM(B2)=СЖПРОБЕЛЫ(B2) и нажмите Enter.
Дважды щёлкните по маленькому квадратику в правом нижнем углу C2: формула протянется до конца таблицы.
Выделите столбец C → Ctrl+C → выделите столбец B → правый клик → «Специальная вставка / Paste Special» → «Значения / Values».
Удалите вспомогательный столбец C.

Ловушка 2: повторные строки

Например, в описании сказано: «один покупатель может встречаться несколько раз — учитывается последняя запись по дате».

Выделите всю таблицу (встаньте в неё и нажмите Ctrl+A).
«Данные / Data» → «Сортировка / Sort» → по столбцу «Дата», порядок «от новых к старым / Newest to Oldest».
«Данные / Data» → «Удалить дубликаты / Remove Duplicates» → снимите все галочки, оставьте только ID → OK.
Excel напишет, сколько строк удалено и сколько осталось.

Не удаляйте повторы без причины

Если в описании не сказано, что повторы — ошибка или что нужна последняя запись, одинаковые строки не трогайте. Две одинаковые покупки на одну сумму — это вполне могут быть две разные покупки.

Почему сначала сортировка

«Удалить дубликаты / Remove Duplicates» всегда оставляет первую сверху строку. Без сортировки останется случайная запись, а не последняя. Сначала сортируем так, чтобы нужная строка была выше всех своих повторов.

Ловушка 3: нули, которые означают «нет данных»

Если в описании сказано, что 0 значит «не указано», строки удалять нельзя: в них есть другие столбцы, нужные для других вопросов. Нули исключаем в самих формулах условием ">0".

Файл не в том виде: что делать

Что видите	Что делать
Вместо 0/1 стоит «Yes / No», «Да / Нет», «True / False»	Доля через подсчёт: `=COUNTIFS(B:B, "Казань", J:J, "Да") / COUNTIF(B:B, "Казань")=СЧЁТЕСЛИМН(B:B; "Казань"; J:J; "Да") / СЧЁТЕСЛИ(B:B; "Казань")` — «из Казани и с подпиской» делить на «всех из Казани». Или столбец-помощник `=IF(J2="Да", 1, 0)=ЕСЛИ(J2="Да"; 1; 0)`, и дальше как с 0/1.
Цена как текст: `$3.40`, `1 200 ₽`	Выделите столбец → `Ctrl`+`H`: найти `$` → заменить на пусто → «Заменить все / Replace All». Потом так же `.` → `,` и пробел → пусто. Числа прижмутся вправо — значит, стали числами. Формулой (в новом столбце, работает в любом Excel): `=VALUE(SUBSTITUTE(TRIM(SUBSTITUTE(E2, "$", "")), ".", MID(1/2, 2, 1)))=ЗНАЧЕН(ПОДСТАВИТЬ(СЖПРОБЕЛЫ(ПОДСТАВИТЬ(E2; "$"; "")); "."; ПСТР(1/2; 2; 1)))` — «убери $, убери пробелы по краям, замени точку на десятичный знак твоего Excel». `MID(1/2, 2, 1)ПСТР(1/2; 2; 1)` — это и есть десятичный знак: из «0.5» (или «0,5» в русском регионе) берётся второй символ.
Дата и время текстом: `2024-03-15T10:45:07` (прижато влево, вычитание даёт `#ЗНАЧ!`)	См. ниже, «Время записано текстом».
В условии «возраст», а в файле два похожих столбца (`age_group` с текстом «18–24», «25–34» и `age` с числами)	Среднее можно посчитать только по числам. Если `AVERAGEСРЗНАЧ` даёт `#ДЕЛ/0!`, в столбце текст — вам нужен соседний числовой столбец. Сверьтесь с описанием столбцов.
В столбце ID среди чисел попадаются обрывки текста: `xK41b`, `2207Q`, `8Rr3`	Столбец-помощник `=ISNUMBER(D2)=ЕЧИСЛО(D2)` — у мусора будет `FALSEЛОЖЬ`. Посчитайте `=COUNTIF(E:E, FALSE)=СЧЁТЕСЛИ(E:E; ЛОЖЬ)`, отфильтруйте `FALSEЛОЖЬ` и удалите эти строки (если условие не говорит иначе). Не пытайтесь «вытащить» из мусора цифры: `2207Q` — это не пользователь 2207, и ответ получится неверным.
Пустые клетки в нужных столбцах	`AVERAGEIFSСРЗНАЧЕСЛИМН`, `CORRELКОРРЕЛ`, `MEDIANМЕДИАНА` их пропускают — и по обычному диапазону, и внутри `MEDIAN(FILTER(…))МЕДИАНА(ФИЛЬТР(…))`. Но если вывести `FILTERФИЛЬТР` на лист отдельно, пустые покажутся нулями — по такому выводу не считайте (см. медиану).
Один участник встречается в двух столбцах (шахматы: «белые» и «чёрные»)	Считайте по каждому столбцу и складывайте: `=SUMIF(B:B, "Иванов", D:D) + SUMIF(C:C, "Иванов", E:E)=СУММЕСЛИ(B:B; "Иванов"; D:D) + СУММЕСЛИ(C:C; "Иванов"; E:E)` — очки Иванова за белых и за чёрных.
Значение может стоять в любом из нескольких столбцов (1-й, 2-й, 3-й выбор)	Диапазон на все столбцы сразу: `=COUNTIF(D2:F100, "Физика")=СЧЁТЕСЛИ(D2:F100; "Физика")`.

Формулы с условием

=COUNTIFS(диапазон1, условие1, диапазон2, условие2, …) — сколько строк =AVERAGEIFS(что_усреднять, диапазон1, условие1, …) — среднее =SUMIFS(что_суммировать, диапазон1, условие1, …) — сумма

=СЧЁТЕСЛИМН(диапазон1; условие1; диапазон2; условие2; …) — сколько строк =СРЗНАЧЕСЛИМН(что_усреднять; диапазон1; условие1; …) — среднее =СУММЕСЛИМН(что_суммировать; диапазон1; условие1; …) — сумма

Условия пишутся в кавычках: "Казань", ">0", "<>Москва" (не Москва), ">=30". Все условия должны выполняться одновременно.

«Не равно» по целому столбцу

=COUNTIFS(B:B, "<>Москва")=СЧЁТЕСЛИМН(B:B; "<>Москва") посчитает и миллион пустых строк под таблицей (они тоже «не Москва»), и шапку. Для подсчёта с "<>…" берите диапазон строго по данным: =COUNTIFS(B2:B1000, "<>Москва")=СЧЁТЕСЛИМН(B2:B1000; "<>Москва"). В AVERAGEIFSСРЗНАЧЕСЛИМН так не бывает: пустые клетки в усредняемом столбце пропускаются.

Пример: средняя покупка в Казани

=AVERAGEIFS(D:D, B:B, "Казань", D:D, ">0")=СРЗНАЧЕСЛИМН(D:D; B:B; "Казань"; D:D; ">0")

Читается так: «усредни столбец D там, где в B стоит Казань и где D больше нуля».

Пример: доля возвратов у владельцев карты

Возврат — это 0 или 1. Среднее столбца из нулей и единиц — это и есть доля единиц.

=AVERAGEIFS(H:H, F:F, 1)=СРЗНАЧЕСЛИМН(H:H; F:F; 1) → например, 0,109. Чтобы увидеть проценты: «Главная / Home» → кнопка «%» → 10,9%.

То же самое длиннее, но понятнее: =COUNTIFS(F:F, 1, H:H, 1) / COUNTIFS(F:F, 1)=СЧЁТЕСЛИМН(F:F; 1; H:H; 1) / СЧЁТЕСЛИМН(F:F; 1) — «у скольких есть карта и возврат» делить на «у скольких есть карта».

Медиана, минимум, максимум по условию

Для медианы готовой «MEDIANIF» нет. Используем FILTERФИЛЬТР: он оставляет только подходящие значения.

=MEDIAN(FILTER(D2:D1000, D2:D1000>0)) — медиана без нулей =MEDIAN(FILTER(D2:D1000, (B2:B1000="Казань")*(D2:D1000>0))) — два условия: умножаем =MEDIAN(FILTER(D2:D1000, (B2:B1000="Казань")*(D2:D1000<>""))) — без пустых клеток

=МЕДИАНА(ФИЛЬТР(D2:D1000; D2:D1000>0)) — медиана без нулей =МЕДИАНА(ФИЛЬТР(D2:D1000; (B2:B1000="Казань")*(D2:D1000>0))) — два условия: умножаем =МЕДИАНА(ФИЛЬТР(D2:D1000; (B2:B1000="Казань")*(D2:D1000<>""))) — без пустых клеток

FILTER и пустые клетки

Проверено в Excel: внутри MEDIAN(FILTER(…))МЕДИАНА(ФИЛЬТР(…)), AVERAGE(FILTER(…))СРЗНАЧ(ФИЛЬТР(…)), STDEV.S(FILTER(…))СТАНДОТКЛОН.В(ФИЛЬТР(…)) пустые клетки пропускаются, как и должно быть. Ловушка в другом: если написать =FILTER(…)=ФИЛЬТР(…) в отдельную клетку, Excel «выльет» результат на лист, и пустые там покажутся нулями. Не считайте по такому выводу — пишите FILTERФИЛЬТР прямо внутри MEDIANМЕДИАНА и других функций. Условие «не пусто» *(D2:D1000<>"") добавляйте, когда считаете по двум столбцам сразу (CORRELКОРРЕЛ): так оба FILTERФИЛЬТР берут одни и те же строки.

Минимум и максимум по условию есть готовые: MINIFSМИНЕСЛИ и MAXIFSМАКСЕСЛИ с теми же аргументами, что у AVERAGEIFSСРЗНАЧЕСЛИМН.

Если FILTER не работает

В старом Excel (2016 и раньше) функции FILTERФИЛЬТР нет. Тогда: =MEDIAN(IF(D2:D1000>0, D2:D1000))=МЕДИАНА(ЕСЛИ(D2:D1000>0; D2:D1000)), а ввод завершите не Enter, а Ctrl+Shift+Enter. Формула окажется в фигурных скобках.

Сводная таблица: всё по группам сразу

Если вопрос «в каком городе больше всего…», писать формулу для каждого города долго. Сводная таблица посчитает все группы за раз.

Встаньте в любую ячейку таблицы → «Вставка / Insert» → «Сводная таблица / PivotTable» → OK. Откроется новый лист.
Справа появится список полей. Перетащите Город в область «Строки / Rows».
Перетащите Возврат в область «Значения / Values». По умолчанию Excel посчитает сумму.
В области «Значения / Values» нажмите на «Сумма по полю Возврат / Sum of Возврат» → «Параметры полей значений / Value Field Settings» → выберите «Среднее / Average» → OK.
Выделите получившиеся числа → кнопка «%» на вкладке «Главная / Home».
Чтобы упорядочить: правый клик по любому числу → «Сортировка / Sort» → «По убыванию / Descending».

Получится доля возвратов в каждом городе, самая большая сверху.

Что ещё умеет сводная

В «Значения / Values» можно положить одно поле дважды: один раз «Среднее / Average», второй «Количество / Count» — сразу видно, сколько строк в группе.
Второе поле в «Столбцы / Columns» (например, «Карта лояльности») даст таблицу город × карта.
Группа по нескольким признакам сразу (город + доставка + год) — положите все поля в «Строки / Rows».
Только часть данных («только покупки из Казани»): перетащите поле «Город» в область «Фильтры / Filters» — над сводной появится выпадающий список, выберите в нём нужное значение.
Пустые клетки и пустые строки "" сводная в «Среднем» не учитывает, поэтому у столбца-помощника для пропусков ставьте "", а не 0.
Медиану сводная не считает. Для медианы — формула с FILTERФИЛЬТР.
Если исходные данные изменились: правый клик по сводной → «Обновить / Refresh».

Проверка очистки

Если в сводной один и тот же город встречается дважды, вы не убрали лишние пробелы (ловушка 1).

Вспомогательные столбцы

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

Пример: новый показатель и деление на группы по порогу

Задача: «Посчитайте скидку в процентах от суммы покупки. Сравните долю возвратов у покупок со скидкой больше 10% и у остальных».

В J1 напишите заголовок Скидка, %, в J2: =IF(D2>0, E2/D2, "")=ЕСЛИ(D2>0; E2/D2; ""). Для суммы 0 ячейка останется пустой, деления на ноль не будет.
Протяните формулу двойным щелчком по квадратику в углу ячейки.
В свободную ячейку, например M1, впишите порог: 0.10,1. Если нужно делить по медиане, впишите туда =MEDIAN(J2:J1000)=МЕДИАНА(J2:J1000) (пустые ячейки она пропускает сама).
В K1 заголовок Группа, в K2: =IF(J2="", "", IF(J2>$M$1, "больше", "не больше"))=ЕСЛИ(J2=""; ""; ЕСЛИ(J2>$M$1; "больше"; "не больше")), протянуть.
Доли: =AVERAGEIFS(H:H, K:K, "больше")=СРЗНАЧЕСЛИМН(H:H; K:K; "больше") и =AVERAGEIFS(H:H, K:K, "не больше")=СРЗНАЧЕСЛИМН(H:H; K:K; "не больше").

Зачем знаки $

$M$1 — абсолютная ссылка: когда формулу протягивают вниз, она продолжает смотреть на M1. Без долларов в третьей строке была бы M2, в четвёртой M3… и всё сломалось бы. Быстро поставить доллары: встаньте курсором на ссылку в формуле и нажмите F4.

Подтянуть данные с другого листа

Задача: «Посчитайте долю возвратов по регионам». Регионов в основной таблице нет, они на другом листе, где у каждого города указан регион.

Новый столбец Регион, в первой строке данных: =XLOOKUP(B2, Города!A:A, Города!B:B)=ПРОСМОТРX(B2; Города!A:A; Города!B:B) — «найди город из B2 в столбце A листа Города и верни соседнее значение из столбца B».
Протяните вниз.
Сводная таблица: «Регион» в «Строки / Rows», «Возврат» в «Значения / Values» → «Среднее / Average».

Если XLOOKUPПРОСМОТРX нет (старый Excel): =VLOOKUP(B2, Города!A:B, 2, FALSE)=ВПР(B2; Города!A:B; 2; ЛОЖЬ). Последний аргумент FALSEЛОЖЬ обязателен — это «точное совпадение».

Ошибка #Н/Д

Значит, значение не нашлось. Почти всегда это лишний пробел или опечатка: проверьте ловушку 1.

Корреляция, регрессия, значимость

=CORREL(C2:C1000, D2:D1000) — корреляция возраста и суммы покупки =SLOPE(D2:D1000, C2:C1000) — наклон прямой «сумма = a + b·возраст» (сначала y, потом x!) =INTERCEPT(D2:D1000, C2:C1000) — свободный член a

=КОРРЕЛ(C2:C1000; D2:D1000) — корреляция возраста и суммы покупки =НАКЛОН(D2:D1000; C2:C1000) — наклон прямой «сумма = a + b·возраст» (сначала y, потом x!) =ОТРЕЗОК(D2:D1000; C2:C1000) — свободный член a

Если строки с нулями надо исключить — тем же FILTERФИЛЬТР:

=CORREL(FILTER(C2:C1000, D2:D1000>0), FILTER(D2:D1000, D2:D1000>0))

=КОРРЕЛ(ФИЛЬТР(C2:C1000; D2:D1000>0); ФИЛЬТР(D2:D1000; D2:D1000>0))

Корреляция внутри группы, только по строкам, где есть оба показателя (одно и то же условие в обоих FILTER):

=CORREL(FILTER(D2:D1000, (B2:B1000="Казань")*(D2:D1000<>"")*(E2:E1000<>"")), FILTER(E2:E1000, (B2:B1000="Казань")*(D2:D1000<>"")*(E2:E1000<>"")))

=КОРРЕЛ(ФИЛЬТР(D2:D1000; (B2:B1000="Казань")*(D2:D1000<>"")*(E2:E1000<>"")); ФИЛЬТР(E2:E1000; (B2:B1000="Казань")*(D2:D1000<>"")*(E2:E1000<>"")))

Значимость разницы средних двух групп: =T.TEST(FILTER(D2:D1000, G2:G1000=1), FILTER(D2:D1000, G2:G1000=0), 2, 3)=СТЬЮДЕНТ.ТЕСТ(ФИЛЬТР(D2:D1000; G2:G1000=1); ФИЛЬТР(D2:D1000; G2:G1000=0); 2; 3) даёт p-value. Меньше 0,05 — разница значима на 5% уровне. Как посчитать z вручную, и для долей тоже, — в туториале, тип 22.

Значимость разницы долей — готовые ячейки

Пример: доля покупок с доставкой в Казани и в Москве (B — город, G — доставка 0/1). Впишите в свободные клетки:

Клетка	Что	Формула
P1	n₁ — размер группы 1	`=COUNTIF(B:B, "Казань")=СЧЁТЕСЛИ(B:B; "Казань")`
P2	n₂ — размер группы 2	`=COUNTIF(B:B, "Москва")=СЧЁТЕСЛИ(B:B; "Москва")`
P3	p₁ — доля в группе 1	`=COUNTIFS(B:B, "Казань", G:G, 1)/P1=СЧЁТЕСЛИМН(B:B; "Казань"; G:G; 1)/P1`
P4	p₂ — доля в группе 2	`=COUNTIFS(B:B, "Москва", G:G, 1)/P2=СЧЁТЕСЛИМН(B:B; "Москва"; G:G; 1)/P2`
P5	общая доля p	`=(P3P1+P4P2)/(P1+P2)`
P6	z	`=(P3-P4)/SQRT(P5(1-P5)(1/P1+1/P2))=(P3-P4)/КОРЕНЬ(P5(1-P5)(1/P1+1/P2))`
P7	p-value	`=2(1-NORM.S.DIST(ABS(P6), TRUE))=2(1-НОРМ.СТ.РАСП(ABS(P6); ИСТИНА))`
P8	модуль разницы долей	`=ABS(P3-P4)`

Если |z| > 1,96 (то же: p-value < 0,05) — разница значима при α = 0,05. Для средних то же самое делает T.TESTСТЬЮДЕНТ.ТЕСТ; модуль разницы средних — =ABS(AVERAGEIF(B:B, "Казань", D:D) - AVERAGEIF(B:B, "Москва", D:D))=ABS(СРЗНАЧЕСЛИ(B:B; "Казань"; D:D) - СРЗНАЧЕСЛИ(B:B; "Москва"; D:D)).

Полная регрессия с несколькими признаками и p-value — через «Пакет анализа / Analysis ToolPak»:

Включить один раз. Windows: «Файл / File» → «Параметры / Options» → «Надстройки / Add-ins» → внизу «Управление: Надстройки Excel / Manage: Excel Add-ins» → «Перейти / Go» → галочка «Пакет анализа / Analysis ToolPak». Mac: «Сервис / Tools» → «Надстройки Excel / Excel Add-ins» → «Пакет анализа / Analysis ToolPak».
Дальше: «Данные / Data» → «Анализ данных / Data Analysis» → «Регрессия / Regression». «Входной интервал Y / Input Y Range» — что объясняем, «Входной интервал X / Input X Range» — признаки (соседние столбцы). Поставьте галочку «Метки / Labels», если выделяли вместе с шапкой.
В результате смотрите столбцы «Коэффициенты / Coefficients» и «P-значение / P-value», а также строку «R-квадрат / R Square».

Время и даты

Для Excel дата и время — это число дней. 1 = сутки, 0,5 = 12 часов. Поэтому разница двух моментов в минутах = разница × 1440 (столько минут в сутках). В часах — × 24. Год из даты: =YEAR(I2)=ГОД(I2), месяц: =MONTH(I2)=МЕСЯЦ(I2), день недели: =WEEKDAY(I2, 2)=ДЕНЬНЕД(I2; 2) (1 — понедельник).

Пример: сколько раз человек заходил в приложение

Таблица действий: A — пользователь, B — время. «Считаем, что человек зашёл заново, если перед действием был перерыв больше 20 минут. Кто заходил чаще всех?»

Сортировка в два уровня: «Данные / Data» → «Сортировка / Sort» → по пользователю → «Добавить уровень / Add Level» → по времени, от старых к новым. Без этого шага ничего не получится.
В D1 заголовок Новый заход, в D2:
=IF(A2<>A1, 1, IF(ROUND((B2-B1)*1440, 0)>20, 1, 0))

=ЕСЛИ(A2<>A1; 1; ЕСЛИ(ОКРУГЛ((B2-B1)*1440; 0)>20; 1; 0))
«Если это другой пользователь, чем строкой выше, — 1. Если тот же, но прошло больше 20 минут, — тоже 1. Иначе 0». Два IF один в другом нужны, чтобы в первой строке данных Excel не пытался вычитать из времени шапку: вариант с ORИЛИ там даёт #VALUE! (в русском Excel #ЗНАЧ!), и вся сводная ломается.
Протяните вниз.
Сводная: пользователь в «Строки / Rows», «Новый заход» в «Значения / Values» (Сумма) → сортировка по убыванию.

Сумма единиц у пользователя = сколько раз он заходил.

Зачем ROUND

Время хранится как дробь, и ровно 20 минут может превратиться в 20,0000001 — тогда «больше 20» сработает зря. Округление до целых минут убирает эту проблему. И внимательно читайте: «больше 20» — это >20, «20 и больше» — >=20.

Дата записана числом или по-американски

Что в клетке	Как получить настоящую дату (дата в A2)
Число `240701` — год, месяц, день подряд (ГГММДД)	`=DATE(2000+INT(A2/10000), MOD(INT(A2/100), 100), MOD(A2, 100))=ДАТА(2000+ЦЕЛОЕ(A2/10000); ОСТАТ(ЦЕЛОЕ(A2/100); 100); ОСТАТ(A2; 100))` → 1 июля 2024. Дальше `WEEKDAYДЕНЬНЕД`, сортировка и т. д.
Число `20240701` (ГГГГММДД)	`=DATE(INT(A2/10000), MOD(INT(A2/100), 100), MOD(A2, 100))=ДАТА(ЦЕЛОЕ(A2/10000); ОСТАТ(ЦЕЛОЕ(A2/100); 100); ОСТАТ(A2; 100))`
Текст `1/20/2013` — месяц/день/год (американский)	Если нужен только год: `=VALUE(RIGHT(A2, 4))=ЗНАЧЕН(ПРАВСИМВ(A2; 4))`. Полная дата: `=DATE(RIGHT(A2, 4), LEFT(A2, FIND("/", A2)-1), MID(A2, FIND("/", A2)+1, FIND("/", A2, FIND("/", A2)+1)-FIND("/", A2)-1))=ДАТА(ПРАВСИМВ(A2; 4); ЛЕВСИМВ(A2; НАЙТИ("/"; A2)-1); ПСТР(A2; НАЙТИ("/"; A2)+1; НАЙТИ("/"; A2; НАЙТИ("/"; A2)+1)-НАЙТИ("/"; A2)-1))`

Не доверяйте автоматическому распознаванию

Русский Excel читает 1/12/2013 как 1 декабря, хотя это 12 января, а 1/20/2013 вообще не понимает. Поэтому разбирайте такую дату формулой, как выше. Проверка: в столбце с годом не должно быть ошибок, а месяцы — от 1 до 12.

Время записано текстом

Признаки: значения вида 2024-03-15T10:45:07 прижаты влево, а =B3-B2 даёт #ЗНАЧ!. Excel не понимает букву T посередине. Ниже время в столбце A; вставьте справа два пустых столбца B и C.

Сначала найдите испорченные записи. Заголовок B1 Проверка, в B2:
=IFERROR(AND(LEN(A2)=19, MID(A2,5,1)="-", MID(A2,8,1)="-", MID(A2,11,1)="T", MID(A2,14,1)=":", MID(A2,17,1)=":", --MID(A2,6,2)<=12, --MID(A2,9,2)<=31, --MID(A2,12,2)<24, --MID(A2,15,2)<60, --MID(A2,18,2)<60), FALSE)

=ЕСЛИОШИБКА(И(ДЛСТР(A2)=19; ПСТР(A2;5;1)="-"; ПСТР(A2;8;1)="-"; ПСТР(A2;11;1)="T"; ПСТР(A2;14;1)=":"; ПСТР(A2;17;1)=":"; --ПСТР(A2;6;2)<=12; --ПСТР(A2;9;2)<=31; --ПСТР(A2;12;2)<24; --ПСТР(A2;15;2)<60; --ПСТР(A2;18;2)<60); ЛОЖЬ)
«Длина ровно 19, дефисы, T и двоеточия на своих местах, месяц не больше 12, день не больше 31, часы меньше 24, минуты и секунды меньше 60». Два минуса -- превращают кусок текста в число; если там буква, получается ошибка, и IFERRORЕСЛИОШИБКА ставит FALSEЛОЖЬ. Протяните вниз. У правильной записи будет TRUEИСТИНА.
Сколько испорченных: =COUNTIF(B:B, FALSE)=СЧЁТЕСЛИ(B:B; ЛОЖЬ). Включите фильтр, в столбце B оставьте только FALSEЛОЖЬ, выделите эти строки → правый клик → «Удалить строку / Delete Row». Снимите фильтр. (Если условие задачи говорит что-то другое про испорченные записи — делайте как в условии.)
Заголовок C1 Время, в C2 — собираем дату по кусочкам (работает в любом Excel):
=DATE(LEFT(A2,4), MID(A2,6,2), MID(A2,9,2)) + TIME(MID(A2,12,2), MID(A2,15,2), MID(A2,18,2))

=ДАТА(ЛЕВСИМВ(A2;4); ПСТР(A2;6;2); ПСТР(A2;9;2)) + ВРЕМЯ(ПСТР(A2;12;2); ПСТР(A2;15;2); ПСТР(A2;18;2))
Покороче, если ваш Excel понимает такой формат: =VALUE(SUBSTITUTE(A2, "T", " "))=ЗНАЧЕН(ПОДСТАВИТЬ(A2; "T"; " ")). Протяните вниз.
Чтобы видеть дату, а не число вроде 45366,45: выделите столбец C → правый клик → «Формат ячеек / Format Cells» → «(все форматы) / Custom» → в поле «Тип / Type» впишите dd.mm.yyyy hh:mm:ss (в русском Excel — ДД.ММ.ГГГГ чч:мм:сс) → OK. Для расчётов формат не важен.
Дальше работайте со столбцом C: сортировка, разница *1440, сессии.

Почему сначала проверка, а не сразу перевод

Проверено в Excel: испорченная запись 2024-03-15T18:71:30 (71 минута) и через DATE(…)+TIME(…)ДАТА(…)+ВРЕМЯ(…), и через VALUE(…)ЗНАЧЕН(…) молча превращается в 19:11:30 — ошибки на экране нет, а время неправильное. Поэтому сначала убираем всё, что не прошло проверку, и только потом переводим.

Вариант без формулы перевода: после удаления испорченных строк выделите только столбец со временем → Ctrl+H → найти T, заменить на пробел → «Заменить все / Replace All». Если значения прижались вправо — стали временем.

Огромный файл

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

Пропуски в рядах по дням

Если в выходные значение «тянется» с пятницы, а считать надо только по рабочим дням, добавьте столбец =WEEKDAY(дата, 2)=ДЕНЬНЕД(дата; 2) и берите строки, где он меньше 6.

Посчитать по формуле из условия

Иногда в условии дают свою формулу (индекс, нормировку), и её надо посчитать для каждой строки. Делайте столбец-помощник и переводите значки так:

В условии	В Excel
x², xⁿ, ⁿ√x	`=B2^2`, `=B2^n`, `=B2^(1/n)`
√x	`=SQRT(B2)=КОРЕНЬ(B2)`
ln x, log₁₀ x	`=LN(B2)`, `=LOG10(B2)`
eˣ	`=EXP(B2)`
\|x\|	`=ABS(B2)`
min / max по всему столбцу	`MIN($B$2:$B$500)МИН($B$2:$B$500)`, `MAX($B$2:$B$500)МАКС($B$2:$B$500)` — с долларами, чтобы при протягивании не съезжало
(x − min) / (max − min)	`=(B2-MIN($B$2:$B$500))/(MAX($B$2:$B$500)-MIN($B$2:$B$500))=(B2-МИН($B$2:$B$500))/(МАКС($B$2:$B$500)-МИН($B$2:$B$500))`
(x − 10) / (90 − 10) — числа даны в условии	`=(B2-10)/(90-10)`
Среднее геометрическое трёх: ∛(a·b·c)	`=(F2G2H2)^(1/3)` или `=GEOMEAN(F2:H2)=СРГЕОМ(F2:H2)`
Среднее арифметическое двух: (a + b) / 2	`=(D2+E2)/2` — скобки обязательны

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

Типовые расчёты целиком

До и после, с контрольной группой — в Excel

Столбцы: A — год (2023 или 2024), B — была ли акция в магазине (1/0), C — выручка. Акция началась в 2024. «Каков эффект акции».

Z1 =AVERAGEIFS(C:C, A:A, 2023, B:B, 0) — без акции, до Z2 =AVERAGEIFS(C:C, A:A, 2024, B:B, 0) — без акции, после Z3 =AVERAGEIFS(C:C, A:A, 2023, B:B, 1) — с акцией, до Z4 =AVERAGEIFS(C:C, A:A, 2024, B:B, 1) — с акцией, после Z5 =(Z4-Z3)-(Z2-Z1) — эффект

Z1 =СРЗНАЧЕСЛИМН(C:C; A:A; 2023; B:B; 0) — без акции, до Z2 =СРЗНАЧЕСЛИМН(C:C; A:A; 2024; B:B; 0) — без акции, после Z3 =СРЗНАЧЕСЛИМН(C:C; A:A; 2023; B:B; 1) — с акцией, до Z4 =СРЗНАЧЕСЛИМН(C:C; A:A; 2024; B:B; 1) — с акцией, после Z5 =(Z4-Z3)-(Z2-Z1) — эффект

Эффект = насколько группа с акцией изменилась сверх того, как изменилась группа без неё. Отрицательное число — акция снизила показатель. Логика — в туториале, тип 10. Средняя по всем вместе (=AVERAGEIF(A:A, 2024, C:C)=СРЗНАЧЕСЛИ(A:A; 2024; C:C)) про эффект ничего не говорит.

Итог по строке из нескольких столбцов

Сумма за три месяца: столбец =D2+E2+F2, дальше =AVERAGEIF(C:C, "группа", H:H)=СРЗНАЧЕСЛИ(C:C; "группа"; H:H).
Сравнить два столбца в строке (план D и факт E): =IF(E2>D2, "выше", IF(E2<D2, "ниже", "ровно"))=ЕСЛИ(E2>D2; "выше"; ЕСЛИ(E2<D2; "ниже"; "ровно")), потом =COUNTIF(F:F, "выше")=СЧЁТЕСЛИ(F:F; "выше"). Так же считают победы, поражения и ничьи по счёту матча.
Прибыль, только где есть оба числа (расходы L, доход M): =IF(AND(L2<>"", M2<>""), M2-L2, "")=ЕСЛИ(И(L2<>""; M2<>""); M2-L2; ""). «Прибыльный» 1/0: =IF(P2="", "", IF(P2>0, 1, 0))=ЕСЛИ(P2=""; ""; ЕСЛИ(P2>0; 1; 0)). Доля прибыльных среди тех, у кого расходы ниже медианы (медиана в S1): =AVERAGEIFS(Q:Q, L:L, "<"&$S$1)=СРЗНАЧЕСЛИМН(Q:Q; L:L; "<"&$S$1) — пустые "" в Q не считаются.

Уникальные сочетания нескольких столбцов

«Сколько разных позиций (товар + размер)»: =ROWS(UNIQUE(C2:D3000))=ЧСТРОК(УНИК(C2:D3000)). UNIQUEУНИК по двум столбцам оставляет уникальные пары, а ROWSЧСТРОК считает их. Не COUNTAСЧЁТЗ: он посчитает клетки, а не строки, и ответ выйдет почти вдвое больше. Одного столбца это не касается: там =COUNTA(UNIQUE(A2:A3000))=СЧЁТЗ(УНИК(A2:A3000)) — то же самое.

Как читать знак ответа

Год выпуска и возраст — наоборот. CORREL(год, цена) > 0КОРРЕЛ(год; цена) > 0 значит «чем новее, тем дороже», то есть старые дешевле. Если нужен возраст — сделайте столбец =2024-I2, и знак станет привычным.
«Сколько пропустили» = сколько забил соперник. Читайте, чьи это числа, прежде чем сравнивать.
Название группы — точно как в данных. В фильтре может оказаться Кредит/Рассрочка, а не Кредит; Высокий с большой буквы. Условие "Кредит" не найдёт ничего, и формула даст #ДЕЛ/0!. Копируйте значение из самой ячейки.
«Равен» после округления. Если в условии «округлите до 3 знаков», сравнивайте округлённые числа: =ROUND(x, 3)=ОКРУГЛ(x; 3). 0,8164 и 0,8158 после округления оба дают 0,816 — значит, «равны». А по неокруглённым Excel скажет, что разные.

Приёмы для задач посложнее

Порог: из вероятности в «да / нет», потом TP, FP, FN, TN

В файле есть оценка модели от 0 до 1 (например, P — «вероятность возврата товара») и что было на самом деле (H — возврат 0/1). Спрашивают: «при пороге 0,4 сколько возвратов модель угадала».

Порог впишите в свободную клетку, например M2: 0.40,4.
Новый столбец Прогноз (пусть это Q): =IF(P2>$M$2, 1, 0)=ЕСЛИ(P2>$M$2; 1; 0), протянуть. (Если в условии «не ниже порога» — >=.)
Четыре клетки:
TP (сказала 1, было 1): =COUNTIFS(Q:Q, 1, H:H, 1) FP (сказала 1, было 0): =COUNTIFS(Q:Q, 1, H:H, 0) FN (сказала 0, было 1): =COUNTIFS(Q:Q, 0, H:H, 1) TN (сказала 0, было 0): =COUNTIFS(Q:Q, 0, H:H, 0)

TP (сказала 1, было 1): =СЧЁТЕСЛИМН(Q:Q; 1; H:H; 1) FP (сказала 1, было 0): =СЧЁТЕСЛИМН(Q:Q; 1; H:H; 0) FN (сказала 0, было 1): =СЧЁТЕСЛИМН(Q:Q; 0; H:H; 1) TN (сказала 0, было 0): =СЧЁТЕСЛИМН(Q:Q; 0; H:H; 0)
Другой порог — просто поменяйте число в M2, всё пересчитается.

«Что будет, если порог снизить»: модель чаще говорит «1», поэтому TP и FP растут или не меняются, а TN и FN падают или не меняются. Выше порог — наоборот. Что значат TP, FP и т. д. — в туториале, тип 11.

Две таблицы: много строк на один объект

Пример: лист Отзывы (A — товар, B — оценка одного покупателя) и лист Товары (A — товар, B — цена, C — год выпуска). Вопрос: «связана ли средняя оценка с ценой».

Сначала посчитайте по каждому товару. На листе Товары новый столбец D Средняя оценка: =AVERAGEIF(Отзывы!A:A, A2, Отзывы!B:B)=СРЗНАЧЕСЛИ(Отзывы!A:A; A2; Отзывы!B:B), протянуть. Число отзывов: =COUNTIF(Отзывы!A:A, A2)=СЧЁТЕСЛИ(Отзывы!A:A; A2).
Теперь на листе Товары одна строка — один товар, и всё считается как обычно: =CORREL(B2:B500, D2:D500)=КОРРЕЛ(B2:B500; D2:D500).
«Товар с наибольшей средней оценкой» — сортировка по новому столбцу по убыванию.

Если таблицы в двух разных файлах — скопируйте лист из одного файла в другой: правый клик по ярлыку листа → «Переместить или скопировать / Move or Copy» → в списке «В книгу / To book» выберите второй файл, галочка «Создать копию / Create a copy».

Строка — это кто? Участник или команда

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

Скопируйте лист (правый клик по ярлыку → «Переместить или скопировать / Move or Copy» → «Создать копию / Create a copy»).
На копии: «Данные / Data» → «Удалить дубликаты / Remove Duplicates» → оставьте галочку только у столбца Команда.
Считайте CORRELКОРРЕЛ, средние и т. д. на этой копии.

И наоборот: вопрос про участников («средний балл школьников из Казани») считаем по исходной таблице, где строка = участник.

Одна таблица, где свойства объекта повторяются в каждой строке

Пример: таблица оценок, строка = одна оценка одного зрителя, и в каждой строке ещё раз записаны бюджет, год и длительность фильма. Фильм с 10 000 оценок встречается 10 000 раз. Вопросы «чем новее фильм, тем…», «чем длиннее фильм, тем…» — про фильмы, поэтому сначала сделайте таблицу «один фильм — одна строка».

Сводная таблица: id фильма в «Строки / Rows»; в «Значения / Values» положите оценку, бюджет, год, длительность, популярность — у каждого поля «Параметры полей значений / Value Field Settings» → «Среднее / Average». У бюджета и года среднее по одному фильму — это просто его бюджет и год, а у оценки — средняя оценка фильма.
Выделите сводную → Ctrl+C → новый лист → «Специальная вставка / Paste Special» → «Значения / Values». Теперь это обычная таблица: строка = фильм.
Корреляции и средние считайте на ней. Прежде чем отвечать «связаны» или «чем больше, тем больше», проверьте значимость: =r*SQRT(n-2)/SQRT(1-r^2)=r*КОРЕНЬ(n-2)/КОРЕНЬ(1-r^2), где n — число фильмов; |t| < 1,96 — «не следует» (тип 22).

Проверено на таком файле

По строкам-оценкам «бюджет растёт со временем» выходит верным (r ≈ 0,26 на сотнях тысяч строк), а по фильмам r ≈ 0 — и правильный ответ «не следует». И ещё: «21 век» — это годы с 2001, «20 век» — до 2000 включительно.

Среднее по «своей» группе без самого себя

Пример: «коллеги — это люди с тем же отделом (D), должностью (E) и годом (F)». Нужно для каждого посчитать число коллег и их среднюю оценку G, не считая его самого.

Число коллег (столбец H): =COUNTIFS(D:D, D2, E:E, E2, F:F, F2) - 1 Средняя оценка коллег (столбец I): =IF(H2=0, "", (SUMIFS(G:G, D:D, D2, E:E, E2, F:F, F2) - G2) / H2)

Число коллег (столбец H): =СЧЁТЕСЛИМН(D:D; D2; E:E; E2; F:F; F2) - 1 Средняя оценка коллег (столбец I): =ЕСЛИ(H2=0; ""; (СУММЕСЛИМН(G:G; D:D; D2; E:E; E2; F:F; F2) - G2) / H2)

«Минус 1» и «минус G2» убирают самого человека из его группы. Если год надо взять из даты — сначала столбец =YEAR(дата)=ГОД(дата). «Сколько людей имеют хотя бы одного коллегу»: =COUNTIF(H:H, ">0")=СЧЁТЕСЛИ(H:H; ">0"). Дальше — обычные AVERAGEСРЗНАЧ и CORRELКОРРЕЛ по новым столбцам (строки с пустым I они пропустят сами).

Таблица «на 1-е число»: изменения за месяц и квартал

Остатки (вклады, долги, население) в таблицах обычно даны на дату: «01.01.2024», «01.04.2024». Тогда:

За январь — от 1 января до 1 февраля. За апрель и май — от 1 апреля до 1 июня (два месяца). За 1 квартал — от 1 января до 1 апреля. За 2023 год — от 01.01.2023 до 01.01.2024. «На 1 июля» — столбец 01.07.
Прирост за период: =C5/B5-1 (новое / старое − 1).
Среднемесячный темп за n месяцев: =(C5/B5)^(1/n)-1. За апрель–май n = 2, за квартал n = 3, за год n = 12.
«Все группы росли не менее 1% в месяц» — посчитайте среднемесячный темп для каждой строки и посмотрите на самый маленький: =MIN(…)=МИН(…).
«Наибольшую долю составляют…» — доля = строка / «Итого» в том же столбце.

Цены по дням: доходность и риск

Пример: столбец A — дата, B — цена акции, в выходные стоит цена пятницы. «Какая акция самая рискованная».

Уберите дни без торгов: столбец =WEEKDAY(A2, 2)=ДЕНЬНЕД(A2; 2), оставьте строки, где он меньше 6. Если в будний день цены всех бумаг такие же, как накануне, — это праздник, его тоже уберите. Иначе ненастоящие «нулевые изменения» занизят разброс.
Чем мерить риск — смотрите в условии. Если сказано «коэффициент вариации» — считайте его по ценам торговых дней: =STDEV.S(B2:B23)/AVERAGE(B2:B23)=СТАНДОТКЛОН.В(B2:B23)/СРЗНАЧ(B2:B23). Больше — рискованнее. Если в ответе допускают два числа (например, 0,059 и 0,058), второе обычно получается через STDEV.PСТАНДОТКЛОН.Г — оба верны.
Если мера риска не указана — считайте разброс доходностей: доходность за день (со второй строки) =B3/B2-1, протянуть, затем =STDEV.S(C3:C23)=СТАНДОТКЛОН.В(C3:C23). Доходности, а не цены, потому что у дорогой акции цена «скачет» на больше рублей просто из-за масштаба.

Единицы в файле

Сверьте с описанием, в чём записан столбец. Длительность в миллисекундах → минуты: /60000; в секундах → минуты: /60. Деньги в тысячах или миллионах — ответ переведите так, как просят. Доля 0–1 или проценты 0–100 — тоже частая путаница.

«Мог ли получиться такой график из этих данных»

Прочитайте подписи осей графика: какие группы, какой показатель (среднее, доля, количество).
Сделайте такую же сводную: группы — в «Строки / Rows», показатель — в «Значения / Values» с тем же видом (Среднее / Количество).
Сравните порядок групп по высоте и примерные значения. Доли по всем группам вместе должны давать 100%. Если на графике есть группа, которой нет в данных, — график не подходит.

Ещё частые ловушки

Фильтр не влияет на формулы. Скрытые фильтром строки AVERAGEСРЗНАЧ и SUMСУММ всё равно считают. Для подсчёта только видимых строк: =SUBTOTAL(101, D:D)=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(101; D:D) (101 — среднее, 109 — сумма, 103 — количество). Надёжнее не полагаться на фильтр и писать условия в формулу.
Формат ячейки только прячет цифры. Если ячейка показывает 0,11, внутри может быть 0,10869. Округляйте функцией =ROUND(x, 3)=ОКРУГЛ(x; 3) или посмотрите полное значение в строке формул.
Доля или проценты. 0,109 и 10,9% — одно и то же число. В ответ пишите в том виде, который просят.
CSV открылся в одну колонку или числа стали датами. Не открывайте CSV двойным щелчком. «Данные / Data» → «Из текста/CSV / From Text/CSV» → кодировка «65001: Юникод (UTF-8) / 65001: Unicode (UTF-8)», правильный разделитель (запятая или точка с запятой) → «Загрузить / Load». Бывает и так: поля разделены ;, а дроби записаны через точку (0;0.1318). Выберите разделитель «точка с запятой», а если числа после загрузки прижались влево (стали текстом) — Ctrl+H: точку заменить на запятую в этом столбце.
Десятичная точка. В русском Excel дробная часть пишется через запятую. Если числа пришли как 3.253,25, они стали текстом: «Найти и заменить / Find and Replace» (Ctrl+H) точку на запятую в этом столбце.
Пустая ячейка ≠ 0. AVERAGEСРЗНАЧ пустые пропускает, а нули считает.
«Есть оба показателя». Если просят считать только строки, где заполнены два столбца, добавьте оба условия: D:D, "<>"D:D; "<>" и E:E, "<>"E:E; "<>" («не пусто»).

Все формулы: английские и русские названия

Английский Excel	Русский Excel	Что делает
`COUNTA`	`СЧЁТЗ`	число непустых ячеек
`COUNTIFS`	`СЧЁТЕСЛИМН`	число строк по условиям
`AVERAGEIFS`	`СРЗНАЧЕСЛИМН`	среднее по условиям (и доля для столбца 0/1)
`SUMIFS`	`СУММЕСЛИМН`	сумма по условиям
`MINIFS` / `MAXIFS`	`МИНЕСЛИ` / `МАКСЕСЛИ`	минимум / максимум по условиям
`MEDIAN`	`МЕДИАНА`	медиана
`FILTER`	`ФИЛЬТР`	оставить значения по условию
`UNIQUE`	`УНИК`	уникальные значения
`QUARTILE.INC`	`КВАРТИЛЬ.ВКЛ`	квартиль (1 — Q1, 3 — Q3)
`PERCENTILE.INC`	`ПРОЦЕНТИЛЬ.ВКЛ`	процентиль (0,9 — 90-й)
`STDEV.S` / `.P`	`СТАНДОТКЛОН.В` / `.Г`	стандартное отклонение: выборочное / генеральное
`CORREL`	`КОРРЕЛ`	корреляция Пирсона
`SLOPE` / `INTERCEPT`	`НАКЛОН` / `ОТРЕЗОК`	коэффициенты прямой y = a + b·x
`T.TEST`	`СТЬЮДЕНТ.ТЕСТ`	p-value для разницы средних
`NORM.S.DIST`	`НОРМ.СТ.РАСП`	вероятность для стандартного нормального (по z)
`XLOOKUP` / `VLOOKUP`	`ПРОСМОТРX` / `ВПР`	подтянуть значение из другой таблицы
`TRIM`	`СЖПРОБЕЛЫ`	убрать лишние пробелы
`YEAR`, `MONTH`, `WEEKDAY`	`ГОД`, `МЕСЯЦ`, `ДЕНЬНЕД`	части даты
`IF`, `AND`, `OR`	`ЕСЛИ`, `И`, `ИЛИ`	условия
`ROUND`	`ОКРУГЛ`	округлить
`COUNTIF / SUMIF / AVERAGEIF`	`СЧЁТЕСЛИ / СУММЕСЛИ / СРЗНАЧЕСЛИ`	то же с одним условием
`SQRT, LN, EXP, ABS`	`КОРЕНЬ, LN, EXP, ABS`	корень, логарифм, экспонента, модуль
`GEOMEAN`	`СРГЕОМ`	среднее геометрическое
`NORM.DIST / NORM.INV`	`НОРМ.РАСП / НОРМ.ОБР`	доля ниже x / граница по доле (нормальное)
`COMBIN / PERMUT`	`ЧИСЛКОМБ / ПЕРЕСТ`	сочетания / размещения
`SUBSTITUTE, VALUE`	`ПОДСТАВИТЬ, ЗНАЧЕН`	заменить кусок текста; текст → число
`LEFT, MID, LEN`	`ЛЕВСИМВ, ПСТР, ДЛСТР`	вырезать часть текста; длина
`DATE, TIME`	`ДАТА, ВРЕМЯ`	собрать дату и время из чисел
`IFERROR`	`ЕСЛИОШИБКА`	что вернуть, если ошибка
`COUNTBLANK`	`СЧИТАТЬПУСТОТЫ`	число пустых клеток
`SUBTOTAL`	`ПРОМЕЖУТОЧНЫЕ.ИТОГИ`	считать только видимые строки
`AVERAGE`	`СРЗНАЧ`	среднее
`ISNUMBER`	`ЕЧИСЛО`	число ли в ячейке
`MAX`	`МАКС`	максимум
`MIN`	`МИН`	минимум
`ROWS`	`ЧСТРОК`	число строк диапазона (в т.ч. результата UNIQUE)