Расчет Excel на основе ввода пользователя из выпадающих полей

Question

Мы используем Excel 2003, но можем обновить при необходимости. Нам нужно вычислить (или найти) значение на основе двух входных данных. Оба входа будут выпадающими окнами Excel.

Первый из них будет иметь значения 6 , 9 , 12 , 16 , 20 . Второй выпадающий имеет 6x6 , 10x10 , 20x20 .

Как мы можем найти соответствующее значение и поместить результат в отдельную ячейку?

Answer 1

Это не должно быть достаточно сложным. Возьмем для примера следующее.

1. Выберите заголовки столбцов и назовите их Input1. (Диапазон E1:I1 в примере.)
2. Выберите заголовки строк и назовите их Input2. (Диапазон D2:D4)
3. Выберите ваши данные и назовите их Data. (Диапазон E2:I4)
4. Используйте проверку данных, чтобы получить выпадающие списки.
   • Выберите ячейку B2 и нажмите Данные - Проверка данных.
   • Установите критерии проверки Разрешить для List и источник =Input1
   • Делайте все, что вы хотите для входящего сообщения и предупреждения об ошибках. (Я выключил их.)
   • Повторите для вашего второго ввода. (ячейка B3 получает источник =Input2)
5. В ячейке Result введите следующую формулу =INDEX(Data,MATCH(B3,Input2,0),MATCH(B2,Input1,0))

Формула работает следующим образом:

MATCH(lookup_value, lookup_array, [match_type])

MATCH является своего рода похожа на VLOOKUP за исключением того, что вместо возвращения / значение ячейки сота , которую он возвращает относительную позицию lookup_value в lookup_array , . С его помощью на диапазоне Input1 возвращает относительную позицию столбца при использовании его на Input2 возвращает относительную строку. Если для match_type значение 0, Excel будет возвращать только точное совпадение.

INDEX(reference, row_num, [column_num], [area_num])

INDEX возвращает ячейку в Data найденную в ссылочной строке Input2 и ссылочной колонке в Input1 .

Примечание. В Excel 2010 (я не уверен в более ранних версиях) таблица данных даже не должна находиться на том же рабочем листе, что и ячейки ввода и результата.

Answer 2

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

• Таблица пользовательских данных: это может быть тот же формат, который у вас уже есть, с размерами слева и типом / размерами вверху, и соответствующими данными, куда он идет.

• Данные формы и индексы . Поскольку на листе Excel есть поля со списком, то есть элементы управления формой, необходимо правильно связать эту информацию. Чтобы сделать это, проверьте раздел связывания этого ответа внизу; но достаточно сказать, что вы должны указать форме, какие измерения и какие у вас типы, чтобы они отображались в комбинированных полях. Вам также нужно указать в листе Excel, какие индексы имеют какое значение, поскольку поля со списком не возвращают никаких данных, кроме фактического индекса.

• Результаты и формула: пользователь может выбрать нужные размеры и типы, и вы можете увидеть, что это за данные; результат также вычисляется по формуле, перечисленной там. Из данных видно, как составляется формула. Вам нужно будет настроить диапазоны и местоположения, основываясь на нашем листе и месте, где вы разместили все, но именно поэтому я дал вам раздел разбивки .

---

Добавление ComboBox в Excel:

• Нажмите Developer вверху или используйте меню Developer в более ранних версиях:

Если у вас нет вкладки « Разработчик » или меню вверху, отредактируйте свои предпочтения, обычно в разделе «Дополнительно», и выберите или включите параметр « Разработчик» , чтобы у вас появилось меню. Вы можете проверить эту статью здесь, для более подробной информации: http://office.microsoft.com/en-us/excel-help/show-the-developer-tab-or-run-in-developer-mode-HA010173052.aspx

• Нажмите Вставить, а затем выберите поле со списком:

Это добавит фактическое поле со списком к вашему листу Excel, обычно, если вы не нарисуете нужный размер, вам предоставляется один большой блок:

Вы можете изменить размер поля со списком, щелкнув по нему правой кнопкой мыши и перетащив края по мере необходимости.

• Щелкните правой кнопкой мыши новое поле со списком и выберите « Управление форматами»:

• Настройте параметры так, как вам нужно:
  • Диапазон ввода: это должен быть вертикальный список всех параметров, которые должны быть в поле со списком
  • Ссылка на ячейку: здесь вы хотите, чтобы возвращался индексный результат.

• Результаты будут возвращены при работе со списком:

• Чтобы привязать индекс к значению: просто укажите число индексов после параметров в поле со списком и выполните простой поиск: