Одна только длина этого ответа заставит это казаться ужасным, но я просто многословен ... Я верю, что немного (хорошо, много) подробностей очень помогает, и рассказ о том, почему вы делаете что-то, делает его более доступным для вас и более пригодным для использования в других приложениях. Это просто. Я сделал это за пять минут на работе с боссом, разговаривающим со мной. Это просто. Так терпеть?
Вы можете сделать это без особых проблем или даже каких-либо проблем, если:
1) У вас есть доступ к функции CONCAT().
2) Вы соглашаетесь с тем, что информация о названии пищи / калории должна быть введена каким-либо образом, либо в справочную таблицу, как это делает большинство людей, либо в виде именованных диапазонов и их значений. Ни один из видов печатания не является более неприятным, чем другой, так почему бы и нет?
3) Ваш диапазон ячеек для ввода продуктов в не огромный. Под этим я подразумеваю не более 800 предметов. Даже это действительно может быть расширено, но это своего рода "многослойные вещи", и это иногда неприятно в Excel. Я представляю ограниченную потребность в поступлении "в этот день" или стоимость недели, исходя из моих ощущений по поводу вашего описания, но ... трудно, если это предназначено, чтобы работать вечно, или для группы людей для неделю или что у тебя. Хотя выполнимо, не поймите меня неправильно, но я продолжу использовать предположение, что в диапазоне, который вы ожидаете, может быть 25 или 200 ячеек.
Итак ... каждая еда должна быть указана как именованный диапазон, без пробелов или неестественных символов в именах. Например:
PorkChop GreenBeans Макароны и Сыр
но НЕ "Pork_Chop" и так далее. Просто отбросьте пробелы, это идея.
Для области "Относится к" вместо обычной формулы или ссылки на ячейку (например, «= Sheet1!»).A5 "), просто укажите значение калории: = 180 для свиной отбивной, возможно.
Теперь для построения формулы вам нужно:
Вам нужно использовать INDIRECT(), чтобы превратить название продукта в полезную ссылку для Excel. Например:
=INDIRECT(PorkChop)
превратит "PorkChop" в PorkChop (именованный диапазон), найдет его в ваших NR и вернет введенное вами значение калории.
К сожалению, хотя я клянусь, что он сделал это правильно для меня, но не сейчас, используя INDIRECT(), вот так:
=INDIRECT(K10:K13)
возвращает единственное значение, используя "верхний левый угол" этого диапазона, K10. (Я буду использовать K10:K13 для вашей зоны входа в примере здесь.) Возможно, я добился этого с помощью прямых ссылок на ячейки в клетках-мишенях (как в K10: у K13 были значения, а не NR), и в этом была разница. Во всяком случае, CONCAT () делает это не так важно.
Таким образом, вы должны вводить каждый НЕПРЯМОЙ отдельно, а не сладкий и легкий диапазон, как указано выше. Это будет ОЧЕНЬ МНОГО печатать для диапазона входа в 500 ячеек! Но CONCAT() решит это.
Хорошо. Как бороться с тем, что ваши пользователи вводят пробелы в названия продуктов, которые они вводят? Проблема № 2, а? Оберните каждый NR в SUBSTITUTE() перед передачей его в INDIRECT():
=INDIRECT(SUBSTITUTE(K10," ",""))
и это было бы НЕДОРОГОЙ объем работы без CONCAT().
Итак, для построения формулы суммирования мы создадим текстовую строку для каждой ячейки в диапазоне ввода и добавим значение ROW(), чтобы сделать их уникальными. Давайте изобразим здесь диапазон K10:K13, хотя вы могли бы иметь гораздо больше строк и использовать несколько столбцов, используя следующие приемы.
Во-первых, базовая строка. Выберите ячейку в строке 10 (соответствует строке первой ячейки, K10, чтобы упростить вам задачу), которая находится в стороне. Мой пример настроен на G10, но вы действительно хотите что-то справа от столбца K. Просто скажите. Введите в ячейку G10 следующее:
="INDIRECT(SUBSTITUTE(K"&ROW()&","&""" "","""")),"
(Обратите внимание, как вы должны использовать два "символа" для каждого из них, необходимых для "реальной" функции SUBSTITUTE() ... это текстовая строка, которая будет передана как настоящий мальчик, я имею в виду формулу, при которой Точка Excel обрежет одну из каждой пары из них ... это аспект того, что я называю "наслоением" и как это получается ... иногда смешно.)
Один вошел, скопируйте ячейку и вставьте ее в G11, G12 и G13. Или двести ячеек, если ваша зона входа имеет глубину 200 ячеек. Но эти четыре пока.
Затем перейдите к последней, G13, и отредактируйте ее, чтобы удалить последнюю запятую, чтобы она заканчивалась:
))" instead of )),"
потому что это будет конец вашей строки в функции CONCAT(), и вы не можете завершить ее запятой!
Хорошая сделка. Теперь перейдите в другую ячейку, возможно, G15 и введите:
=CONCAT(G10:G13)
и после нажатия клавиши Enter скопируйте ячейку в буфер обмена, затем перейдите в еще одну ячейку, возможно, G16, и вставьте | Специальные | Значения, чтобы наконец создать текстовую строку, необходимую для функции SUM().
Затем нажмите F2, чтобы отредактировать содержимое ячейки и скопировать все содержимое в буфер обмена. Делая это изнутри, ячейка получает буквальный текст и ничего больше. Копирование самой ячейки было бы бесполезным!
Перейдите в ячейку, в которой вы хотите получить сумму калорий, и наберите "= SUM(", затем вставьте текстовую строку, затем нажмите Enter и позвольте Excel добавить закрывающее значение ")". Это будет выглядеть так:
=SUM(INDIRECT(SUBSTITUTE(K10," ","")),INDIRECT(SUBSTITUTE(K11," ","")),INDIRECT(SUBSTITUTE(K12," ","")),INDIRECT(SUBSTITUTE(K13," ","")))
Готово.
Все пробелы, которые пользователи вводят в названия продуктов, удаляются, чтобы они соответствовали номерам NR. Они видят свои записи с названиями продуктов в клетках, несмотря ни на что. Тем не менее, ваша ячейка суммирования получает числа, необходимые для фактического суммирования.
Теперь пользователи по-прежнему будут нарушать его, но эти нарушения (о, неправильное написание чего-либо, опечатки, новое название еды, неправильные имена, вы поймете, что) ВСЕ также нарушат подход таблицы поиска. Все они заставят суммирующую ячейку отображать ошибку, чтобы кто-то (вы ...) знал, что нужно искать ошибки / с.
Кстати, если вы используете несколько столбцов, а не тот, который я использовал, просто введите строку для первого столбца, затем отредактируйте первую ячейку, чтобы настроить следующий столбец, и так далее. Каждый раз вставляйте материал дополнительной текстовой строки в конце предыдущего вставления в формулу суммирования.
И если вы достигнете предела длины символа в Excel (помните, я говорил, что диапазон ввода был ограничен, возможно, 800 ячейками?) вы можете создать NR с именем, возможно, "Sum1" и поместить в него первый пакет, затем другой ("Sum2") для второго и т. д., пока не будет учтен весь диапазон входных данных и для ячейки суммирования вы не захотите использовать:
=Sum1 + Sum2 + Sum3
или что есть у тебя. Хотя "слоистая" вещь ... Длина символа в формуле Excel в именованном диапазоне может быть меньше, чем в реальной ячейке, поэтому она может быть сложной. Но это принцип, и вам просто нужно поэкспериментировать.
Но опять же, при простой и быстрой работе у вас есть только названия продуктов в диапазоне ввода данных и все значения калорий вне поля зрения. Это было бы волшебством для них.
