#excel #if-statement #excel-formula
#excel #if-statement #excel-формула
Вопрос:
У меня есть лист1, который содержит следующий набор данных A
ColumnA: ColumnB: ColumnC: ColumnD:
Row1: June 1 June 2 June 3
Row2:
Row3: A Apple Apple Orange
Row4: B Orange Orange Orange
Row5: C Orange Apple Orange
Row6: D Pear Apple Pear
На этом листе у меня есть следующая таблица
Consumed fruits
**June 1** **June 2** **June 3**
Apple Orange Pear Apple Orange Pear Apple Orange Pear
A True True True
B True True True
C True True True
D True True True
Где для каждой ячейки я добавляю следующий код, относящийся к DataSet
Для 1 июня, A, Apple
=IF(B3="Apple","True","")
1 июня, A, оранжевый
=IF(B3="Orange","True","")
1 июня, A, Pear
=IF(B3="Pear","True","")
Но когда я перетаскиваю формулу на следующую дату (2 июня, A, Apple), она меняется на
=IF(B6="Apple","True","")
Вместо
=IF(B4="Apple","True","")
Можно ли в любом случае сохранить ячейки запроса постоянными для 3 формул, прежде чем увеличивать только одну ячейку запроса в следующих 3 формулах?
Комментарии:
1. В небольшой базе данных вы можете использовать КОСВЕННЫЙ (ADDRESS()). Но это изменчивая функция, которая замедлит работу вашей книги. Следовательно, не рекомендуется для более крупной книги. Энергонезависимое решение будет включать использование INDEX() . Если ваши данные находятся в реальной таблице, диапазон, подлежащий индексации, уже существует. В противном случае вам пришлось бы ее настроить, и для этой цели вы бы использовали динамический именованный диапазон. Ваши данные в таблице? Знаете ли вы, как настроить динамический именованный диапазон?
2. Привет, Вариатус. Спасибо вам за ваш ответ. Да, мои данные находятся в таблице. Лист1 содержит dataA, и я надеялся создать таблицу в Листе2, которая содержала бы все данные в Листе1. Нет, я не знаю, как настроить динамический диапазон — я знаю только базовые формулы Excel — так что любой ввод очень ценится!
Ответ №1:
Вот формула. Это в B11 на скриншоте ниже и скопировано оттуда в B11: J14.
=IF(INDEX(Table1,MATCH($A11,Table1[Column1],0),MATCH(TEXT(INDEX($9:$9,0,COLUMN()-MOD(COLUMN()-2,3)),"mmm-dd"),Table1[#Headers],0))=B$10,TRUE,"")
Формулы в B16: B20 являются компонентами окончательной формулы. Я использовал их для тестирования. Они могут помочь вам понять, что делает формула. Вот еще одна помощь в этом направлении.
Основной синтаксис функции INDEX() таков INDEX([Range address], [Row number], [Column number]) . Диапазон может быть именованным диапазоном, например «Table1». Номера строк и столбцов могут быть жесткими числами, но предоставляются функциями MATCH() в моей формуле.
Excel хранит дату, например Jun-01 , в виде числа, считая дни с 30 декабря 1899 года. Однако в заголовках таблицы Excel преобразовал даты в строки. Поэтому значение ячейки B1 равно «Jun-01», именно то, что вы видите, но значение ячейки B9 равно 37043, что является числом для 01 июня 2001 года. Год был предоставлен Excel, потому что я не вводил ни одного года (я ожидал, что он предоставит текущий 2020 год, но здесь это не имеет значения). Чтобы найти дату в заголовках таблицы, реальная дата в B9 преобразуется в строку, в TEXT(B9,"mmm-dd") которой год не учитывается. Эта строка используется функцией СОПОСТАВЛЕНИЯ для поиска правильного столбца таблицы, используя значения в строке 9 для примеров. Конечно, если у вас нет реальных дат в вашем эквиваленте строки 9, но строки, моя формула не будет работать, потому что преобразование не требуется. Извлеките его.
Итак, строка 9 важна для моей формулы. Найдите $9:$9 и измените эту ссылку на строку, в которой у вас есть даты в вашей выходной таблице (A9: J14 на моем рисунке). Если ваша выходная таблица является реальной таблицей, ссылка на лист будет работать, но вы можете захотеть изменить ее на что-то вроде Table2[#Headers] . Если вы сделаете это, также может потребоваться некоторая корректировка столбцов.
Поместите формулу =MOD(COLUMN()-2,3) в пустую строку и скопируйте вправо. если вы вставили оригинал в столбец B, это приведет к серии повторений 0, 1 и 2. Они используются, чтобы всегда ссылаться на первый столбец набора из 3, например, COLUMN()-0, COLUMN()-1 и COLUMN() -2. В этой формуле COLUMN()-2 это относится к столбцу листа B. Формула выдает 0 в столбце B. Измените это число так, чтобы формула выдавала ноль в столбце, в котором у вас есть дата. Протестируйте этот компонент и внесите изменения в большую формулу после того, как узнаете, что она работает так, как должна. Обратите внимание, что если дата находится в объединенных ячейках, Excel сохраняет ее в первой ячейке объединенной области. Остальные ячейки пусты, насколько это касается Excel.
Я надеюсь, что вы сможете взять на себя управление этой formnula. Удачи.
Комментарии:
1. Привет, Вариатус, спасибо за подробное объяснение! Это действительно хорошо и удобно. Я поиграл с различными частями формулы, чтобы посмотреть, получится ли у меня. Я сталкиваюсь с проблемой, когда я пытаюсь перетащить ее, эта часть
MATCH($A11,Table1[Column1],0)становитсяMATCH($A11,Table1[Jun-01],0), и она создает # N / A в ячейке
Ответ №2:
Или,
В B11 скопировано справа налево J11 и все скопировано вниз :
=IF(INDEX($B$3:$D$6,MATCH($A11,$A$3:$A$6,0),MATCH(LOOKUP(9^9,$B$9:B$9),$B$1:$D$1,0))=B$10,TRUE,"")
Комментарии:
1. Привет, bosco_yip! Это тоже сработало отлично! Спасибо!