Как в excel формулы: Обзор формул — Служба поддержки Майкрософт

1. Что такое формула в Excel? Какова её структура? Какие элементы может включать формула?

Формулы
– это выражение, начинающееся со знака
равенства и состоящее из числовых
величин, адресов ячеек, функций, имен,
которые соединены знаками арифметических
операций. К знакам арифметических
операций, которые используются в Excel
относятся:
сложение;
вычитание; умножение; деление; возведение
в степень. Некоторые операции в формуле
имеют более высокий приоритет и
выполняются в такой последовательности:

  • возведение в
    степень и выражения в скобках;

  • умножение и
    деление;

  • сложение и
    вычитание.

2. Каковы правила ввода и редактирования формул в Excel?

Вводить формулу
надо со знака равенства. Это надо для
того, чтобы Excel понял, что в ячейку
вводится именно формула, а не данные.Выделим
произвольную ячейку, например А1. В
строке формул введем =2+3 и нажмем Enter. В
ячейке появится результат (5). А в строке
формул останется сама формула.

едактирование
формул происходит аналогично редактированию
текстовых значений в ячейках. Т.е. надо
активизировать ячейку с формулой
выделением или двойным щелчком мыши, а
затем произвести редактирование,
используя, при необходимости, клавиши
Del, Backspace. Фиксация изменений выполняется
клавишей Enter.

3. Что такое ссылка в Excel? Каким образом осуществляется в формуле ссылка на ячейки? Какие ссылки используются в Excel? в чем разница между абсолютной и относительной ссылкой?

Ссылка в Microsoft
Excel — адрес ячейки или связного диапазона
ячеек. В каждом листе Excel может быть 256
(2 в 8 степени) столбцов и 65536 (2 в 16 степени)
строк. Адрес ячейки определяется
пересечением столбца и строки, как в
шахматах или морском бое, например: A1,
C16. Адрес диапазона ячеек задается
адресом верхней левой ячейки и нижней
правой, например: A1:C5.

Есть два вида
представления ссылок в Microsoft Excel:

Ссылки в Excel бывают
3-х типов:

  • Относительные
    ссылки –
    это изменяющийся при копировании и
    перемещении формулы адрес ячейки,
    содержащий исходное данное (операнд).
    Изменение адреса происходит по правилу
    относительной ориентации клетки с
    исходной формулой и клеток с операндами.

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

  • Смешанные
    ссылки.
    Пример: $A1 или A$1, они наполовину
    относительные, наполовину абсолютные.

4. Как можно копировать и перемещать формулы?

Копирование и
перемещение ячеек, содержащих формулы,
можно осуществлять разными способами
с использованием общих для объектов ОС
Windows приемов: используя команды из пункта
меню Правка (или из контекстного меню),
например, Вырезать, Копировать, Вставить;
используя кнопки стандартной панели
инструментов; используя перемещение с
помощью левой или правой кнопки мыши.
При этом копирование формулы выполняется
аналогично перемещению, но с нажатой
клавишей Ctrl.

Как удалить формулы в Excel и сохранить значения?

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

В этом уроке мы собираемся объяснить методы удаления формул из листов Excel и сохранения данных (значений) с помощью следующих методов:

  1. Удалить формулу с помощью специальной вставки
  2. Удалить формулу с помощью Mouse Trick
  3. Удалить формулу с помощью вставки значений
  4. Удалить формулу с помощью вкладки «Главная»
  5. Удалить формулу с помощью сочетаний клавиш
  6. Найти и удалить формулу с помощью сочетаний клавиш
  7. Удалить формулу с нескольких листов (одновременно)

1) Использование специального метода вставки

Вот способ использования специального метода вставки в MS Excel для удаления формул:

  1. Откройте лист Excel, на который вы хотите удалить формулу.
  2. Выберите и Скопируйте все ячейки, содержащие формулы, которые вы хотите удалить.
  1. Щелкните правой кнопкой мыши и выберите Специальная паста опция
  1. Затем в поле Специальная вставка выберите параметр Значение и нажмите OK.
  1. Теперь вы видите, что формула была удалена из ячейки, а данные останутся прежними.

2) Удалить формулу с помощью мыши

Чтобы удалить формулу с помощью мыши, выполните следующие действия.

  1. Откройте лист Excel, на который вы хотите удалить формулу.
  2. Выберите ячейки, в которых есть формулы, которые вы хотите удалить.

  1. Подведите курсор к правому краю столбца.

  1. Удерживая правую клавишу мыши, перетащите курсор немного вправо и верните его в ячейку.
  2. Теперь щелкните правой кнопкой мыши и выберите «Копировать сюда только как значение».

  1. Это «Копировать сюда только как значение» сохраняет значение и удаляет формулу.

3)Удалить формулу с помощью параметра «Вставить значения»

Чтобы удалить формулу с помощью параметра «Вставить значения «, выполните следующие действия.

  1. Откройте лист Excel, на который вы хотите удалить формулу.
  2. Выберите все ячейки с формулами, которые вы хотите удалить.
  1. Щелкните правой кнопкой мыши и выберите Копировать , чтобы скопировать все выбранные ячейки.
  1. После этого снова щелкните правой кнопкой мыши и перейдите к Параметры вставки и выберите «Значения».
  1. Теперь вы заметите, что остались только значения, а формулы исчезли!

4) Удалить формулу с помощью вкладки «Главная»

Вы можете использовать следующие шаги для удаления формулы с помощью вкладки «Главная».

  1. Откройте лист Excel, на который вы хотите удалить формулу.
  2. Теперь выберите все ячейки с формулами, которые вы хотите удалить.
  1. Щелкните правой кнопкой мыши выбранные ячейки и выберите Копировать.
  1. Теперь перейдите на вкладку Главная и щелкните стрелку вниз под параметром вставки.
  1. Затем нажмите Вставить значения .
  1. Теперь вы заметите, что вся формула из ячейки исчезла, и ее заменили значения.

5) Удалить формулу с помощью сочетания клавиш

Если вам удобно использовать сочетание клавиш «Вставить как значение», выполните следующие действия:

  1. Откройте лист Excel, с которого вы хотите удалить формулу.
  2. Выберите все ячейки с формулами, которые вы хотите удалить.
  1. Затем нажмите Ctrl+C , чтобы скопировать все выделенные ячейки, содержащие формулу.
  1. Теперь нажмите ALT + E + S + V + Enter , чтобы выбрать параметр Вставить как значения .

6) Поиск и удаление формул с помощью сочетаний клавиш

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

  1. Откройте лист Excel, на который вы хотите удалить формулу.
  2. Нажмите Ctrl + G на клавиатуре, чтобы открыть диалоговое окно, а затем выберите Special .
  1. Теперь выберите из списка опцию Formulas и нажмите OK . Будут выделены все ячейки, содержащие формулы.
  1. Далее выделите цветом все ячейки, содержащие формулы.
  1. Теперь нажмите Ctrl + C , чтобы скопировать одну ячейку, содержащую формулу.
  1. Нажмите сейчас, нажмите ALT + E + S + V + введите , чтобы выбрать Вставить как значения .
  2. Теперь формула заменена значением.
  3. Затем повторите эти шаги для всех остальных выделенных ячеек.

7) Удалить формулу с нескольких листов (одновременно)

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

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

Для этого мы должны сначала сгруппировать листы, к которым мы хотим применить операции. Вот как мы можем это сделать:

  1. Перейдите на вкладку Имя первого листа
  2. Удерживайте кнопку  SHIFT и одновременно нажмите на вкладку Имя последнего листа

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

  1. . Затем перейдите на первый лист и выберите все ячейки, содержащие формулу.
  1. Нажмите Ctrl+C или щелкните правой кнопкой мыши и выберите Копировать .
  1. Теперь щелкните правой кнопкой мыши и перейдите к опции «Вставить» и выберите «Значения».

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

