← Все курсы · Курс Excel · Статья 0: порядок сборки

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

Статья 1 Excel для бизнес-аналитиков · Один добавленный столбец, одно вложенное ЕСЛИ, выполняется на 82 956 действительных строк · Часть Analyst Prep Kit

К Майкл Носито, аналитик данных · Опубликовано 7 августа 2026 г.

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

Отличие вот в чем. Фильтр скрывает строки. Этикетка хранит их. Если ваш вопрос сравнивает две группы, а это почти каждый вопрос, достойный диаграммы, фильтр уничтожает сравнение в тот момент, когда вы его применяете. В итоге вы видите на экране одну группу, не имея возможности составить график «остальных» и не подозревая, что чего-то не хватает.

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

Ловушка: фильтр кажется ответом

Сначала спрогнозируйте кое-что: если вы отфильтруете 82 956 строк до 175, которые вас интересуют, что произойдет с остальными 82 781 строками при построении диаграммы? Держи свой ответ.

Вопрос в стимовской сборке был о сравнении. Некоторые по-настоящему любимые игры нашли аудиторию, а некоторые не менее любимые остались скрытыми. Чем отличаются эти две группы?

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

Вот сколько вам обойдется фильтр. Группа сравнения исчезла. Найденные 590 любимых игр скрыты от глаз, поэтому фразе «скрытые игры имеют более низкие цены» нечего противопоставить. Ниже чего? В тот момент, когда вы хотите, чтобы обе группы были на одной диаграмме, а каждая сравнительная диаграмма хочет именно этого, фильтру нечего вам дать. Нужные вам строки — это те, которые вы удалили.

Что такое столбец меток

Столбец метки — это один новый столбец, в каждую строку которого записывается имя группы. Ни одна строка не удаляется. В каждой строке содержится ответ на один и тот же вопрос: в какой вы группе?

В сборке Пар колонка называется Segment, и каждая из 82 956 игр помещается в одну из четырех групп:

В сумме эти четыре числа дают 82 956 — количество строк в файле. Эта дополнительная проверка имеет значение позже.

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

Формула: один тест за раз

IF задает один вопрос с ответом «да» или «нет» по одной строке и дает один из двух ответов. Вот и весь инструмент:

=IF(test, value_if_yes, value_if_no)

Один тест дает вам две группы. Вам нужно четыре, поэтому формула задает вопросы подряд: каждое «нет» передает строку следующему вопросу. Это все, что представляет собой вложенный IF. Следующий IF занимает место «нет» предыдущего.

Вот формула сегмента из сборки. Данные представляют собой таблицу Excel с именем Games, поэтому ссылка типа [@TotalReviews] означает «значение Всего отзывов этой строки». Присвоение имени таблице — это шаг 1 порядка сборки, и для нее создается отдельная статья. А пока введите формулу в первую ячейку нового столбца с именем Segment:

=IF([@TotalReviews]<2000, "Unproven",
  IF([@PctPositive]<95, "Proven, not loved",
    IF([@IsHiddenGem]=1, "Loved, hidden",
      "Loved, found")))

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

Прочтите это как коридор дверей. Каждый ряд идет вверху.

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

Порядок вопросов имеет значение. Каждый тест видит только те строки, которые прошли предыдущие тесты, поэтому сначала поставьте самый дешевый дисквалифицирующий тест. Здесь «менее 2000 отзывов» очищает 78 064 строки у первой двери, и последующим тестам о них никогда не приходится думать. Если вы знаете SQL, это тот же ход, что и выражение CASE, где побеждает первое совпадение и порядок является логикой. Та же идея, но другое написание.

Убедитесь, что метки приземлились

Это шаг 4 порядка построения, примененного к одному столбцу: определите, какими должны быть значения, а затем посмотрите. Две ячейки COUNTIF рядом с данными определяют его:

=COUNTIF(Games[Segment], "Loved, hidden")    → 175
=COUNTIF(Games[Segment], "Loved, found")     → 590

Функция СЧЕТЕСЛИ просматривает столбец и подсчитывает ячейки, соответствующие одному значению. Две ожидаемые цифры были получены в результате анализа источников: 765 любили игры, из которых 175 оставались скрытыми. Если в первой ячейке указано 174, формула неверна, и это незаметно. Ничто на экране не становится красным, если метка попадает не в те строки.

