Как уменьшить строку формул в excel
В процессе работы с Microsoft Excel у пользователей возникает необходимость внесения дополнительных данных. Для этого необходимо добавлять или копировать строки с формулами.
Кажется, это простая и понятная процедура. Но часто возникает вопрос о том, как добавить новую строку с формулами в Excel с определенными условиями. Например, без изменения адресов в формулах с относительными ссылками на ячейки и т.п. На готовых примерах рассмотрим оригинальные решения этих задач.
Как добавить новую строку с формулами в Excel?
Пускай мы добавили формулы в одну строку или в несколько. Теперь нам нужно скопировать все строки с формулами. Как это сделать при добавлении новой строки?
- Выделяем ту строку, которая содержит формулы. В нашей ситуации это строка под номером 3. Нам необходимо, чтобы остальные строки содержали в себе такие же формулы.
- Нажимаем на выделенную область правой кнопкой мышки. Из контекстного меню нужно выбрать опцию «Копировать» (или жмем CTRL+C).
- После этого следует выделить следующую одну (или несколько) нижнюю строку, куда будет вставлена новая с формулами.
- Щелкнуть по заголовку выделенной строки правой кнопкой мышки и выбрать опцию вставить скопированные ячейки (или нажать комбинацию клавиш CTRL+SHIFT+«=»).
Заметьте, значения ячеек не заменились а добавилась в середину таблицы новая строка со старыми значениями и формулами.
Как копировать без изменения формул в Excel?
Часто при копировании относительные формулы (т.е. те, в которых нет знака «$») «подставляют» другие значения. А что делать, если вы не хотите изменять значение формул?
Можно сделать все ссылки в формулах абсолютными. Такие формулы будут копироваться неизменно, без изменения данных. Чтобы сделать ссылки абсолютными, добавляем к ним знак «$».
Ставим перед буквами и перед числами знак доллара – и получаем абсолютные ссылки в формулы, значения в которых не будут изменяться при копировании.
На пример в такой таблице ссылка на наценку ($B$1) не будет изменятся при копировании на новых строках, так как она является абсолютной, а не относительной:
Но в некоторых больших и сложных таблицах слишком много формул, которые нужно скопировать и переместить. И чтобы во всех ссылках проставить знак «$» нужно потратить много времени.
В результате при копировании во всех строках ссылки формул остались неизменными.
Ссылка — ячейка или интервал ячеек, для которых определяется номер строки.
Если ссылка опущена, то функция СТРОКА() вернет номер строки в которую введена сама формула. Т.е. если формула =СТРОКА() введена в ячейку B5 , то результат будет 5.
В аргументе «ссылка» нельзя указывать несколько областей, т.е. запись =СТРОКА(A1:A2;B2:B3) не допустима.
Использование функции в формулах массива
Так как функция СТРОКА() в качестве аргумента позволяет ввести ссылку на диапазон ячеек, то следует ожидать, что она вернет не одно число, а массив целых чисел - соответствующих номеров строк. Для этого введите в Строке формул выражение =СТРОКА(F8:F18) . Выделите формулу и нажмите F9 (см. файл примера ).
Элементы этого массива можно вывести в отдельные ячейки. Для этого выделите диапазон A5:A18 , состоящий из 13 ячеек, в Строке формул введите выражение =СТРОКА(F8:F18) как формулу массива, нажав CTRL+SHIFT+ENTER .
Чтобы вывести номера строк в горизонтальном диапазоне используйте формулу =ТРАНСП(СТРОКА(F8:F18)) . Не забудьте нажать CTRL+SHIFT+ENTER .
Использование функции для чрезстрочного выделения
Записав в правиле Условного форматирования для диапазона A5:A18 формулу =НЕЧЁТ(СТРОКА())=СТРОКА() получим выделение цветом каждой второй строки (см. рисунок выше).
Формирование списка без повторов
Если список значений содержит повторяющиеся значения (диапазон A21:A34 ), то функция СТРОКА() может быть использована для формирования списка без повторов. Формула =ЕСЛИ(ПОИСКПОЗ(A21;$A$21:$A$34;0)=СТРОКА()-СТРОКА($A$20);A21;"") возвращает значение если оно встречается первый раз.
В некоторых случаях вы не хотите отображать содержимое ячейки, например формулы, в строке формул. В этой статье мы покажем вам два метода скрытия содержимого строки формул в Excel.
Скрыть содержимое строки формул с помощью функции форматирования ячеек
Вы можете скрыть содержимое строки формул с помощью функции «Формат ячеек», сделайте следующее.
1. Выберите диапазон с содержимым ячеек или формулами, которые вы хотите скрыть в строке формул.
2. Щелкните выделенный фрагмент правой кнопкой мыши, а затем щелкните Формат ячеек в контекстном меню. Смотрите скриншот:
3. в Формат ячеек диалоговое окно, проверьте Скрытый поле под Protection меню.
4. Затем нажмите Обзор > Защитить лист.
5. Во всплывающем Защитить лист диалоговом окне введите пароль и щелкните значок OK кнопку, затем Подтвердить пароль появится диалоговое окно, введите пароль еще раз и нажмите кнопку OK кнопка. Смотрите скриншот:
Затем содержимое выбранных ячеек сразу скрывается в строке формул.
Скрыть содержимое строки формул с помощью Kutools for Excel
Работы С Нами Kutools for Excel, вы можете легко скрыть содержимое строки формул в Excel. Пожалуйста, сделайте следующее.
Перед применением Kutools for Excel, Пожалуйста, сначала скачайте и установите.
1. Нажмите Kutools Plus > Дизайн рабочего листа для Дизайн меню.
2. Выберите ячейки, содержимое которых вы хотите скрыть, в строке формул, а затем щелкните Формулы Скрыть под Дизайн Вкладка. Нажмите OK во всплывающем диалоговом окне.
3. Нажмите Защитить лист под Дизайн Вкладка, затем введите пароль и подтвердите его в следующих двух всплывающих диалоговых окнах.
С этого момента содержимое выбранных ячеек (включая формулы) не будет отображаться в строке формул, пока рабочий лист не будет снят с защиты.
Если вы хотите получить 30-дневную бесплатную пробную версию этой утилиты, пожалуйста, нажмите, чтобы загрузить это, а затем перейдите к применению операции в соответствии с указанными выше шагами.
Читайте также: