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

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

Функциональные возможности программы Excel позволяют не только структурировать и работать с большими объемами данных, но и производить различные расчеты. Зачастую после того, как получен результат, рассчитанный по формуле, в ней больше нет необходимости, и в ячейке требуется оставить именно само значение. Более того, в некоторых случаях наличие формулы будет только мешать в последующей работе. Например, если попробовать перенести или скопировать данные из ячейки с формулой в другое место таблицы, результат вычислений будет утерян или изменится, т.к. при выполнении данной процедуры будут изменены ссылки на ячейки, указанные в формуле, за исключением тех случаев, когда вместо относительные ссылок (по умолчанию) использовались абсолютные.

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

Содержание

  1. Удаление формул
    • Метод 1: копируем значения с помощью параметров вставки
    • Метод 2: используем специальную вставку
    • Метод 3: удаляем формулы в исходной таблице
    • Метод 4: удаляем формулы без копирования в другое место
    • Метод 5: применяем макрос
    • Метод 6: удаляем формулу вместе с результатом вычислений
  2. Заключение

Удаление формул

Смотрите также: “Как разъединить объединенные ячейки в Excel”

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

Метод 1: копируем значения с помощью параметров вставки

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

  1. Для начала с помощью зажатой левой кнопки мыши нужно выделить область ячеек, которую мы хотим скопировать.
  2. Правой кнопкой мыши щелкаем по любой точке выделенного диапазона и в открывшемся контекстном меню выбираем пункт “Копировать”. Также, можно вместо этого действия можно просто нажать сочетание клавиш Ctrl+C (после того, как выполнено выделение).Для копирования данных можно также воспользоваться кнопкой “Копировать”, которая расположена на лента программы во вкладке “Главная”.
  3. Переходим в ячейку, начиная с которой мы хотим вставить скопированные данные (эта ячейка станет самой верхней левой точкой диапазона данных, который мы будем вставлять из буфера обмена). Затем правой кнопкой мыши щелкаем по выбранному элементу, в появившемся меню в группе “Параметры вставки” кликаем по варианту “Значения” (значок в виде цифр “123”).
  4. В результате скопированная область данных (только конкретные числовые значения, без формул) будет вставлена в новое место.

Метод 2: используем специальную вставку

Если требуется скопировать-вставить данные с сохранение первоначального вида (форматирования) ячеек, можно воспользоваться “Специальной вставкой”.

  1. Выделяем и копируем требуемый диапазон данных. На этот раз давайте отметим всю таблицу, а для копирования используем кнопку на ленте инструментов.
  2. Переходим в ячейку, начиная с которой планируем вставить скопированные элементы. Щелкаем правой кнопкой мыши по ней, в раскрывшемся контекстном меню кликаем (или наводим указатель мыши) по небольшой стрелке вправо рядом с командой “Специальная вставка”. Всплывет дополнительное подменю, в котором щелкаем по пункту “Значения и исходное форматирование”.
  3. Мы получим исходную таблицу в новом месте с охранением форматирования и конкретными числовыми значениями вместо формул.

Метод 3: удаляем формулы в исходной таблице

Смотрите также: “Как вычитать числа в Экселе: формула, примеры”

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

  1. Копируем нужный диапазон ячеек любым удобным способом, например, воспользовавшись контекстным меню.
  2. Как и в ранее рассмотренном методе, вставляем скопированную область в новое место с сохранением исходного форматирования. После этого, не снимая выделение, копируем только что вставленные данные, например, нажав комбинацию клавиш Ctrl+C (или любым другим удобным способом).
  3. Переходим в самую верхнюю левую ячейку исходной таблицы (или выделяем в ней тот диапазон ячеек, который был скопирован в 1 шаге), кликом правой кнопки мыши вызываем меню, в котором выбираем вставку “Значений”.
  4. Теперь, когда содержимое ячеек без формул скопировано в исходное место на листе, удаляем созданные дубликаты. Для этого, выделяем нужный диапазон данных, щелкаем по нему правой кнопкой мыши и в раскрывшемся меню выбираем пункт “Удалить”.
  5. Появится окно удаления ячеек. Выбираем то, что нужно удалить. Исходя из нашего примера, ставим отметку напротив пункта “строку”, после чего жмем кнопку OK.Также, так как справа от выделенного диапазона нет заполненных ячеек, можно выбрать вариант – “ячейки, со сдвигом влево”.
  6. Задублированный диапазон данных удален. На этом работа по замене формул на конкретные значения в исходной таблице завершена.

Метод 4: удаляем формулы без копирования в другое место

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

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

Метод 5: применяем макрос

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

  1. Щелкаем по меню “Файл”.
  2. В открывшемся окне в боковом перечне слева в самом низу выбираем раздел “Параметры”.
  3. В параметрах программы переходим в подраздел “Настроить ленту”. В правой части окна ставим галочку напротив пункта “Разработчик”, после чего щелкаем OK.

Теперь все готово, чтобы выполнить поставленную задачу:

  1. Переключаемся во вкладку “Разработчик”, в которой щелкаем по кнопке “Visual Basic” (группа “Код”).
  2. Выбрав нужный лист книги нажимаем на кнопку “View Code” (или дважды щелкаем левой кнопкой мыши по выбранному листу), чтобы запустить редактор макросов, куда вставляем код ниже, после чего закрываем данное окно.
    Sub Удаление_формул()
    Selection.Value = Selection.Value
    End Sub
  3. Выделяем диапазон ячеек, содержащих формулы, и нажимаем кнопку “Макросы”, которая расположена во вкладке “Разработчик” (блок инструментов “Код”).
  4. В открывшемся окне выбора макросов отмечаем нужный и щелкаем по кнопке “Выполнить”.
  5. После того, как процедура будет выполнена, все формулы в выбранных ячейках будут заменены результатами расчетов по ним.

Метод 6: удаляем формулу вместе с результатом вычислений

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

  1. Начинаем с того, что выделяем область ячеек, содержащих формулы. Кликом правой кнопки мыши по выделенному диапазону вызываем меню, в котором щелкаем по команде “Очистить содержимое”.Также, вместо этого, после того, как нужные элементы отмечены, можно просто нажать клавишу Del (Delete) на клавиатуре.
  2. В итоге все данные в ячейках, в том числе, формулы будут стерты.

Заключение

Смотрите также: “Как сделать сумму прописью с копейками в Эксель: надстройка, формула”

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

Таблицы и формулы Excel — WiseData



Просмотров:
349

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

Имя таблицы

Когда Вы определяете таблицу, Excel автоматический присваивает ей уникальное имя. Изменить это имя можно на вкладке «Конструктор» ленты инструментов. Указав необходимое имя в поле «Имя таблицы» группы «Свойства».

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

Итоги

Как же легко добавлять итоги в таблицу. Встаньте в любую ячейку таблицы, откройте вкладку на ленте инструментов «Конструктор» и в группе элементов «Параметры стилей таблиц» выберете галочку «Строка итогов». Всё итог появится внизу таблицы.

Как видно итоговая сумма появилась только под полем общей стоимости продаж. Нам нужны еще итоги по полю «TotalCost», среднее значение по «DiscountAmount» и посчитать количество по полю «DiscountQuantity» учитывая только значения больше 0.

Для того что бы добавить итог сумму по полю «TotalCost» встаем курсором в строку «Итог» столбца и в выпадающем списке выбираем «Сумма». Так же встаем в столбце «DiscountAmount» на последнюю строку итоги и выбираем в выпадающем списке «Среднее». С количеством по полю «DiscountQuantity» интереснее. Так же в нужном столбце встаем в последнюю строку и в выпадающем списке выбираем «Другие функции». Откроется привычное окно «Мастер функций» в котором в категории «Статистические» выбираем функцию «СЧЕТЕСЛИ».

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

Нажмем ОК. и наша строка итогов, после всех действий должна принять такой вид:

