☰

Извлечение дат с общей точкой в ​​Excel

tag-icon • tag-icon microsoft-excel
Извлечение дат с общей точкой в ​​Excel

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

State/Date 01Jan 02Jan 03Jan 04Jan 05Jan 
Alabama     YES   YES   NO    YES   NO
Alaska      YES   YES   YES   NO    YES
Florida     NO    NO    NO    YES   NO
Nevada      NO    YES   NO    YES   NO

Как написать формулу, которая даст мне список дат, когда в определенном штате шел дождь? Например, я хочу узнать, когда в Неваде шел дождь. Могу ли я получить ответ 02Jan, 04Janв ячейке?

решение1

Ниже представлен метод, который решает эту проблему:

введите описание изображения здесь

Как это работает:

  • Для более быстрого получения результатов создайте раскрывающийся список для штатов в ячейке A28.
  • Нажмите вкладку «Данные», затем «Проверка данных».
  • В качестве источника списка выберите D28:D31.

введите описание изображения здесь

  • Теперь для «Дождь» или «Нет дождя» создайте раскрывающийся список в ячейке B28, используя проверку данных, а для «Источника списка» добавьте «Да» и «Нет».

введите описание изображения здесь

  • Теперь окончательная формула массива (CSE) в ячейке A35:

    {=IFERROR(INDEX($E$27:$I$27,1,SMALL(IF(INDEX($E$28:$I$31,MATCH($A$28,$D$28:$D$31,0),)=$B$28,COLUMN($A$26:$E$26)),ROW(1:1))),"")}

Примечание.

  • Закончите формулу с помощьюCtrl+Shift+Enterи залейте.

  • При необходимости измените ссылки на ячейки в формуле.

решение2

Честно говоря, простое создание сводной таблицы из ваших данных, вероятно, будет лучшим подходом. Но если вам нужен подход с формулой:

Допустим, ваши данные находятся в ячейках A1:F5, включая заголовки. Тогда, если ваше искомое значение находится в ячейке G8, следующее:

=TEXTJOIN(", ",TRUE,TEXT(IFERROR(IF(IF(XLOOKUP(G8,A2:A5,B2:F5)="YES",B1:F1,"0")/IF(XLOOKUP(G8,A2:A5,B2:F5)="YES",B1:F1,"0")>0,IF(XLOOKUP(G8,A2:A5,B2:F5)="YES",B1:F1,"0"),""),""),"dd-mmm"))

дает вам вывод, который выглядит следующим образом:

02-янв., 04-янв.

если искать "Невада".

И так далее.

По сути, он использует первый XLOOKUP()для поиска строки данных ДА и НЕТ, которая относится к штату. IF()Обертка, которая дает 0 для НЕТ и дату в строке заголовка для ДА. Это обертывается , IF()который использует предыдущий результат, деленный на себя (что дает ОШИБКИ и 1), в проверке, чтобы увидеть, является ли результат >0, каковыми являются 1. Эти ОШИБКИ продолжатся, но второй IF()снова заменяет 1 датами. Затем обертывает IFERROR()это, чтобы сократить результат только до тех элементов, которые были датами, а не ОШИБКАМИ. Это целые значения дат Excel (скажем, 43563) на этом этапе, и, похоже, они нужны в той форме, в которой они отображаются в строке заголовка, поэтому форматирует TEXT()их так. Тот факт, что они теряют свою «числовость», не является проблемой, поскольку они сделали бы это в любом случае, если бы были перечислены (ну, если только не была возвращена только одна дата... если кто-то хочет «быть техническим»...). Наконец, TEXTJOIN()для удобства чтения используется разделитель «запятая-пробел» (и поэтому у них есть разделитель, который можно использовать для извлечения одного или всех символов, если это необходимо в дальнейшей работе).

Вероятно, есть и другие подходы, XLOOKUP()версия давней техники "двойного ИНДЕКСА/СОПОСТАВЛЕНИЯ" может быть одним из них, хотя получение нескольких ответов от XLOOKUP()дало мне несколько ответов (5, как и ожидалось), но это были три 01 января и два 02 января, а не, например, 02 января и 04 января, которые я хотел. Хотя сейчас 3 часа ночи, так что, возможно, я что-то там упустил. FILTER()действовал слишком быстро, потеряв позиционную информацию, чтобы получить две даты для Невады (сразу только два результата, но теперь 1-2, а не 2-4, так что нет хорошего способа вытащить даты из заголовка). Могут быть и другие идеи. Опять же, 3 часа ночи...

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

Связанный контент