Опасаясь чужаков, Робинзон окружил своё жильё посадками и оценивал их уже десятками тысяч деревьев. На острове у него было где развернуться. Начнём с балкона: пока выберем между десятью и двадцатью горшками, чтобы за будущими зарослями по-прежнему было видно окно.
Допустим, сначала вам подарили один кустик базилика. Потом захотелось поставить рядом ещё что-нибудь зелёное, и вот вы уже присматриваете два размера горшков. Внутренний Робинзон открыл каталог и начал с того, что мысленно переименовал балкон в рощу. Перед походом за грунтом сравним четыре расчёта для будущих посадок. Для пробного плана возьмём расход 2 или 3 литра на один горшок, количество — 10 или 20. В каждой паре достаточно умножить количество на выбранный расход. Хочется увидеть сочетания рядом, сохранив одну исходную формулу.
Для этого в настольном Excel для Windows есть «Таблица данных» из набора «Анализ что если» — What-If Analysis / Data Table. Работать будем в уже установленной русской Microsoft 365 или Office 2024. Результатом станет сохранённая книга с четырьмя вариантами и понятными связями между ними.
Таблица подстановки в Excel начинается с одного обычного расчёта
В нашей модели меняются две величины: сколько горшков и сколько литров грунта закладываем на каждый. Ответ один — общий объём. Слева лежит список будущих горшков, справа — выбранный расход на каждый. Программа будет по очереди подставлять эти величины в ту же формулу, чтобы вам не пришлось стирать ответ после каждого варианта.
Сначала разберём одну пару. Потом разместим рядом ещё три сочетания этих же двух входов.
Откройте отдельную копию своей книги через «Файл → Сохранить как» или создайте новую. На свободном листе в A1 напишите «Горшков», в A2 — «Литров на горшок», в A3 — «Всего литров». В B1 введите 10, в B2 — 2. Нужны числовые значения без единиц измерения внутри этих клеток; подписи уже стоят слева.
В B3 введите формулу и нажмите Enter:
=B1*B2Должно получиться 20. Проверьте обычным умножением: десять горшков по два литра. Если на этом шаге ответ другой, сначала исправьте исходный расчёт. Следующий инструмент будет использовать именно эту формулу.
В справке Microsoft о таблицах данных такой подход описан как изменение входных величин с просмотром результатов формулы. Нам достаточно двух входов и четырёх сочетаний. Расход на горшок здесь выбран для сравнения вариантов; своё нужное количество грунта вы затем зададите по своим ёмкостям.
Разместим варианты вокруг будущих ответов
На том же листе подготовим небольшой квадрат E3:G5. В его левом верхнем углу будет ссылка на готовый результат, сверху — варианты количества горшков, слева — варианты расхода.
В E3 введите:
=$B$3Клетка покажет 20, потому что ссылается на B3. Важно, что внутри осталась ссылка на формулу, а не вручную вписанный ответ. Два знака$ удерживают адрес B3.
Справа в F3 введите 10, в G3 — 20. Под E3, в клетках E4 и E5, введите 2 и 3. Четыре клетки F4:G5 пока оставьте пустыми — там появятся ответы. Не добавляйте в них свои формулы умножения: заполним их через выбранный инструмент.
Схема интерфейса: в выделение входят ссылка, оба списка вариантов и четыре пустые клетки для результатов.
| Клетка | Что ввести | Зачем |
|---|---|---|
| E3 | =$B$3 | Ссылка на результат исходной модели |
| F3 и G3 | 10 и 20 | Количество горшков в разных вариантах |
| E4 и E5 | 2 и 3 | Литры на один горшок |
| F4:G5 | Пока ничего | Будущие результаты сочетаний |
Угол E3 нужен не для названия таблицы. Если заменить ссылку словом «Грунт» или обычным числом 20, инструмент потеряет подготовленную связь с вычислением B3. Заголовок своей задумки можно написать выше, за пределами E3:G5. Тогда у сетки будет и человеческое имя, и отдельный рабочий угол, который действительно связан с расчётом.
Свяжем строку и столбец с исходными клетками
Выделите весь диапазон E3:G5, начиная с E3. Откройте «Данные → Анализ что если → Таблица данных». В английском меню это Data → What-If Analysis → Data Table. Нужная команда относится именно к анализу вариантов известной формулы.
В окне задаются две входные клетки. В поле для подстановки значений строки — Row input cell — укажите $B$1. В верхней строке у нас 10 и 20 горшков, поэтому эти значения должны временно подставляться в клетку количества горшков.
В поле для подстановки значений столбца — Column input cell — укажите $B$2. Слева идут 2 и 3 литра на горшок; их подставляют в исходную клетку расхода. Нажмите ОК.
Здесь в поля вводятся адреса исходной модели. Сами варианты 10/20 и 2/3 уже стоят в подготовленном диапазоне. Поэтому строковый вход относится к B1, а не к F3; столбцовый — к B2, а не к E4. Связи удобно прочитать вслух до подтверждения окна.
Точный порядок для двух переменных приведён и в английском разделе Microsoft Create a two-variable data table. Расположение вариантов определяет, какой вход считается строковым и какой столбцовым.
Если после ОК ответов нет, сначала убедитесь, что выделяли все девять клеток E3:G5. Затем проверьте ссылку в углу E3 и оба адреса входов. Смысл этой проверки понятен по схеме: инструменту нужны формула, два набора значений и место, куда поставить их сочетания.
Прочитаем два пересечения и всю картину
В F4 должно появиться 20. Над этой клеткой стоит 10 горшков, слева — 2 литра на горшок. Сверяем: 10 ×2 =20 литров. В противоположном углу, G5, ожидаем 60: двадцать горшков по три литра.
На пересечении читаем одну комбинацию. Два противоположных угла проверим вручную: F4=20, G5=60.
| Расход на горшок | 10 горшков | 20 горшков |
|---|---|---|
| 2 л | 20 л | 40 л |
| 3 л | 30 л | 60 л |
Теперь можно сравнивать варианты по разным направлениям. Вдоль верхней строки меняется только количество горшков: при том же расходе общий объём растёт с 20 до 40 литров. Вдоль первого столбца меняется расход на один горшок: при тех же десяти горшках получается 20 или 30 литров. Кажется, что добавили всего литр на одну ёмкость, а для десяти таких ёмкостей понадобилось уже на десять литров больше. Сетка делает это следствие видимым.
Эта сетка помогает выбрать, какую комбинацию вы хотите рассмотреть дальше. Она рассчитывает заданное правило; подходящий объём для конкретных растений вы уже заложили во входных числах. Новую величину можно попробовать, изменив соответствующий вариант.
Есть тонкость нашего простого примера: при умножении порядок множителей не меняет ответ. Если перепутать входные поля B1 и B2, числа могут совпасть с ожидаемыми. Поэтому два верных результата дополните проверкой самих адресов: строка количества идёт в B1, столбец литров — в B2. Так сохраняется смысл модели, который пригодится при следующей своей формуле.
Изменим один вариант и увидим, что сетка связана с расчётом
В G3 временно замените 20 на 15 и нажмите Enter. В этом столбце теперь должны получиться 30 и 45 литров: пятнадцать горшков по два или по три литра. Левый столбец результатов останется 20 и 30, потому что вариант 10 горшков в F3 мы не трогали.
Верните в G3 число 20. Правый столбец должен снова показать 40 и 60. Так видно, что правый столбец связан со своим вариантом, а левый продолжает считать прежнее количество. Если бы вы вручную заполнили четыре ответа, новая партия из пятнадцати горшков потребовала бы возвращаться к каждому числу.
Если сетка не меняется, проверьте режим пересчёта: «Файл → Параметры → Формулы → Автоматически». В режиме, который исключает таблицы данных из автоматического расчёта, они могут ждать отдельного обновления. Microsoft также описывает ручной пересчёт: выделите формулы таблицы и нажмите F9.
Сами исходные B1 и B2 остаются моделью одной текущей пары, а верхняя строка и левый столбец задают варианты сравнения. Поэтому изменение только B1 не заменяет число в G3. Когда хотите изменить одну из сравниваемых комбинаций, найдите её значение среди заголовков сетки.
Помощник поможет не перепутать два входа
Если непривычно окно с двумя полями, откройте DeepSeek. Можно передать наш участок листа обычными словами и попросить один следующий шаг:
У меня настольный Excel. В B1 стоит 10 горшков, в B2 — 2 литра на горшок, B3=B1*B2. В E3 ссылка =$B$3, в F3/G3 числа 10/20, в E4/E5 числа 2/3. Хочу через «Анализ что если → Таблица данных» получить четыре результата. Сначала уточни устройство и выпуск Excel. Потом веди по одному действию: что выделить, какое поле заполнить и что должно появиться. Жди моего ответа. Объясни, почему для строки нужен B1, для столбца B2. После создания проверим F4=20 и G5=60, затем заменим G3 на 15 и сверим 30/45.
После одного действия назовите наблюдение: «Выделил E3:G5, открыл окно, вижу два поля» или «В E3 у меня просто написано 20, без знака равенства». Помощник сможет уточнить именно то место, где остановился результат. Обычный чат узнаёт устройство листа из вашего описания.
Если названия пунктов отличаются, перечислите видимые и назовите выпуск программы. При доступном поиске помощник сверит актуальную справку. Готовую формулу B3 и связь E3 можно сохранить: для разбора окна не требуется заново придумывать всю модель.
Попробуйте сами объяснить получившийся G5 одной фразой. «Шестьдесят» без единиц мало что говорит, а «двадцать горшков по три литра — всего шестьдесят литров грунта» уже связывает ответ с вашей задумкой. Именно так удобно проверить и следующую свою пару входов.
Частые вопросы
Зачем в E3 ссылка, если там и так видно 20?
Инструменту нужен связанный результат модели. Запись =$B$3 обращается к формуле B3, обычное число 20 остаётся постоянным ответом.
Что вводить в поле для строки?
Адрес B1: в верхней строке стоят варианты количества горшков. Значения 10 и 20 будут подставляться в эту исходную клетку.
Почему для столбца нужен B2?
Слева перечислены варианты расхода на один горшок. В нашей модели эта величина хранится в B2.
Два правильных угла доказывают, что поля не перепутаны?
Умножение допускает перестановку множителей. Поэтому проверьте и числа, и смысл адресов: B1 для количества, B2 для расхода.
Почему после изменения B1 варианты сверху остались прежними?
Числа в F3/G3 задаются отдельно для сравнения. Если хотите заменить сравниваемое количество, измените соответствующий заголовок сетки.
Сколько переменных можно сравнить таким способом?
В этой таблице используются два входа одной формулы. Для первого результата у нас два значения каждого входа — всего четыре сочетания.
Сохраним четыре варианта для возвращения к плану
Верните G3=20, если меняли его, и сохраните книгу как XLSX под понятным именем, например «Балкон-варианты». Закройте и снова откройте эту копию. Проверьте ссылку в E3, два контрольных пересечения и изменение одного варианта, если хотите убедиться в сохранённом пересчёте.
В следующий раз можно выбрать другие количества горшков или другие расходы на один. Для сравнения уже собранных фактических записей полезнее сводная таблица: она группирует имеющиеся данные. А если нужно по коду найти название из справочника, следующий отдельный инструмент — ВПР с точным совпадением.
В «Первопроходцах» можно продолжать со своим бытовым расчётом, таблицей или проектом: объяснить помощнику задумку, задать вопрос о незнакомом шаге и доработать результат с обратной связью. Начало уже предметное — четыре понятных сочетания вместо переписки о том, как бы всё посчитать.
Двадцать тысяч деревьев оставим Робинзону. У нас есть четыре варианта балконного сада и понимание, откуда берётся каждый объём. Можно выбрать свой и отправляться за грунтом с понятной записью. Базилик пока ничего не знает о будущей роще — ему ещё предстоит назначить себя главным деревом.