Интересна особенность вставки формулы итогов через саму строку итогов. Вместо вставки функций СРЗНАЧ, СЧЁТ, СЧЕТЗ, МАКС, МИН, ПРОИЗВЕД, СТАНДОТКЛЛОН, СТАНДОТКЛОНП, СУММ, ДИСП, ДИСПР Excel вставит функцию ПРОМЕЖУТОЧНЫЕ. ИТОГИ с необходимыми параметрами, а если выбирать «Другие функции» то вставлена будет именно та функция, которую выбрали. По этому в строке итогов в трех полях будет функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ, а в одном поле функция СЧЁТЕСЛИ.

Формулы в таблице

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

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

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

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

Обратите внимание на некоторые особенности ввода формул, мы вводили формулу используя имя столбца таблицы, а не имя столбца листа. На самом деле вводя формулу в ячейке L2, мы могли написать формулу как =F2*G2. Такой вариант тоже допустим, и такая формула так же будет распространена на весь столбец таблицы, но тогда будет теряться удобство работы с таблицей. Формула в которой отражается реальное имя столбца более наглядна чем формула со ссылками. В некоторых формулах можно встретить обозначение столбца как [@UnitPrice] в данном случае символ @ обозначает – «эта строка» и фактический может опускаться при вводе формул.

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

Ссылки на таблицы и данные таблицы

Ссылаться на таблицы и на данные таблицы из любого другого места рабочей книги так же просто как добавлять формулу в саму таблицу. Для этого будет использоваться полное имя таблицы или столбца данных. Таблица – универсальный и хороший источник данных для других таблиц и отчетов. Например, если бы у нас таблица содержала только числовые значения, как например результаты каких то наблюдений за показателями, разделенных на несколько столбцов, то что бы просчитать среднее значение всех показаний, достаточно вне таблицы написать формулу =СРЗНАЧ(Таблица1). Где «Таблица1» имя таблицы, в которой содержатся значения, в данном случае мы ссылались бы на всю таблицу целиком. Хотя на самом деле для ссылки в формулах на числовые значения не обязательно что бы таблица содержала только числа. Просто любые не числовые значения будут пропускаться.

Ссылка на столбец таблицы выглядит как Имя_таблицы[имя_столбца]. Так что бы вне таблицы получить сумму всех значений поля SalesAmount можно написать формулу =СУММ(Таблица1[SalesAmount]). То есть что бы сослаться на весь столбец таблицы достаточно указать имя таблицы и имя столбца в квадратных скобках. Такой подход не отменяет стандартных возможностей ссылки на значения таблицы, можно использовать как имя ячейки, например А10 так и диапазон, например А10:С15.

Удобство работы с именами вместо абсолютных ссылок заключается в том, что Вам нет необходимости заботиться о положении данных в таблице, о количестве строк таблицы или том где таблица находится. Ссылка в формулах по именам всегда будет давать верный результат. Укажете имя таблицы, Excel сам найдет ее в рабочей книге, укажете имя столбца Excel сам посчитает, сколько строк в таблице и включит все строки в формулу. Ведь на сколько лучше выглядит формула =СУММА(Таблица1[Сумма_продаж]) чем формула =СУММА(А10:А40). Если Вы не видите, данные Вы не можете точно сказать на что указывает диапазон А10:А40, это тем более может быть не известно другому специалисту, который может работать с книгой вместо Вас, но даже не это главное, если количество строк в таблице изменится то первая формула будет давать правильный результат, а вторую необходимо будет исправить, указав верный диапазон.

Как создать таблицу в Excel

Автор
Амир М. Бохлули

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

Таблицы Excel

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

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

Что такое таблицы в Excel?

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

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

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

Таблицы Excel похожи на таблицы данных Excel по названию, но они очень разные. Таблицы данных являются частью набора инструментов Excel для анализа «что, если». Вы можете узнать больше о таблицах данных в нашей статье о том, как таблица данных в Excel позволяет сравнивать результаты.

Как создать таблицу в Excel

