Как формулу в excel сделать значением

Добавил пользователь Валентин П.
Обновлено: 06.09.2024

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

• либо это значения, вычисленные этими формулами
• либо сам текст формул

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

• где удалять формулы:
  • в диапазоне,
  • всем листе
  • или всей книге

  Эта статья даст ответы на все такие вопросы.

  Удалить формулы и сохранить данные

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

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

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

  С помощью мыши

  Не самый быстрый, т.к. работает только одна рука, но самый удобный и понятный способ. Выделяем, копируем, и в контекстном меню, вызываемом нажатием правой кнопки мыши по выделенному диапазону, нужно выбрать вот эту команду (выделена красным):

  С помощью клавиатуры (сочетания клавиш)

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

  1. выделяем диапазон
     • мышью, если это столбец, строка или лист
     • сочетаниями Ctrl+Shift+стрелки или Shift+стрелки, если ячейки или диапазоны ячеек
  2. копируем сочетанием Ctrl+C
  3. перемещаемся стрелками к диапазону, куда нужно вставить данные и/или Ctrl+Tab для перехода в другую книгу
  4. вызываем контекстное меню клавишей на клавиатуре, иногда ее нет, но обычно на клавиатурах она есть, рядом с правым ALT
  5. Стрелками перемещаемся к команде (Вниз-вниз-вправо)
  6. Клавиша Enter

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

  В 2 клика с надстройкой !SEMTools

  Быструю замену формул на значения в выделенном диапазоне без необходимости копирования и особой вставки я реализовал в своей надстройке для Excel !SEMTools.

  Для тех, кто не готов разбираться в VBA, после установки !SEMTools запуск процедуры доступен в 2 клика из соответствующего меню в группе «УДАЛИТЬ«. Функционал относится к бесплатным возможностям !SEMTools (работают без ограничений в любой версии).

  Удалить формулы и сохранить значения в Excel с !SEMTools

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

  Он отработает на отлично, как и та же процедура надстройки. В синтаксисе кода присутствует проверка ячеек на предмет того, скрыты ли они.

  Удаляем формулы только в отфильтрованных строках, пропуская скрытые

  Удалить формулы на листе / во всех листах

  Когда нужно быстро избавиться от всех формул на листе или во всей книге, также могут помочь соответствующие простенькие процедуры, написанные на VBA:

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

  Удаляем формулы со всего листа с !SEMTools

  Перевод формул в их текст (без сохранения результирующих значений)

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

  
  

  
  

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

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

  Работа с формулами

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

  
  
  

  
  
  

  
  
  

  
  
  

  Постоянные и абсолютные ссылки

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

  
  
  

  
  
  

  
  
  

  Виды формул

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

  Простые

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

  
  
  

  ПРОИЗВЕД

  
  
  

  ОКРУГЛ

  
  Помогает произвести округление дробного числа в большую (ОКРУГЛВВЕРХ) или меньшую сторону (ОКРУГЛВНИЗ).
  

  
  
  

  
  
  

  
  Это – поиск необходимых данных в таблице или диапазоне по строкам. Рассмотрим функцию на примере поиска сотрудника из списка по коду.
  

  
  
  

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

  Таблица – диапазон, в котором будет осуществляться поиск.
  

  Номер столбца – порядковый номер столбца, где будет осуществляться поиск.
  

  Альтернативные функции – ИНДЕКС/ПОИСКПОЗ.
  

  СЦЕПИТЬ/СЦЕП

  =СЦЕПИТЬ(значение1;значение2) – цельный текст
  

  
  
  

  =СЦЕПИТЬ(значение1;" ";значение2) – между словами пробел или знак препинания
  

  
  
  

  КОРЕНЬ

  
  
  

  
  
  

  ПРОПИСН

  
  
  

  
  
  

  СТРОЧН

  
  
  

  =СЧЁТ(диапазон_ячеек)

  
  
  
  

  СЖПРОБЕЛЫ

  
  
  

  Сложные

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

  
  
  

  
  Анализирует выбранную ячейку и проверяет, отвечает ли значение заданным параметрам. Возможны два результата: если отвечает – истина, не отвечает – ложь.
  

  
  
  

  
  
  

  СУММЕСЛИ

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

  
  
  

  Исходя из примера, программа посчитала суммы всех чисел, которые больше 10.
  

  СУММЕСЛИМН

  
  
  

  Программа посчитала общую сумму зарплат женщин-кассиров.
  

  По такому же принципу работают функции СЧЁТЕСЛИ, СРЗНАЧЕСЛИ и т.п.
  

  Комбинированные

  Есть задача: найти сумму трех чисел и умножить ее на коэффициент 1,3, если она меньше 80, и на коэффициент 1,6 – если больше 80.
  

  По окончании этого урока вы сможете:

  1. Перечислить и показать арифметические операторы для создания формулы
  2. Вводить вручную формулы
  3. Использовать встроенные функции
  4. Выбирать нужные функции из контекстного меню

  1. Арифметические операторы

  
  
  

  Все эти символы вводятся с клавиатуры. Рассмотрим на примере.

  Шаг 1. Щелкаем по ячейке А1, куда будет введена формула.

  
  

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

  Шаг 3. Нажимаем Enter. Активной станет ячейка А2.

  Шаг 4. Делаем активной ячейку А1.

  
  

  Понять и запомнить!

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

  Алгоритм ввода формулы при помощи арифметических операторов:

  1. Щелкаем на ячейке, в которой должны появиться результаты формулы.
  2. Вводим сначала знак равенства (=), а потом - первое значение.
  3. Вводим арифметический оператор, а затем - второе значение.
  4. После того, как вся формула введена, нажимаем Enter, чтобы завершить ввод.

  2. Формула с использованием данных, которые находятся в ячейках

  При расчете плотности вязания мне часто приходится вычислять пропорцию:

  Размер	Количество петель
  10 см образца	18 петель
  50 см размер изделия	Сколько нужно набрать петель?

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

  
  

  
  

  
  

  
  

  Шаг 5. Завершаем ввод, нажав клавишу Enter.

  
  

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

  3. Формула с использованием встроенной функции и данных, которые находятся в ячейках

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

  
  

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

  
  

  
  

  
  

  Все время контролируем полученное значение. Уже 90. А заодно внимательно посмотрим на строку формул. Формула записалась с соблюдением синтаксиса Excel.

  
  

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

  Изменяем значение в любой ячейке, которая участвует в формуле, например в ячейке B2:

  
  

  Формула вычислила новое значение

  4. Формула, записанная вручную

  Удалите в ячейке В3 формулу. Записываем формулу заново.

  
  

  
  

  При щелчке по зеленому треугольнику ЛМ появится значок с восклицательным знаком, а уже при щелчке ЛМ по этому значку появится выпадающее меню:

  
  

  Разбираться с этим меню вы будете весь световой день, но ошибку не найдёте. Так что лучше не делать ошибок в синтаксисе формул. Самое лучшее в этом случае щелкнуть ЛМ по строке формул:

  
  

  5. Суммирование строк и столбцов

  Шаг 1. Создаем ряд чисел в столбце и делаем активной ячейку суммирования.

  Шаг 2. Повторяем Шаги 1?2 из пункта 4:

  Шаг 3. Щелкаем по первой ячейке ряда чисел, которые следует просуммировать, и протягиваем курсор вниз, то есть выделяем диапазон ячеек.

  
  

  Шаг 1. Нажимаем Enter

  Есть более простой способ: воспользоваться кнопкой Автосумма (AutoSum) на ленте Главная (Лента Главная -> группа команд Редактирование -> команда Автосумма). Эта кнопка продублирована на ленте Формулы.

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

  
  

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

  Шаг 1. Нажимаем кнопку Автосумма на ленте Главная:

  
  

  Шаг 2. Нажимаем Enter

  Но по жизни часто надо просуммировать расходы, которые записаны по разным столбцам (например, при определении общих семейных расходов). То есть необходимо узнать сумму по разным диапазонам.

  Создаем ряд чисел по двум столбцам и делаем активной ячейку суммирования.

  Шаг 1. Повторяем Шаги 1?2 из пункта 4:

  Шаг 7. Выбираем первый диапазон (1), затем второй диапазон (2) простым перетаскиванием ЛМ:

  
  

  6. Далеко идущие выводы

  
  

  2. Формула с использованием данных, которые находятся в ячейках

  
  

  3. Формула с использованием встроенной функции и данных, которые находятся в ячейках

  
  

  4. Формула с использованием встроенной функции и данных, которые находятся в двух диапазонах

  
  

  Теперь вы можете:

  1. Перечислить и показать арифметические операторы для создания формулы
  2. Вводить вручную формулы
  3. Использовать встроенные функции
  4. Выбирать нужные функции из контекстного меню

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

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

  Как написать формулу в Excel

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

  
  

  Каждая ячейка в Excel является неделимой единицей с собственным идентификатором (адрес), который обозначается буквой (номер столбца) и цифрой (номер строки). Отображается адрес в поле над таблицей.
  
  Итак, как создать и вставить формулу в Excel? Действуйте по следующему алгоритму:

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

  Обозначение Значение

  + Сложение
  - Вычитание
  / Деление
  * Умножение

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

  Использование функций для вычислений

  Формула текст в Excel

  Формула даты в Excel

  Автосумма в Excel

  Работа в Excel с формулами: пример

  

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

    
  • Разделительный состав для бетона своими руками
    
  • Электропроводка полуприцепа своими руками
    
  • Шапочка божья коровка своими руками для детского сада
    
  • Полировка линолеума своими руками
    
  • Полки во дворе своими руками