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

Преобразование ФОРМУЛЫ в ЗНАЧЕНИЕ в Excel

Home > Основы Excel > Преобразование формулы в значение в Excel

Зачем преобразовывать формулу в значение

1. Вставка значений с помощью простой комбинации клавиш

2. Использование Специальная вставка для преобразования в значения

3. Использование контекстного меню

4. Преобразование формул в значения с помощью кода VBA

5. Преобразование в значения внутри формулы

6. Получение значения результата формулы с помощью Power Query

Заключение

Связанные учебные пособия

С первых дней моей работы я усвоил одну трудную вещь:

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

Позвольте мне рассказать вам несколько интересных способов сделать это, но до этого.

Почему следует преобразовывать формулу в значение

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

1. Если вы отправляете кому-то отчет без исходных данных, то лучше все формулы перевести в статические значения.
2. Некоторые функции являются непостоянными, и при обновлении рабочего листа их значение будет пересчитано.
3. Для предотвращения внесения конечным пользователем каких-либо изменений в расчет.
4. Статические значения легко редактируются, и каждый может легко скопировать и вставить их.
5. Вы же не хотите запутать конечного пользователя сложными формулами.
6. Или просто не хотят показывать формулы.

Краткое примечание: Это один из тех советов по Excel, которые могут помочь улучшить навыки Excel

1. Вставьте значения с простой сочетание клавиш

Когда речь идет о скорости и легче, клавиши коротких ударов являются лучшими и превращать формулы в значения. Самый простальный путь — для использования клавиша шортчата.

Все, что вам нужно сделать:

• Выберите диапазон ячеек, в которых у вас есть формулы.
• Нажмите Ctrl + C, чтобы скопировать ячейки.

• Затем нажмите Alt + E S V.

С помощью этой клавиши быстрого доступа все формулы будут заменены их статическим значением результата.

Примечание: Если вы не хотите потерять исходные формулы, вам следует сделать резервную копию файла.

2. Использование специальной вставки для преобразования в значения

Если вам не нравится использовать горячую клавишу, вы можете просто заменить ее специальной опцией вставки.

• Прежде всего, выберите диапазон дат или весь столбец.
• Затем щелкните правой кнопкой мыши и скопируйте его.
• Перейти на вкладку «Главная» ➜ Буфер обмена ➜ Вставить ➜ Специальная вставка.

• Или вы также можете нажать правую кнопку мыши и перейти к Специальная вставка -> Значения.

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

3. Использование контекстного меню

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

1. Прежде всего, выберите диапазон ячеек, в которых у вас есть формулы.
2. Теперь щелкните правой кнопкой мыши край выделения.
3. И, удерживая правую кнопку мыши, перетащите диапазон в правую сторону и мгновенно верните его на исходное место и отпустите.
4. Здесь вы получите меню опций.
5. В этом меню параметров выберите «Копировать сюда только как значения».

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

4. Преобразование формул в значения с помощью кода VBA

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

 Sub ConvertToValues()
Затемнить MyRange как диапазон
Dim MyCell как диапазон
Выберите дело
MsgBox("Это действие нельзя отменить." & _
«Сначала сохранить книгу?», _
vbYesNoCancel, "Предупреждение")
Дело = vbДа
ThisWorkbook.Сохранить
Случай = vbCancel
Выйти из подпрограммы
Конец выбора
Установите MyRange = Выбор
Для каждой ячейки MyCell в MyRange
Если MyCell.HasFormula Тогда
МояЯчейка.Формула = МояЯчейка.Значение
Конец, если
Следующая MyCell
Конец суб 

Связанный: Учебник по Excel VBA

5. Преобразование в значения внутри формулы

Давайте думать по-другому. У вас есть сложная формула, представляющая собой комбинацию двух или трех функций. Здесь у вас есть длинная формула для очистки текстового значения, а затем преобразования его в правильный регистр.

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

1. Отредактируйте формулу, нажав F2.
2. Выберите только ту часть формулы, которую необходимо преобразовать в статическое значение.
3. Нажмите F9.

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

6. Получить значение результата формулы с помощью Power Query

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

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

• Выберите таблицу и перейдите на вкладку «Данные» ➜ «Получить и преобразовать» ➜ «Из таблицы».

• Это откроет вашу таблицу в редакторе запросов мощности.

• Здесь просто нажмите «Закрыть и загрузить».

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

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

Связано: Учебное пособие по Excel Power Query

Заключение

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

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

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

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

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

Основы Microsoft Excel (компоненты, детали и примечания к знаниям)

Связанные учебные пособия

Как использовать формулу автозаполнения в Excel (6 эффективных способов)

Получите БЕСПЛАТНЫЕ расширенные упражнения Excel с решениями!

В этом учебном пособии показано, как использовать формулу Autofill в Excel . Когда мы имеем дело с большим количеством данных или ячеек в Excel, часто нам приходится использовать одну и ту же формулу во многих ячейках. Мы можем легко сделать это, используя формулу автозаполнения в Excel. Более того, в Excel доступно множество полезных сочетаний клавиш и атрибутов для получения надлежащего результата. Это не только экономит наше время, но и помогает снизить количество ошибок в общей работе. Итак, очень важно научиться использовать формула автозаполнения в Excel.

Скачать практическую рабочую тетрадь

6 эффективных методов использования формулы автозаполнения Excel

1. Использование команды FILL для формулы автозаполнения Excel

2. Использование параметра FILL HANDLE для формулы автозаполнения Excel

я. Двойным щелчком значка ручки автозаполнения

II. Путем перетаскивания значка ручки автозаполнения

