У меня есть набор чисел, разделенных запятыми. Каждый набор представляет собой ряд уникальных чисел, относящихся к проекту. Как написать формулу для поиска в справочном листе любого количества элементов в списке и возврата жестко закодированного значения?
Ниже я приведу примеры (без идентификации) для каждого листа (таблицы 1-5) и предполагаемый/ожидаемый вывод. Я бы предположил, что это что-то связанное с SUM, FREQUENCY, INDEX или какой-то другой формулой массива, но я не могу понять. Вы можете увидеть мой вопросздесьтакже, но там содержится почти та же информация.
Я использовал "пзадница" "Фаил" и "Миссинг", но все работает.
Логика:
Формула должна выполнять следующие шаги.
- Извлеките список чисел из каждой ячейки в столбце Cтаблица 4.
- Поисктаблица 3(столбец D) для экземпляров чисел в списке.
2b. Если в списке появляется какое-либо число, проверьте, не провалился ли аудит (обозначается текстом «неуспешно» в столбце Bтаблица 3).
2c. Если да, выведитеФ2d .
Если нет, распечататьп. - Если ни один из номеров в списке не отображается, выведитеМ.
Таблицы:
Таблица 1
Эта таблица является основным справочным листом. В ней перечислены все UID по имени, серверу и проекту.
Unique ID Name Server Address Project
10000 Company 1 Server 1 Project 1
10001 Company 2 Server 2 Project 2
10002 Company 3 Server 3 Project 3
10003 Company 3 Server 3 Project 4
10004 Company 3 Server 3 Project 5
10005 Company 3 Server 3 Project 6
10006 Company 4 Server 4 Project 7
10007 Company 4 Server 4 Project 7
10008 Company 4 Server 4 Project 7
10009 Company 5 Server 5 Project 8
10010 Company 6 Server 6 Project 9
10011 Company 7 Server 7 Project 10
10012 Company 8 Server 8 Project 11
10013 Company 8 Server 8 Project 11
10014 Company 8 Server 8 Project 11
10015 Company 8 Server 8 Project 11
10016 Company 8 Server 8 Project 11
10017 Company 8 Server 8 Project 11
10018 Company 8 Server 8 Project 11
10019 Company 8 Server 8 Project 11
10020 Company 8 Server 8 Project 11
10021 Company 9 Server 9 Project 12
10022 Company 10 Server 10 Project 13
10023 Company 11 Server 10 Project 14
10024 Company 12 Server 10 Project 15
10025 Company 13 Server 10 Project 16
10026 Company 14 Server 10 Project 17
10027 Company 15 Server 10 Project 18
10028 Company 16 Server 10 Project 19
10029 Company 17 Server 10 Project 20
10030 Company 18 Server 10 Project 21
10032 Company 19 Server 10 Project 23
10033 Company 19 Server 10 Project 24
Таблица 2
эта таблица содержит всю ту же информацию, что иТаблица 1но группирует каждый UID в соответствующий ему проект. Я в настоящее время не использую его, но это может быть полезно? Я удалю, если нет.
Company Trading Name Project UID UID UID UID
Company 1 Project 1 10000
Company 2 Project 2 10001
-----------------------------------------------------
Company 4 Project 7 10006 10007 10008
таблица 3
эта таблица — место, куда вставляются результаты аудита. Они следуют этой схеме в точности, со столбцом примечаний, имеющим слово failed, если аудит не пройден (условное форматирование выделяет это в настоящее время), и UID, извлеченный простой функцией LEFT. Затем столбцы Name и company используют VLOOKUP против UID вТаблица 1чтобы получить имя/сервер.
Notes Time Rec. UID Name Server
Audit for company passed:10001 auto 10001 Company 2 Server 2
Audit for company passed:10006 auto 10006 Company 4 Server 4
Audit for company failed:10007 auto 10007 Company 4 Server 4
таблица 4
Это лист аудита. Формула будет помещена в столбец D, а вывод будет копироваться/вставляться как значения каждый день. Я заполнил здесь несколько примеров того, как это должно выглядеть впоследствии.
Server Name Project (UID) Today's Date
Server 1 Company 1 Project 1 (10000) P
Server 2 Company 2 Project 2 (10001) N
Server 3 Company 3 Project 3 (10002,10003,10004,10005) F
Server 4 Company 4 Project 4 (10006,10007,10008) P
-----------------------------------------------------
Server 10 Company 10 Project 13 (10022) P
Server 10 Company 11 Project 14 (10023) M
Server 10 Company 12 Project 15 (10024) P
Server 10 Company 13 Project 16 (10025) P
Server 10 Company 15 Project 18 (10027) F
Server 10 Company 16 Project 19 (10028) P
Server 10 Company 17 Project 20 (10029) M
Server 10 Company 18 Project 21 (10030) P
Server 10 Company 19 Project 22 (100310) P
Server 10 Company 19 Project 23 (10032) P
Server 10 Company 19 Project 24 (10033) M
В качестве альтернативы у меня есть пятый стол,таблица 5, который автоматически ищет любые отсутствующие идентификаторы изтаблица 3используя следующую формулу:
=INDEX(Reference!$A$2:$A$160, SMALL(IF(ISERROR(MATCH(Reference!$A$2:$A$160, Check!$D$2:$D$350, 0)), (ROW(Reference!$A$2:$A$160)-MIN(ROW(Reference!$A$2:$A$160))+1), ""), ROWS($A$1:A1)))
Когдатаблица 3пусто (оно очищается каждый день), это просто список всех проектов. Когда любое количество аудитов вставлено втаблица 3Однако список втаблица 5обновлю, чтобы показать, чего не хватает. Возможно, есть способ включить это в окончательную формулу.
У меня есть обезличенная версия, если кому-то она нужна для работы.
Спасибо.
edit1: Кто-то запросил больше подробностей о системе. Это в среде Windows 10 с использованием Excel 365.
решение1
Я попытался собрать подход, который, как я признаю, не очень масштабируемый и использует несколько вспомогательных столбцов. Я не уверен, есть ли прямой способ поиска разделенных запятыми значений за один раз. Это может быть возможно, но я не эксперт по Excel.
Более того, я думаю, что версия Office 365 Desktop поддерживает макросы VBA. Если у вас есть версия для настольного компьютера, вы можете выбрать решение с макросами VBA, которое может быть более оптимизированным. Однако я думаю, что макросы не поддерживаются в версии Online.
Смотрите снимок ниже.
Имеется 5 вспомогательных столбцов. H, M, N, O, P. H может быть необязательным, но я предпочитаю использовать простую числовую последовательность, поскольку имена ваших серверов могут на самом деле не иметь последнего символа в качестве цифр, например, Server 1, Server 2 в таком порядке.
В ячейку M4 введите следующую формулу и перетащите ее вниз.
=SUBSTITUTE(RIGHT(K4,LEN(K4)-FIND("(",K4)),")","")
Это создает правильный список UID, разделенных запятыми.
В N4 введите следующую формулу и перетащите ее вниз до предварительного количества ячеек. Когда нечего отображать, содержимое ячейки будет пустым, но продлите его вниз для последующего предоставления, так как количество UID может меняться.
=VALUE(TRIM(MID(SUBSTITUTE(TEXTJOIN(",",TRUE,$M$4:$M$22),",",REPT(" ",99)),(ROW(1:1)-1)*99+1,99)))
В ячейку O4 введите следующую формулу и перетащите ее вниз.
=IF(ISNUMBER(VALUE(N4)),MATCH("*"&N4&"*",$M$4:$M$22,0),"")
Здесь перечислены позиции UID, которые будут использоваться позже.
В ячейку P4 вставьте следующую формулу и перетащите ее вниз.
=IF(ISNUMBER(VALUE(N4)),IF(ISNUMBER(FIND("passed",INDEX($B$4:$B$22,MATCH(VALUE(N4),$D$4:$D$22,0)))),"P",IF(ISNUMBER(FIND("failed",INDEX($B$4:$B$22,MATCH(VALUE(N4),$D$4:$D$22,0)))),"F","M")),"")
Это определяет статус каждого UID из Таблицы 4.
Теперь выходной столбец L в моем примере.
В L4 поместите следующую формулу и перетащите ее вниз.
=IF(COUNTIFS($O$4:$O$22,H4,$P$4:$P$22,"M")=COUNTIF($O$4:$O$22,H4),"M",IF(COUNTIFS($O$4:$O$22,H4,$P$4:$P$22,"P")+COUNTIFS($O$4:$O$22,H4,$P$4:$P$22,"M")=COUNTIF($O$4:$O$22,H4),"P","F"))
Это применяет окончательную логику для определения статуса аудита «Пройдено, Не пройдено» или «Отсутствует» на основе транспонированного списка в столбцах Helper.
Вы можете преобразовать эти наборы данных в таблицы Excel и использовать ссылки Table[Header], чтобы сделать их более динамичными.
Попробуйте и решите, может ли это быть вам полезным. Я согласен, что это не так уж и здорово, так как использует несколько вспомогательных столбцов. Идея состоит в том, чтобы перенести список, разделенный запятыми, в смежные ячейки, а затем применить поиск.