Вопрос или проблема
Я пытаюсь создать график, показывающий количество купленных в каждый год автомобилей BEV/HEV/ICE.
Я знаю, что формула должна содержать два условия, но не могу понять, как заставить ее игнорировать дубликаты или нужно ли их игнорировать?
Вот фотография данных. Столбец K – это год, M – тип автомобиля, а E – уникальный идентификатор для каждого автомобиля:
При условии, что вы просто хотите подсчитать все для различных типов автомобилей, простым решением будет следующее. Я сохранил три столбца данных в диапазоне A1:C22. Уникальные типы автомобилей хранятся в E2:E5, а последовательность годов – в F1:W1.
Теперь вы можете ввести следующую формулу в ячейку F2 и скопировать ее во все ячейки:
=SUMIFS($C$1:$C$22,$A$1:$A$22,$E2,$B$1:$B$22,F$1)
Вы суммируете столбец C (sum_range), если vehicle_type совпадает (условие 1) и year совпадает (условие 2), соответственно.
Итоговый вывод выглядит следующим образом:
Использованные данные следующие (могут потребоваться корректировки в зависимости от вашего конкретного столбца, который фиксирует количество купленных автомобилей):
| vehicle_type | замена | покупка |
|---|---|---|
| HEV | 2026 | 24227 |
| HEV | 2033 | 26045 |
| PHEV | 2040 | 22149 |
| ICE | 2023 | 18159 |
| ICE | 2029 | 25247 |
| ICE | 2036 | 28432 |
| HEV | 2026 | 24227 |
| HEV | 2033 | 26045 |
| PHEV | 2040 | 22149 |
| BEV | 2029 | 35281 |
| BEV | 2036 | 27247 |
| ICE | 2026 | 26261 |
| ICE | 2033 | 28496 |
| ICE | 2040 | 32732 |
| ICE | 2023 | 28781 |
| ICE | 2029 | 34844 |
| ICE | 2036 | 39240 |
| ICE | 2029 | 32323 |
| ICE | 2036 | 32454 |
| ICE | 2029 | 32323 |
| ICE | 2036 | 32454 |
Вы можете использовать COUNTIFS для этого, следующим образом.
Сначала создайте заголовки столбцов с типами и первую строку с годами (что-то вроде ниже, я разместил типы в столбцах, а годы в строках, так как лет больше, поэтому так удобнее):
Затем в ячейке на пересечении BEV:2026 (B2 в примере) введите следующее (где B$1 – это ячейка с BEV, а $A2 – ячейка с 2026):
=COUNTIFS($M:$M,B$1,$K:$K,$A2)
Заполните перетаскиванием для всех типов, затем (выбрав все результаты первого года) заполните перетаскиванием для всех лет.
Если вы хотите, чтобы заголовки столбцов (типы) и первая строка (годы) автоматически генерировались на основе данных (и вам не нужны строки для лет, в которых нет продаж), то вы можете использовать следующее:
- В
A2используйте:=UNIQUE(FILTER(K:K,K:K<>"")) - В
B1используйте:=TRANSPOSE(UNIQUE(FILTER(M:M,M:M<>"")))
Затем в B2 используйте это (и перетаскивайте во множество столбцов/строк, в диапазоне, где не будет ничего в годе или типе, это приведет к пустому значению):
=IF(OR(B$1="",$A2=""),"",COUNTIFS($M:$M,B$1,$K:$K,$A2))
Ответ или решение
Для начала давайте разберёмся с поставленной задачей. Вам необходимо создать диаграмму, отображающую количество транспортных средств различных типов (BEV, HEV, ICE), приобретённых за каждый год. При этом нужно учитывать два условия: тип транспортного средства и год его приобретения, а также игнорировать дубликаты.
1. Определение задач
Ваша таблица данных содержит три столбца:
- Произведённый тип транспортного средства (столбец M)
- Год приобретения (столбец K)
- Уникальный идентификатор транспортного средства (столбец E)
2. Зачем игнорировать дубликаты
Одной из ключевых задач является точное подсчёт уникальных транспортных средств, чтобы данные были корректными и неповторяющимися. Если вы будете учитывать каждый дублирующийся идентификатор, это приведёт к завышенным показателям в итоговом отчёте. Поэтому игнорирование дубликатов – важный шаг.
3. Использование формул
Для решения этой задачи вы можете воспользоваться функцией COUNTIFS в Excel, которая позволяет одновременно учитывать несколько условий.
Шаги по созданию формулы
-
Создание заголовков:
- Введите типы транспортных средств в ячейки столбца и года в ячейки строки. К примеру, в ячейках B1, C1, D1 будут указаны BEV, HEV, ICE соответственно, а в ячейках A2, A3, A4 – годы.
-
Введение формулы:
- В ячейку B2 (пересечение 2026 года и типа BEV) введите следующую формулу:
=SUM(--(FREQUENCY(IF(($M$2:$M$22=B$1)*($K$2:$K$22=A2), $E$2:$E$22), $E$2:$E$22)>0))- Данная формула работает следующим образом:
IF(($M$2:$M$22=B$1)*($K$2:$K$22=A2), $E$2:$E$22)создаёт массив уникальных идентификаторов, которые соответствуют заданным критериям (типу и году).FREQUENCYсчитает количество уникальных идентификаторов в этом массиве.SUM(--(...>0))подводит итог по количеству уникальных значений, которое будет равно количеству уникальных транспортных средств для указанного типа и года.
-
Применение и заполнение формулы:
- После ввода формулы в B2, протяните её вправо и вниз для всех типов и годов.
4. Дополнительные рекомендации
-
Автоматизация заголовков: Вы можете использовать функции
UNIQUEиFILTERдля автоматического создания заголовков по типам и годам, чтобы упрощать задачу в будущем:=UNIQUE(FILTER(K:K,K:K<>""))=TRANSPOSE(UNIQUE(FILTER(M:M,M:M<>""))) -
Форматирование итогов: Убедитесь, что ваши итоги хорошо визуализированы на диаграмме, чтобы данные были понятны и наглядны.
Заключение
Эти шаги помогут вам точно подсчитать уникальное количество транспортных средств по типам и годам, игнорируя дубликаты. Данный подход обеспечивает точность данных и поможет вам создать информативную диаграмму для анализа распределения продаж транспортных средств по годам.