Несмотря на свою важность, создать таблицу в Excel очень просто. В этом примере у нас есть продажи, которые несколько сотрудников сделали за два квартала. Цель состоит в том, чтобы отформатировать данные в виде таблицы Excel, а затем рассчитать общий объем продаж с помощью функции СУММ в Excel со ссылками на структуру.

  1. Выберите диапазон ячеек. В данном примере это A1:D11 .
  2. На вкладке Главная нажмите Форматировать как таблицу в разделе Стили .
  3. Выберите стиль для своей таблицы. Обратите внимание, что если ваши ячейки уже имеют стили, то стиль таблицы будет игнорироваться.
  4. Проверить В моих таблицах есть заголовки , если вы уже выводили заголовки. Если нет, вы можете снять этот флажок, чтобы Excel автоматически добавлял заголовки. Мы собираемся держать это под контролем.
  5. Нажмите OK .

Вуаля! У вас есть таблица Excel! Теперь вы можете использовать заголовки для сортировки и фильтрации данных любым удобным для вас способом. Однако наша работа здесь еще не сделана. Пришло время создать формулу для расчета общего объема продаж для каждого сотрудника:

  1. Выберите первую ячейку, в которой вы хотите отобразить результаты формулы. В этом примере это будет ячейка D2 .
  2. В строке формул введите следующую формулу: =СУММ(Таблица1[@[Продажи за 1 квартал]:[Продажи за 2 квартал]]) Лучше ввести формулу, содержащую ссылки на структуру, а не вставлять ее, поскольку заголовки уникальны. Как только вы введете знак at после скобки, Excel предложит вам доступные заголовки в Table1 . В этой формуле мы вызвали функцию SUM и сделали структурные ссылки на столбцы Продажи за 1 квартал и Продажи за 2 квартал . В результате эта формула суммирует числа в этих двух столбцах.
  3. Нажмите Введите .

Как только вы нажмете Enter, Excel мгновенно просуммирует столбцы и отобразит результаты. Обратите внимание, что поскольку вы использовали ссылочную структуру, все ячейки заполнялись автоматически.

Таблицы Excel упрощают анализ

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

Таблицы

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

Как создавать таблицы в Excel (простой учебник)

Создать таблицу | Сортировать | Фильтр | Итоговая строка | Имя таблицы | AutoExpansion

Освойте Таблицы Excel и быстро и легко проанализируйте свои данные. Узнайте, как создать таблицу, отсортировать таблицу, отфильтровать таблицу и многое другое.

Создать таблицу

Чтобы создать таблицу , выполните следующие шаги.

1. Щелкните любую ячейку внутри набора данных.

2. На вкладке Вставка в группе Таблицы нажмите Таблица (или просто нажмите Ctrl+T).

Появится диалоговое окно «Создать таблицу».

3. Excel автоматически выбирает данные за вас. Установите флажок «В моей таблице есть заголовки» и нажмите «ОК».

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

Сортировка

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

1. Сначала щелкните стрелку рядом с пунктом «Продажи» и выберите «Сортировка от меньшего к большему».

2. Затем щелкните стрелку рядом с фамилией и выберите Сортировка от А до Я.

Результат.

Фильтр

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

1. Щелкните стрелку рядом с полем Страна и отметьте только США.

Результат.

Итоговая строка

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

1. Сначала выберите ячейку внутри таблицы. Затем на вкладке «Конструктор таблицы» в группе «Параметры стиля таблицы» установите флажок «Строка итогов» (или просто нажмите Ctrl + Shift + T).

Результат.

2. Щелкните любую ячейку в последней строке, чтобы вычислить итог (среднее, количество, максимум, минимум, сумма и т. д.) столбца. Например, вычислите сумму столбца Продажи.

Примечание. В строке формул посмотрите, как Excel использует функцию ПРОМЕЖУТОЧНЫЙ ИТОГ для вычисления суммы. 109 — это аргумент для суммы, если вы используете функцию ПРОМЕЖУТОЧНЫЙ ИТОГ. Excel использует эту функцию (а не стандартную функцию СУММ) для правильного расчета итоговых значений отфильтрованных таблиц.

Имя таблицы

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

1. Сначала выберите ячейку внутри таблицы. Далее на вкладке Table Design в группе Properties видим, что Table1 — это имя этой таблицы.

2. Приведенная ниже формула относится к таблице 1 и подсчитывает общее количество записей.

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

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