3. Использование сочетания клавиш для формулы автозаполнения Excel

4. Использование формулы массива

5. Создав таблицу

6. Путем копирования и вставки ячейки

Заключение

Статьи по Теме

Загрузить рабочую тетрадь

Загрузите этот практический лист, чтобы практиковаться, пока вы читаете эту статью.

6 эффективных методов использования формулы автозаполнения Excel

В этом разделе вы найдете шесть способов выполнить задание. Мы будем использовать образец обзора набора данных в качестве примера в Excel, чтобы его было легко понять. Например, на данном листе около «Товары» приведены в столбце B , а их «Цена за единицу» доступна в столбце C , «Количество» в столбце D и «Скидка» в столбец E . Если вы правильно выполните шаги, вы должны научиться использовать формулу Autofill в Excel самостоятельно. Шаги

1. Использование команды ЗАПОЛНИТЬ для формулы автозаполнения Excel

В этом первом случае мы хотим использовать Команда ЗАПОЛНИТЬ для формулы автозаполнения Excel. Но для этого сначала нужно определить правильный результат, а затем применить его далее ко всем ячейкам с помощью команды ЗАПОЛНИТЬ в Excel. Мы опишем шаги этого метода ниже.

• Сначала в ячейку F5 вставьте следующую формулу.

=C5*D5-C5*D5*E5

Итак, мы умножили цену за единицу на количество , а затем вычли Снижаем от значения и нажимаем «Ввод» для получения результата.

• Далее мы получили результат одной ячейки. Теперь выберите весь столбец «Общая цена» и нажмите на опцию «Заполнить» , чтобы щелкнуть опцию «Вниз» , как на изображении ниже.

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

Таким образом, мы имеем автоматически заполненных ячеек с помощью команды Заполнить в Excel.

Подробнее: Как автозаполнить ячейку на основе другой ячейки в Excel

2. Использование опции FILL HANDLE для формулы автозаполнения Excel

Метод FILL HANDLE — один из самых простых способов автозаполнения формул. Вы можете сделать это, применяя два подхода.

я. Двойным щелчком значка ручки автозаполнения

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

• Наконец, когда вы увидите этот значок, дважды щелкните по нему, и вы получите желаемый результат.

Итак, у нас есть автоматически заполненных ячеек с помощью d двойного щелчка  в Excel.

ii. Путем перетаскивания значка ручки автозаполнения

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

• Таким образом, Excel автоматически заполнит ячеек соответствующим результатом.

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

Подробнее: Увеличение числа перетаскиваний не работает в Excel (решение с простыми шагами)

3. Использование сочетания клавиш для формулы автозаполнения Excel

Чтобы применить этот метод, вы должны выполнить следующие шаги.

• Первоначально мы должны получить аналогичный результат, используя ту же формулу в ячейке F5 точно так же, как первый метод .
•  После этого выберите ячейку с формулой и нажмите клавишу «SHIFT+клавиша со стрелкой вниз (🔽)» .

• Кроме того, нажмите «CTRL+D» , чтобы применить формулу ко всем выбранным ячейкам.

• Кроме того, вы также можете нажать «CTRL+ENTER» , чтобы получить аналогичный результат.

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

Подробнее: Как применить ярлык автозаполнения в Excel (7 методов)

Аналогичные показания

• Как автоматически заполнять возрастающие числа в Excel (5 быстрых способов)
• Как заполнить пробелы в Excel (4 быстрых метода)
• Flash Fill не распознает шаблон в Excel (4 причины с исправлениями)
• Автозаполнение не увеличивается в Excel? (3 решения)
• [Исправление] Серия заполнения Excel не работает (8 причин с решениями)

4. Использование формулы массива

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

• Первоначально в ячейку F5 вставьте следующую формулу.

=C5:C11*D5:D11-C5:C11*D5:D11*E5:E11

• После этого нажмите кнопку Введите , чтобы применить эту формулу.
• Наконец, вы получите правильный результат, как на изображении ниже.

В результате у нас есть автоматически заполненных ячеек с использованием формулы Array в Excel.

5. Путем создания таблицы

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

• Сначала выберите диапазон данных > перейдите к «Вставка»> нажмите на «Таблица» опция .

• Затем появится новое окно с именем Создать таблицу. Выберите в окне диапазон данных и нажмите OK .

• Теперь вставьте ту же формулу в ячейку F5 , как в первом методе .

• Затем нажмите Введите , чтобы применить условие, и вы получите правильный результат, аналогичный изображенному ниже.

Следовательно, у нас есть автоматически заполненных ячеек с помощью команды Table в Excel.

Подробнее: [Исправлено!] Формула автозаполнения не работает в таблице Excel (3 решения)

6. Путем копирования и вставки ячейки

Метод копирования-вставки очень прост. Чтобы использовать этот метод, выполните следующие действия.

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

• После этого скопируйте ячейку с формулой, нажав « CTRL + C », а затем выберите ячейки, в которые вы хотите ее вставить.

• Наконец, просто нажмите «CTRL+V» , чтобы применить формулу, или используйте мышь, чтобы вставить формулу.

В конце этой статьи мы автоматически заполнили ячейки с помощью команды копировать-вставить в Excel.

Подробнее: Как автоматически заполнять ячейки на основе другой ячейки в Excel

Заключение

В дальнейшем следуйте вышеописанным методам. Надеюсь, эти методы помогут вам научиться использовать Формула автозаполнения в Excel. Мы будем рады узнать, сможете ли вы выполнить задание каким-либо другим способом. Следите за веб-сайтом ExcelDemy для получения дополнительных статей, подобных этой.