Затем проверка добавления. Четыре счетчика групп должны суммироваться с количеством строк в файле. 78 064 плюс 4 127 плюс 175 плюс 590 составит 82 956. Если это не так, некоторые строки не имеют меток или имеют две метки, и обычной причиной является опечатка в одной из строк меток.

Сохраните обе проверки в файле, на видном месте, с пометкой CHECK. Они стоят две клетки и сообщают следующему человеку, что на них посмотрел человек.

До и после, тот же вопрос

Тот же файл, тот же вопрос: цена скрытых игр отличается от найденных?

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

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

Когда фильтр — правильный инструмент

Фильтры сохраняют реальную работу. Граница – это один вопрос: вы ищете или строите?

Ищем, фильтруем. Вы хотите просмотреть 175 скрытых игр, выборочно проверить ряд или ответить: «Это одна игра в группе?» Фильтр моментальный и одноразовый, и в этом все его достоинство.

Здание, этикетка. Все, что будет служить основой для сводной таблицы, диаграммы, СЧЕТЕСЛИ или другого человека, получает столбец метки, потому что всем этим необходимо, чтобы каждая строка присутствовала с написанной на ней группой.

Неправильная привычка — использовать инструмент поиска для строительных работ. Если вещь, которую вы делаете, переживет следующие десять минут, пометьте ее.

Почему это работает

Две причины: одна о диаграммах, другая об арифметике.

Причина диаграммы: сравнение происходит только тогда, когда оба значения находятся на одной и той же картинке в одном масштабе. Эксперименты Кливленда и МакГилла по графическому восприятию показали, что оценка позиций по общей шкале — это то, что люди делают наиболее точно, а оценка значений, хранящихся отдельно, по памяти или по различным представлениям, — это то, где точность падает (Кливленд & МакГилл, 1984, Журнал Американской статистической ассоциации, 79(387), 531-554). Отфильтрованный лист вызывает именно такое более слабое суждение: одна группа на экране, другая в вашей памяти о другом фильтре.

Арифметическая причина: ошибки в таблицах — это нормально, а не редко. Обзор аудиторских исследований, проведенный Панко, обнаружил ошибки в нескольких процентах ячеек практически в каждой проверенной электронной таблице (Панко, 1998, Журнал организационных вычислений и вычислений для конечных пользователей, 10(2), 15-21). Столбец метки можно проверить так, как не могут быть разрозненные фильтры, поскольку его группы должны суммироваться с количеством строк. Это одно дополнение улавливает в одной ячейке метки с ошибками, пропущенные строки и перекрывающиеся тесты.

Запустите его в своем файле

1. Напишите вопрос в виде сравнения. «Чем строки X отличаются от строк Y?» Если вы не можете назвать одновременно X и Y, значит, вы еще не готовы что-либо составить.
2. Прежде чем писать формулу, назовите группы. От двух до пяти надписей, понятными незнакомому человеку. Метки окажутся на оси диаграммы, поэтому напишите их для читателя.
3. Закажите тесты, сначала самый дешевый дисквалификатор. Тест, который очищает наибольшее количество рядов, проводится в верхней части коридора.
4. Напишите вложенное ЕСЛИ и позвольте таблице заполнить его. Если ваши данные еще не являются именованной таблицей, сначала стоит это исправить. Для этого достаточно одного нажатия клавиши: Ctrl+Т.
5. Предскажите два значения, затем проверьте с помощью COUNTIF. Прежде чем смотреть, запишите ожидаемые цифры. Чек, который вы читаете постфактум, подтверждает все, что на экране.
6. Добавьте проверку суммы. Количество групп должно равняться количеству строк. Оставьте это в файле с пометкой.

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

Шпаргалка

Ссылки

• Кливленд, У.С., и МакГилл, Р. (1984). Графическое восприятие: теория, эксперименты и применение к разработке графических методов. Журнал Американской статистической ассоциации, 79(387), 531-554.
• Панко, Р.Р. (1998). Что мы знаем об ошибках в электронных таблицах. Журнал организационных вычислений и вычислений для конечных пользователей, 10(2), 15-21.

Каков был ваш ответ вверху о 82 781 строке, удаленной фильтром? Вы заметили, что диаграмма теряет их еще до того, как диаграмма была упомянута?