У меня есть электронная таблица, которая вычисляет потребность батареи в постоянном токе. Я хочу иметь возможность найти подходящую батарею из установленной продолжительности нагрузки, которая имеет постоянный ток больше или равный моему расчетному значению.
Я пытаюсь использовать таблицу поиска, где моя "Длительность загрузки" (желтая) выбирает столбец из массива, а затем моя функция поиска (зеленая) находит значение >= моему вычисленному опорному значению (синяя). Мне также нужна другая функция, которая находила бы модель из той же строки, что и найденное значение из массива.
Load Duration — это выпадающее меню, которое соответствует соответствующим столбцам. В настоящее время у меня есть ячейка, которая выбирает столбец массива:
=MATCH(C39,'Battery Lookup Table'!B2:O2,0)
Я перепробовал несколько функций поиска, чтобы найти ближайшие значения постоянного тока:
=VLOOKUP(C46,'Battery Lookup Table'!B4:O36,MATCH(C39,'Battery Lookup Table'!B2:O2,0),TRUE)
=VLOOKUP(C46,'Battery Lookup Table'!B4:O36,N49)
=(HLOOKUP(C46,'Battery Lookup Table'!S56:AW69,O50,TRUE))
Каждая из этих таблиц поиска выдала одно и то же значение.
Я также пробовал сортировать значения от наименьшего к наибольшему, так как VLOOKUP и HLOOkUP с трудом справляются с расшифровкой. Я также пробовал менять строки на длительность времени, но безуспешно.
Номер модели:=INDEX('Battery Lookup Table'!A4:A36,MATCH(TRUE,'Battery Lookup Table'!M4:M25>=C46,0))
В настоящее время моя функция поиска переходит к правильному столбцу, но чаще всего использует строку/значение в нижней части таблицы. Ни одно из найденных значений не было самым близким значением.
решение1
VLOOKUPсопоставляет значение спервыйстолбец диапазона и выдает значение из указанного столбца диапазона в совпадающей строке. Таким образом, ваша первая формула
=VLOOKUP(C46,'Battery Lookup Table'!B4:O36,MATCH(C39,'Battery Lookup Table'!B2:O2,0),TRUE)
соответствует C46(я думаю, что это 500,8 в вашем примере) значениям в диапазоне 'Battery Lookup Table'!B4:B36. Это не тот диапазон, с которым вы хотите сопоставлять, но он, вероятно, объясняет, почему вы получаете неверные результаты.
На самом деле, вы уже определили правильный столбец для сопоставления с вашей MATCHфункцией. Вы можете использовать INDEXфункцию для выбора части диапазона. Выражение
INDEX('Battery Lookup Table'!B4:O36,,n)
обеспечиваетн'th столбец 'Battery Lookup Table'!B4:O36так что поместите вашу MATCHфункцию на местонпредоставляет правильный столбец значений, с которыми C46можно сравнивать. Это дает выражение:
INDEX('Battery Lookup Table'!B4:O36,,MATCH(C39,'Battery Lookup Table'!B2:O2,0))
Для простоты восприятия я сокращу это выражение следующим образом:список.
Теперь вам нужно сделать 2 вещи:
- Найдите наименьшее значение всписокчто больше или равно
C46 - Найдите аккумулятор, соответствующий этому значению.
Здесь трюк не в использовании , VLOOKUP()а в использовании его более гибкого "двоюродного брата" INDEX(MATCH()). В то время как, VLOOKUPсопоставляет значение в первом столбце диапазона и выдает значение из столбца справа, более гибкая версия позволяет значению поступать либо из того же столбца, либо из столбца слева.
Кроме того, существует 3 возможных способа сопоставления с использованием MATCH, в зависимости от того, является ли значение третьего аргумента -1,0 или 1. Самый простой из них - точное сопоставление, которое использует значение 0 (как вы уже делали при сопоставлении с Battery Lookup Table'!B2:O2). Значение -1 находит позицию наименьшего значения в диапазоне поиска, которое больше или равно значению поиска. Это ваше требование, но оно требует, чтобы диапазон поиска был помещен внисходящийпорядок. (Значение 1 находит позицию наибольшего значения в диапазоне поиска, которое меньше или равно значению поиска, и требует, чтобы диапазон поиска был в порядке возрастания — это не ваше требование.)
Значения в вашем Battery Lookup Tableall, по-видимому, находятся в порядке возрастания, поэтому реорганизация ваших данных таким образом, чтобы строки отображались в обратном порядке, будет соответствовать условию убывания - 100G99 будет первой строкой данных, а 50G05 - последней. Вам также необходимо удалить пустые строки, поскольку значения в них будут рассматриваться как нулевые, поэтому потенциально могут вызвать проблемы из-за нарушения требования убывания.
Выражение
MATCH(C46,list,-1))
обеспечивает положение всписокзначения, которое вы ищете, и его фактическое значение (для отображения в вашей зеленой фоновой ячейке) предоставляется
=INDEX(list,MATCH(C46,list,-1))
Модель батареи предоставлена
=INDEX('Battery Lookup Table'!A4:A46,MATCH(C46,list,-1))
Подставляя выражение длясписок(отмечено выше) в эти две формулы приводит к громоздким выражениям с некоторой степенью повторения в первой и повторным вычислением в MATCH(C46,list,-1)обеих. Эти повторения не являются хорошей практикой и должны, по возможности, избегаться.
Один из способов сделать это — сохранить в ячейках листа значения соответствующих номеров столбцов и строк. Соответственно, это:
=MATCH(C39,'Battery Lookup Table'!B2:O2,0)- обозначить черезн, и
=MATCH(C46, INDEX('Battery Lookup Table'!B4:B36,,n),-1)- обозначить черезм
и результаты поиска затем
INDEX('Battery Lookup Table'!B4:O36,m,n)(постоянная мощность при требуемой продолжительности нагрузки) и
INDEX('Battery Lookup Table'!A4:A36,m) (соответствующий номер модели аккумулятора)
гденим— это две ссылки на ячейки, содержащие вычисленные номера столбцов и строк.