Примечание: Теперь, после выполнения задачи, отключите функцию группировки. Щелкните правой кнопкой мыши любой лист и выберите РАЗГРУППИРОВАТЬ листы.

Заключение

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

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

Вычисление формулы Excel с помощью режима «Редактировать» и клавиши F9

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

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

Самый быстрый способ оценить формулу в Excel — нажать CTRL + ~ (тильда). Это переключает отображение текущего рабочего листа, позволяя переключаться между значениями ячеек и формулами ячеек. Это отлично подходит для простого понимания того, как работает формула, или для определения положения #REF! ошибка — особенно в области, содержащей несколько ячеек.

Если вам требуется дополнительная информация о том, как работает ваша формула или ее подмножество, вы можете оценить формулу на более детальном уровне, используя функцию Excel «Оценить формулу» (Alt + M + V). Это позволяет вам оценивать формулу по ячейке за ячейкой. Однако есть два ключевых фактора, ограничивающих эту функцию: (i) вы можете оценивать формулу только слева направо и (ii) правки нельзя вносить непосредственно в диалоговом окне «Вычислить формулу».

Чтобы преодолеть эти ограничения, вы можете использовать клавишу F9.ключ для оценки вашей формулы или подмножества вашей формулы. Для этого вы должны сначала нажать клавишу F2 в нужной ячейке, чтобы активировать режим «Редактировать» (см. Наше предыдущее сочетание клавиш на двойной клавише F2).

Когда вы находитесь в режиме «Редактировать», у вас есть возможность выделить любую часть формулы для ее оценки, если она может быть оценена как отдельная формула. После выделения нажмите клавишу F9, чтобы вычислить выделенную часть и просто отобразить ее значение или результат. Это может быть полезно для понимания того, как работают сложные формулы, отладки, почему сложные формулы не работают должным образом, или для определения исходного положения любого значения ошибки.

Имейте в виду две ключевые вещи при оценке формулы в режиме «Редактировать» и клавише F9:

  • Обязательно выделите части формулы в режиме «Редактирования», прежде чем нажимать F9, иначе вся формула будет упрощена. до расчетного значения
  • Когда вы закончите оценивать свою формулу:
    • Нажатие клавиши Escape приведет к выходу из режима «Редактировать» и возврату ячейки к исходной формуле
    • Нажатие клавиши Enter заменяет любое из рассчитанных значений в виде жестких кодов в исходной формуле

Выбор метода оценки ваших формул полностью зависит от желаемого результата. Оценка формулы с использованием режима «Редактировать» и клавиши F9 не только позволяет вам понять и отладить формулу на детальном уровне, но также дает вам дополнительную гибкость для оценки любой части формулы в любом порядке по вашему выбору. Используя режим «Редактировать» и клавишу F9 для оценки ваших формул, вы сможете дополнительно минимизировать количество ошибок, содержащихся в ваших электронных таблицах, что повысит доверие пользователей к вашим моделям… и вам.

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

  • Предположим, у вас есть простая таблица, как показано на рисунке ниже, и вы хотите получить показатель EBITDA за 2018 год на основе входных данных ячеек C3 и C4.
  • Ячейка C5 должна возвращать число 444, но вместо этого возвращает ошибку #Н/Д.
  • Чтобы выяснить, откуда возникла ошибка, нажмите F2 в ячейке C5, чтобы войти в режим «Редактировать».
  • Формула в ячейке C5 отображается и использует формулу ИНДЕКС-ПОИСКПОЗ-ПОИСКПОЗ для получения нужной метрики в течение нужного года. Требуемое значение находится в строке 2 столбца 3 массива INDEX.
  • Выделите часть формулы, которая говорит ПОИСКПОЗ(C3,C8:G8,0).

Читайте также: