Мервин из Англии прислал эту задачу.
Есть ли в ячейке B2 единственная формула, которую можно копировать поперек и вниз для создания таблицы умножения?
Цель Мервина - ввести единую формулу в B2, которую можно легко скопировать во все 144 ячейки, чтобы создать справочную таблицу умножения.
Давайте атакуем это как новичок в Excel и посмотрим, на какие ловушки мы наткнемся. Первой реакцией может быть формула в B2 =A2*B1. Эта формула дает правильный результат, но не подходит для задания Мервина.
Когда вы копируете эту формулу из ячейки B2 в ячейку C2, ячейки, указанные в формуле, также перемещаются на одну ячейку. =A2*B1Теперь делается формула, которая была =C1*B2. Эта функция разработана в Microsoft Excel и называется ссылкой на относительную формулу. При копировании формулы ссылки на ячейки в формуле также перемещают соответствующее количество ячеек поперек и вниз.
Бывают случаи, когда вы не хотите, чтобы Excel демонстрировал такое поведение, и текущий случай является одним из них. Чтобы Excel не изменил ссылку при копировании ячеек, вставьте знак «$» перед той частью ссылки, которую вы хотите зафиксировать. Примеры:
- $ A1 сообщает Excel, что вы всегда хотите обращаться к столбцу A.
- B $ 1 сообщает Excel, что вы всегда хотите ссылаться на строку 1.
- $ B $ 1 сообщает Excel, что вы всегда хотите ссылаться на ячейку B1.
Изучение этого кода значительно сократит время, необходимое для создания рабочих листов. Вместо того, чтобы вводить 144 формулы, Мервин может использовать это сокращение, чтобы ввести одну формулу и скопировать ее во все ячейки.
Глядя на формулу Мервина, =B1*A2мы понимаем, что часть выражения «B1» всегда должна указывать на число в строке 1. Это следует переписать как «B $ 1». Раздел формулы «A2» всегда должен указывать на число в столбце A. Его следует переписать как «$ A2». Итак, новая формула в ячейке B2 =B$1*$A2.
После ввода формулы в B2 я могу скопировать и вставить ее в соответствующий диапазон. Excel следует моим инструкциям, и после копирования я нахожу следующие формулы:
- Ячейка M2 содержит
=M$1*$A2 - Ячейка B13 содержит
=B$1*$A13 - Ячейка M13 содержит
=M$1*$A13
У каждого из этих типов формул есть имя. Формулы без знаков доллара называются относительными формулами. Формулы, в которых строка и столбец заблокированы знаком доллара, называются абсолютными формулами. Формулы, в которых строка или столбец заблокированы знаком доллара, называются смешанными формулами.
Существует сокращенный метод ввода знаков доллара. Когда вы вводите формулу и заканчиваете ссылку на ячейку, вы можете нажимать клавишу для переключения между 4 типами ссылок. Допустим, вы начали вводить формулу и набрали =100*G87.
- Нажмите F4, и ваша формула изменится на
=100*$G$87 - Еще раз нажмите F4, и ваша формула изменится на
=100*G$87 - Еще раз нажмите F4, и ваша формула изменится на
=100*$G87 - Еще раз нажмите F4, и ваша формула вернется к исходному состоянию.
=100*G87
Вы можете приостанавливать ввод формулы при каждой ссылке на ячейку и нажимать F4, пока не получите правильный тип ссылки. Нажатия клавиш , чтобы ввести формулу Merwyn в выше =B1*A1.
Одно из моих любимых применений ссылок на смешанные формулы возникает, когда у меня есть длинный список записей в столбце A. Когда мне нужен быстрый и грязный метод поиска дубликатов, я иногда ввожу эту формулу в ячейку B4, а затем копирую ее:
=VLOOKUP(A4,$A$2:$A3,1,FALSE)
Обратите внимание, что второй член формулы ВПР использует как абсолютную ($ A $ 2), так и смешанную ($ A3) ссылку для описания диапазона поиска. На английском языке я говорю Excel, чтобы всегда искать от ячейки $ A $ 2 до ячейки в столбце A чуть выше текущей ячейки. Как только Excel обнаруживает повторяющееся значение, результат этой формулы изменяется с # N / A! к значению.
Благодаря творческому использованию ссылок на ячейки $ in вы можете быть уверены, что одну формулу можно скопировать во многие ячейки с правильными результатами.
