У меня есть следующий запрос:
Моя таблица ввода
В основном я хочу искать столбцы для каждого идентификатора. Value3> Value2> Значение1
Если в столбце value3 и value есть данные, то на выходе должен быть указан идентификатор, имеющий значение3
Если значение value3 содержит данные, то заполняет вывод как Value3, если value2 и value3 имеют данные, то также должно заполнять value3
Выход:
Есть ли формула, которую я могу написать, чтобы получить это значение?
Если это всего три столбца значений, простой способ сделать это - IF тесты. Скажем, таблица, которую вы показываете, начинается с заголовка A1 (A1 = "ID"). Формула для первого выходного значения будет иметь вид:
=IF(ISBLANK(D2),IF(ISBLANK(C2),$B$1,$C$1),$D$1)
Скопируйте это вниз в выходной столбец. (Это предполагает, что каждая строка в таблице данных представлена строкой в выходной таблице, и обе имеют столбец идентификатора в том же порядке.)
Вот более общее решение. Я предполагаю, что у вас есть пять столбцов «значения» (от B до F), и вы хотите получить результат в столбце G Введите эту формулу в ячейку G2:
=OFFSET($B$1, 0, MAX(COLUMN(B2:F2)*NOT(ISBLANK(B2:F2)))-2)
и введите Ctrl+Shift+Enter. Это делает его «формулой массива»; он будет отображаться в строке формул с фигурными скобками ({ … }) вокруг него.
Теперь перетащите его на столько строк, сколько хотите:
ISBLANK() проверяет, является ли ячейка пустой (TRUE если она есть, FALSE если это не так).NOT(…) инвертирует логику; теперь у нас есть TRUE если в ячейке есть (непустые) данные, и FALSE если у нее нет данных. TRUE - фактически значение 1 а FALSE - фактически 0 .COLUMN(B2:F2)*(the above) берет номер столбца каждой ячейки в диапазоне и умножает его на 1, если ячейка имеет данные, и 0, если нет.
Это дает номер столбца (× 1), если в ячейке есть данные, и 0 (номер столбца × 0), если его нет.
Например, для строки 3 это {2,0,4,0,0}, поскольку во втором (B) и четвертом (D) столбцах есть данные.MAX(…) выбирает самый большой из них; т.е. номер самого правого столбца, где есть данные.=OFFSET($B$1, 0, (the above)-2) находит ячейку, которая на 0 строк ниже и MAX(COLUMN(B2:F2)*NOT(ISBLANK(B2:F2)))-2 столбца справа ячейки B1 .
Мы вычитаем 2 из номера столбца, потому что B1 находится в столбце 2, поэтому, когда значение MAX(COLUMN(B2:F2)*NOT(ISBLANK(B2:F2))) равно 2 (как в строке 2), мы хотим перейти 0 столбцов справа от ячейки B1 .