Выпадающий список Excel, созданный из столбца A: номер и столбец B: элемент, затем сокращаются параметры

Question

Я хотел бы перечислить все фрукты в моей корзине, затем подарить фрукты моим друзьям, и параметры в раскрывающемся списке автоматически сокращаются.

Колонка A: количество фруктов Колонка B: фрукты Колонка C: имена Колонка D: раскрывающийся список

Например, у меня есть 1 анана, 0 яблок и 2 апельсина. Когда я даю фрукт одному своему первому другу, из выпадающего списка можно выбрать "ананас"; "оранжевый"; "оранжевый".

Если я даю ананас одному из моих друзей, раскрывающийся список становится "оранжевым", "оранжевым" для других и так далее.

Можно ли это сделать с помощью Excel? Если так, возможно ли это без VBA, с проверкой данных и регулярными формулами?

Спасибо.

Answer 1

Это возможно, но включает в себя несколько шагов.

Я предполагаю, что количество фруктов в A1:A5, названия фруктов B1:B5, имена друзей C1:C5 и раскрывающийся список корзины находится в D1:D5, я использую F1:F5 для расчета списка, и G1:G15 будет держать список корзины для выпадающего списка.

1. Рассчитайте, в каком ряду каждый фрукт должен начинаться в списке корзин, в зависимости от количества и использования корзины:
   • F1 =A1-COUNTIF($D$1:$D$5,B1),
   • F2 =A2+F1-COUNTIF($D$1:$D$5,B2),
   • Скопируйте F2 в F3:F5.
2. Создайте список корзин для выпадающего списка:
   • G1: G15 =INDIRECT("B"&COUNTIF($F$1:$F$5,"<"&ROW())+1),
3. Создать список проверки данных для D1:D5:
   • В списке источников поставить =OFFSET($G$1:$G$15,0,0,COUNTIF($G$1:$G$15,"<>0")), Я предполагаю, что ячейки под строкой 5 в названиях фруктов будут пустыми, поэтому остальные строки в списке получат значение 0 , следовательно "<>0" в списке кол.

Теперь, когда вы выбираете фрукт из списка проверки, список будет обновляться соответствующим образом.