Как в эксель делать формулы: 29 способов сэкономить время с помощью формул Excel

Копирование формул без сдвига ссылок

34072
10.01.2014
Скачать пример

Проблема

Предположим, что у нас есть вот такая несложная таблица, в которой подсчитываются суммы по каждому месяцу в двух городах, а затем итог переводится в евро по курсу из желтой ячейки J2.

Проблема в том, что если скопировать диапазон D2:D8 с формулами куда-нибудь в другое место на лист, то Microsoft Excel автоматически скорректирует ссылки в этих формулах, сдвинув их на новое место и перестав считать:

Задача: скопировать диапазон с формулами так, чтобы формулы не изменились и остались теми же самыми, сохранив результаты расчета.

Способ 1. Абсолютные ссылки

Как можно заметить по предыдущей картинке, Excel сдвигает только относительные ссылки. Абсолютная (со знаками $) ссылка на желтую ячейку $J$2 не сместилась. Поэтому для точного копирования формул можно временно перевести все ссылки во всех формулах в абсолютные. Нужно будет выделить каждую формулу в строке формул и нажать клавишу F4:

При большом количестве ячеек этот вариант, понятное дело, отпадает — слишком трудоемко.

Способ 2. Временная деактивация формул

Чтобы формулы при копировании не менялись, надо (временно) сделать так, чтобы Excel перестал их рассматривать как формулы. Это можно сделать, заменив на время копирования знак «равно» (=) на любой другой символ, не встречающийся обычно в формулах, например на «решетку» (#) или на пару амперсандов (&&). Для этого:

1. Выделяем диапазон с формулами (в нашем примере D2:D8)




2. Жмем Ctrl+H на клавиатуре или на вкладке Главная — Найти и выделить — Заменить (Home — Find&Select — Replace)




3. В появившемся диалоговом окне вводим что ищем и на что заменяем и в Параметрах (Options) не забываем уточнить Область поиска — Формулы. Жмем Заменить все (Replace all).




4. Копируем получившийся диапазон с деактивированными формулами в нужное место:




5. Заменяем # на = обратно с помощью того же окна, возвращая функциональность формулам.

Способ 3. Копирование через Блокнот

Этот способ существенно быстрее и проще.

Нажмите сочетание клавиш Ctrl+Ё или кнопку Показать формулы на вкладке Формулы (Formulas — Show formulas), чтобы включить режим проверки формул — в ячейках вместо результатов начнут отображаться формулы, по которым они посчитаны:

Скопируйте наш диапазон D2:D8 и вставьте его в стандартный Блокнот:

Теперь выделите все вставленное (Ctrl+A), скопируйте в буфер еще раз (Ctrl+C) и вставьте на лист в нужное вам место:

Осталось только отжать кнопку Показать формулы (Show Formulas), чтобы вернуть Excel в обычный режим.

Примечание: этот способ иногда дает сбой на сложных таблицах с объединенными ячейками, но в подавляющем большинстве случаев — работает отлично.

Способ 4. Макрос

Если подобное копирование формул без сдвига ссылок вам приходится делать часто, то имеет смысл использовать для этого макрос. Нажмите сочетание клавиш Alt+F11 или кнопку Visual Basic на вкладке Разработчик (Developer), вставьте новый модуль через меню Insert — Module  и скопируйте туда текст вот такого макроса:

Sub Copy_Formulas()
    Dim copyRange As Range, pasteRange As Range
    
    On Error Resume Next
    Set copyRange = Application.InputBox("Выделите ячейки с формулами, которые надо скопировать.", _
                                "Точное копирование формул", Default:=Selection.Address, Type:=8)
    If copyRange Is Nothing Then Exit Sub
    Set pasteRange = Application.InputBox("Теперь выделите диапазон вставки." & vbCrLf & vbCrLf & _
                                          "Диапазон должен быть равен по размеру исходному " & vbCrLf & _
                                          "диапазону копируемых ячеек. ", "Точное копирование формул", _
                                          Default:=Selection.Address, Type:=8)
    
    If pasteRange.Cells.Count <> copyRange.Cells.Count Then
        MsgBox "Диапазоны копирования и вставки разного размера!", vbExclamation, "Ошибка копирования"
        Exit Sub
    End If
    
    If pasteRange Is Nothing Then
        Exit Sub
    Else
        pasteRange.Formula = copyRange.Formula
    End If
End Sub

Для запуска макроса можно воспользоваться кнопкой Макросы на вкладке Разработчик (Developer — Macros) или сочетанием клавиш Alt+F8. После запуска макрос попросит вас выделить диапазон с исходными формулами и диапазон вставки и произведет точное копирование формул автоматически:

Ссылки по теме

• Удобный просмотр формул и результатов одновременно




• Зачем нужен стиль ссылок R1C1 в формулах Excel




• Как быстро найти все ячейки с формулами




• Инструмент для точного копирования формул из надстройки PLEX

Как создать формулу в Excel?

В Excel полно формул. Те, кто осваивает эти формулы, являются профессионалами в Excel. Однако в начале изучения Excel всем любопытно узнать, как применять или создавать формулы в Excel. Если вы один из тех, кто хочет научиться создавать формулы в Excel, то эта статья лучше всего подходит для вас. В этой статье будет полное руководство по применению формулы от нуля до промежуточного уровня в Excel.

Давайте создадим простую формулу типа калькулятора для сложения чисел, чтобы начать с формул Excel.

Вы можете скачать это Создание шаблона формулы Excel здесь — Создать шаблон формулы Excel

Посмотрите на приведенные ниже данные чисел.

В ячейке A1 у нас есть 25. В ячейке A2 есть 40, число.

В ячейке A3 нам нужно сложить эти два числа.

В Excel, чтобы начать формулу, всегда ставьте первым знак равенства.

Теперь вставьте 25 + 40 в уравнение.

Это очень похоже на то, что мы делаем в калькуляторе.

Нажмите клавишу «Ввод», чтобы получить сумму этих чисел.

Итак, 25 + 40 равно 65, то же самое мы получили в ячейке A3.

Содержание

• Как создать формулу в Excel?
  • #1 Создать гибкую формулу со ссылками на ячейки
  • #2 Использовать функцию СУММ для сложения чисел
  • #3 Создать формулы со ссылками на другие ячейки Excel
  • Рекомендуемые статьи

#1 Создать гибкую формулу со ссылками на ячейки

Начнем.

1. В приведенном выше примере мы изменим число с 25 на 30 и с 40 на 50.

   Несмотря на то, что мы изменили числа в ячейках A1 и A2, наша формула показывает только старый результат 65. Это проблема прямой передачи чисел в формулу. Это не делает формулу достаточно гибкой для обновления нового результата.

2. Мы можем дать ссылку на ячейку в качестве ссылки на формулу для преодоления этой проблемы. Например, откройте знак равенства в ячейке A3.

3. Затем выберите ячейку A1.

4. Вставьте знак плюс (+) и выберите ячейку A2.

5. Нажмите клавишу «Ввод», чтобы получить результат.

Как мы видим в строке формул, результат не отображается. Скорее, она показывает саму формулу, а ячейка A3 показывает результат формулы.

Теперь мы можем изменить числа в ячейках A1 и A2, чтобы увидеть непосредственное влияние формулы.

#2 Использование функции СУММ для сложения чисел

Чтобы привыкнуть к формулам в Excel, начнем с простой функции СУММ. Все формулы должны начинаться с «+» или «=». Итак, открываем знак равенства в ячейке A3.

Начните вводить СУММ, чтобы увидеть список функций Excel IntelliSense Список функций ExcelФункции Excel помогают пользователям экономить время и вести обширные рабочие листы. Существует более 100 функций Excel, классифицированных как финансовые, логические, текстовые, дата и время, поиск и справочная информация, математические, статистические и информационные функции. Подробнее.

Нажмите клавишу «Tab» после выбора формулы СУММ, чтобы открыть функцию СУММ в excel. Функция СУММ в Excel. Функция СУММ в Excel складывает числовые значения в диапазоне ячеек. Будучи отнесенной к категории функции «Математика и тригонометрия», она вводится путем ввода «= СУММ», за которым следуют значения для суммирования. Значения, предоставляемые функции, могут быть числами, ссылками на ячейки или диапазонами. Подробнее

Первый аргумент функции СУММ — Число 1, — это первое число, которое нам нужно добавить. В этом примере ячейка A1. Итак, мы должны выбрать ячейку A1.

Следующий аргумент Число 2, второе число или элемент, который нам нужно добавить, ячейка A2.

Теперь мы должны закрыть скобку и нажать клавишу «Ввод», чтобы увидеть результат функции СУММ.

Таким образом, мы можем создавать простые формулы в Excel для выполнения вычислений.

#3 Создание ссылок формулы на другие ячейки Excel

Мы рассмотрели основы создания формулы в Excel. Точно так же мы можем применить одну формулу и к другим связанным ячейкам. Например, посмотрите на данные ниже.

В столбце А есть «Единицы». В столбце B у нас есть «Цена за единицу».

В столбце C необходимо получить «Объем продаж». Для получения суммы продаж используется формула единиц * цена.

• Итак, мы должны открыть знак равенства в ячейке C2.

• Выберите ячейку A2 (единицы).

• Введите несколько знаков (*) и выберите ячейку B2 (цена).

• Нажмите клавишу «Ввод», чтобы получить сумму продаж.

Теперь мы применили формулу в ячейке C2. Как быть с оставшимися ячейками?

Можно ли ввести ту же формулу для остальных ячеек по отдельности?

Если вы так думаете, вы будете рады услышать, что «формула должна быть применена к одной ячейке, затем мы можем копировать и вставлять в другие ячейки».

Теперь сначала посмотрим на формулу, которую мы применили.

Формула говорит A2 * B2.

Итак, когда мы копируем и вставляем приведенную ниже формулу, ячейка A2 становится A3, а B2 становится B3.

Точно так же номера строк продолжают изменяться при движении вниз, а буквы столбцов также будут меняться при движении влево или вправо.

• Скопируйте и вставьте формулу в другие ячейки, чтобы получить результат во всех ячейках.

Таким образом, мы можем создать простую формулу в Excel, чтобы начать обучение.

Рекомендуемые статьи

Эта статья представляет собой руководство по созданию формулы в Excel. Здесь мы учимся создавать простую формулу Excel и практические примеры, а также загружаемый шаблон. Вы можете узнать больше об Excel из следующих статей: –

• Запись формулы в Excel
• PI в Excel
• Формула Excel не работает
• Список основных формул Excel

Как заблокировать формулы в Excel (пошаговое руководство)

Смотреть видео для блокировки формул в Excel

Формулы Excel легко создавать и редактировать в Excel.

Вы можете легко редактировать формулу через строку формул или прямо в ячейке.

Хотя это упрощает создание формул в Excel, у него есть и несколько недостатков.

Подумай об этом.

Вы просматриваете рабочий лист, полный формул, и случайно нажимаете клавишу удаления, клавишу возврата или какую-либо другую цифровую/алфавитную клавишу.

Теперь вам повезет, если вы сможете обнаружить ошибку и исправить ее. Но если вы этого не сделаете, это может привести к некоторым ошибочным результатам.

И позвольте мне сказать вам, что ошибки в Excel обходятся компаниям в миллионы (прочитайте это или это).

Вероятность возникновения таких ошибок увеличивается многократно, когда вы делитесь файлом с коллегами, менеджерами или клиентами.

Один из способов предотвратить это — заблокировать рабочий лист и все ячейки. Однако это помешает пользователю вносить какие-либо изменения в рабочий лист. Например, если вы отправляете книгу своему руководителю на проверку, вы можете разрешить ему добавлять свои комментарии или изменять некоторые ячейки.

Лучший обходной путь — заблокировать только те ячейки, в которых есть формулы.

В этом руководстве рассматриваются:

Блокировка формул в Excel

Прежде чем я покажу вам, как заблокировать формулы в Excel, вот что вы должны знать:

По умолчанию все ячейки в Excel заблокированы. Только когда вы защищаете заблокированные ячейки, вы действительно можете запретить пользователю вносить изменения. Это также означает, что если ячейка не заблокирована и вы ее защищаете, пользователь сможет вносить изменения.

Вот шаги для блокировки формул в Excel (подробно объяснены позже):

1. Выберите все ячейки и разблокируйте их.
2. Выберите все ячейки с формулами (используя Перейти к специальному).
3. Заблокируйте выбранные ячейки.
4. Защитите рабочий лист.

Теперь, когда я описал шаги выше, давайте углубимся и посмотрим, как это сделать (и, что более важно, почему мы должны это сделать):

Шаг 1: выберите все ячейки и разблокируйте их

Хотя вы можете найти это сбивает с толку, терпите меня и продолжайте читать.

Как я уже упоминал, только ячейки, которые заблокированы, а также защищены, могут быть действительно ограничены. Если все ячейки заблокированы, а я защищаю весь рабочий лист, это означает, что пользователь не может ничего изменить.

Но мы хотим заблокировать (ограничить доступ) только те ячейки, в которых есть формулы.

Для этого нам сначала нужно разблокировать все ячейки, а затем выбрать и заблокировать только те ячейки, в которых есть формулы.

Вот шаги, чтобы разблокировать все ячейки:

• Выберите все ячейки на листе (используйте сочетание клавиш Control + A).
• Используйте сочетание клавиш Control + 1 (удерживайте клавишу Control, а затем нажмите 1). Это откроет диалоговое окно форматирования ячеек.
• В диалоговом окне форматирования ячеек выберите вкладку Защита.
• Снимите флажок «Заблокировано».
• Нажмите «ОК».

Шаг 2. Выберите все ячейки с формулами

Теперь, когда все ячейки разблокированы, нам нужно убедиться, что ячейки с формулами заблокированы.

Для этого нам нужно сначала выделить все ячейки с формулами.

Вот шаги для выбора всех ячеек с формулами:

• Выделите все ячейки на листе (используйте Control + A).
• Перейдите на главную и в группе «Редактирование» нажмите «Найти и выбрать».
• В раскрывающемся списке выберите Перейти к специальному.
• В диалоговом окне «Перейти к специальному» выберите «Формулы».
• Нажмите кнопку ОК.

Это выделит все ячейки, в которых есть формулы.

Шаг 3: Блокировка ячеек с формулами

Теперь, когда мы выбрали ячейки с формулами, нам нужно вернуться и заблокировать эти ячейки (включить свойство блокировки, которое мы отключили на шаге 1).

Как только мы это сделаем, защита рабочего листа заблокирует те ячейки, в которых есть формулы, но не другие ячейки.

Вот шаги для блокировки ячеек с формулами:

• Выделив ячейки с формулами, нажмите Control + 1 (удерживайте клавишу Control, а затем нажмите 1).
• В диалоговом окне форматирования ячеек выберите вкладку Защита.
• Установите флажок «Заблокировано».
• Нажмите «ОК».

Шаг 4. Защита рабочего листа

Теперь, когда свойство «Заблокировано» включено для ячеек с формулами (а не для других ячеек), защита всего рабочего листа ограничит доступ только к ячейкам с формулами.

Вот шаги для защиты рабочего листа:

• Перейдите на вкладку Обзор.
• Щелкните Защитить лист.
• В диалоговом окне «Защитить лист» убедитесь, что установлен флажок «Защитить лист и содержимое заблокированных ячеек».
• [Необязательно] Укажите пароль.
• Нажмите кнопку ОК.

После того, как вы выполните указанные выше четыре шага, все ячейки с формулами будут заблокированы, и пользователь не сможет ничего в них изменить.

Если пользователь попытается изменить ячейки, он/она получит подсказку, как показано ниже:

Как скрыть формулы в Excel

Когда вы блокируете формулы в Excel, пользователь не может вносить какие-либо изменения в ячейки с формулами.

Однако, если эта ячейка выбрана, формула в ячейке будет видна в строке формул.

Хотя в большинстве случаев это не проблема, но если вы не хотите, чтобы формула была видна, вам нужно ее скрыть.

Вот шаги, чтобы скрыть формулы в заблокированных ячейках:

• Выберите все ячейки на листе (используйте Control + A).
• Перейдите на главную и в группе «Редактирование» нажмите «Найти и выбрать».
• В раскрывающемся списке выберите Перейти к специальному.
• В диалоговом окне «Перейти к специальному» выберите «Формулы».
• Нажмите кнопку ОК. Это выделит все ячейки, в которых есть формулы.
• Нажмите Control + 1 (удерживайте клавишу Control, а затем нажмите 1).