Как удалить формулу из ячейки в excel: Как убрать формулу в Экселе

Содержание

Как убрать формулу в Экселе

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

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

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

Метод 1: использование параметров вставки

Этот метод самый простой, им может воспользоваться даже новичок. Нужно просто выполнить следующие действия:

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

Метод 2: применение специальной вставки

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

Снова выделяем диапазон, который нам нужно скопировать. Давайте в этом случае воспользуемся кнопкой копирования на панели инструментов. В качестве диапазона будет уже использоваться вся таблица, поскольку ее заголовки содержат сложное форматирование, которое нам нужно скопировать.
6
Следующие действия похожи. Нужно перейти в ячейку, в которой будет находиться таблица без формул. Вернее, в верхнюю левую ячейку, поэтому нужно убедиться в том, что на месте будущей таблицы нет никаких лишних значений. Делаем правый клик мыши по ней и находим опцию «Специальная вставка». Рядом с ней есть значок треугольника, который своей вершиной направлен вправо. Если по нему нажать, появится еще одна панель, где нам нужно найти группу «Вставить значения» и выбрать кнопку, выделенную красным цветом на этом скриншоте.
7
В результате получается такая же таблица, которая в изначально скопированном фрагменте, только вместо формулы уже там числятся значения.
8

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

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

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

Осуществите копирование необходимого диапазона любым из вышеперечисленных методов. Мы для наглядности сделаем правый клик мыши и выберем опцию «Копировать» там.
9
Аналогично предыдущему методу, нам нужно вставить область, которую мы ранее скопировали, в новое место. И при этом оставить исходное форматирование. Далее нам нужно вставить эту таблицу ниже.
10
После этого осуществляем переход в самую верхнюю левую ячейку таблицы, которая была первоначально (или же выделяем такой же диапазон, который был в шаге 1), после чего вызываем контекстное меню и выбираем вставку «Значений».
11
После того, как получилось полностью скопировать нужные ячейки без сохранения формул, но с теми же значениями, нужно удалить дубликат. Чтобы это сделать, необходимо выделить диапазон данных, от которого надо избавиться, после чего нажать по нему правой кнопкой мыши и кликнуть по пункту «Удалить».
12
Далее появится маленькое окошко, в котором следует выбрать пункт «строку» и подтвердить удаление нажатием клавиши «ОК».
13
Можно выбирать и другой пункт. Например, «ячейки, со сдвигом влево» используется для удаления определенного количества ячеек, которые находятся по левую часть при условии, что справа никаких значений не указано.

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

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

Какие же действия предпринять, если нет желания копировать таблицу в другое место вообще? Это довольно нелегкий метод. Его основной недостаток заключается в том, что при ошибках можно значительно испортить исходные данные. Конечно, можно восстановить их с помощью комбинации Ctrl + Z, но переделывать в случае чего будет труднее. Собственно, сам способ следующий:

Выделяем ячейку или диапазон, который нам необходимо очистить от формул, после чего копируем их любым из приведенных выше методов. Можете выбрать тот, который больше нравится. Мы же воспользуемся способом, подразумевающим использование кнопки на панели инструментов во вкладке «Главная».
14
Не снимаем выделение из скопированной области, и параллельно делаем правый клик мыши по ней, а потом выбираем пункт «Значения» в группе «Параметры вставки».
15

В результате, конкретные значения автоматически вставляются в нужные ячейки.
16
Если в ячейке при этом было какое-то форматирование, то нужно воспользоваться опцией «Специальная вставка».

Метод 5: использование макроса

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

Для этого нужно выполнить следующую последовательность действий:

Нажать на «Файл».
17
Появится окно, в котором в меню, расположенном слева, ищем пункт «Параметры» и выбираем его.
18
Там будет пункт «Настроить ленту», и справа окна нужно поставить флажок у пункта «Разработчик».
19

Для того, чтобы написать макрос, нужно выполнить следующие шаги:

Открыть вкладку «Разработчик», где перейти в редактор Visual Basic путем нажатия одноименной кнопки.
20
Далее нам нужно выбрать правильный лист, после чего нажать на кнопку «View Code». Более простой вариант – нажать два раза подряд быстро левой кнопкой мыши по нужному листу. После этого открывается редактор макросов.
21

Затем вставляется такой код в поле редактора.

Sub Удаление_формул()

Selection.Value = Selection.Value

End Sub

Такого небольшого количества строк оказалось вполне достаточно, чтобы убрать формулы в выбранном диапазоне. Затем нужно выбрать ту область, которая нам нужна и кликнуть по кнопке «Макросы». Ее можно найти рядом с редактором Visual Basic. Появляется окошко выбора сохраненных подпрограмм, в котором нужно найти необходимый сценарий и нажать «Выполнить».

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

Метод 6: удаление и формулы, и результата

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

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

После этого все данные будут стерты.

Выводы

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

Оцените качество статьи. Нам важно ваше мнение:

Как очистить ячейку от формулы в excel

Работа с формулами в Экселе позволяет значительно облегчить и автоматизировать различные вычисления. Вместе с тем, далеко не всегда нужно, чтобы результат был привязан к выражению. Например, при изменении значений в связанных ячейках, изменятся и итоговые данные, а в некоторых случаях это не нужно. Кроме того, при переносе скопированной таблицы с формулами в другую область значения могут «потеряться». Ещё одним поводом их спрятать может служить ситуация, когда вы не хотите, чтобы другие лица видели, как проводятся в таблице расчеты. Давайте выясним, какими способами можно удалить формулу в ячейках, оставив только результат вычислений.

Процедура удаления

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

Способ 1: копирование значений через параметры вставки

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

«Главная»

«Копировать»

«Буфер обмена»

Выделяем ячейку, которая будет верхней левой ячейкой вставляемой таблицы. Выполняем щелчок по ней правой кнопкой мышки. Будет активировано контекстное меню. В блоке «Параметры вставки» останавливаем выбор на пункте «Значения». Он представлен в виде пиктограммы с изображением цифр «123».

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

Способ 2: копирование специальной вставкой

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

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

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

Способ 3: удаление формулы из исходной таблицы

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

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

Выделяем первоначальный диапазон. Щелкаем по нему правой кнопкой мыши. В контекстном списке в группе «Параметры вставки» выбираем пункт «Значения».

После того, как данные были вставлены, можно удалить транзитный диапазон. Выделяем его. Вызываем контекстное меню кликом правой кнопки мыши. Выбираем в нем пункт «Удалить…».

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

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

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

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

«Копировать»

Ctrl+C

Затем, не снимая выделения, кликаем правой кнопкой мыши. Запускается контекстное меню. В блоке «Параметры вставки» жмем на пиктограмму «Значения».

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

Способ 5: использование макроса

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

«Разработчик»

«Visual Basic»

«Код»

Запускается редактор макросов. Вставляем в него указанный ниже код:

Sub Удаление_формул()
Selection.Value = Selection.Value
End Sub

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

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

Открывается окно запуска макросов. Ищем в нем элемент под названием «Удаление_формул», выделяем его и жмем на кнопку «Выполнить».

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

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

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

«Очистить содержимое»

Delete

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

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

Отблагодарите автора, поделитесь статьей в социальных сетях.

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

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

Выделите ячейку или диапазон ячеек, содержащих формулу.

Нажмите клавишу DELETE.

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

Для этого нужно скопировать формулу, а затем вставить ее в ту же ячейку с помощью параметра «вставить значения».

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

Щелкните ячейку в формуле массива.

На вкладке Главная в группе Редактирование нажмите кнопку Найти и выделить, а затем выберите команду Перейти.

Нажмите кнопку Дополнительный.

Нажмите кнопку Текущий массив.

На вкладке Главная в группе буфер обмена нажмите кнопку Копировать .

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

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

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

Щелкните ячейку в формуле массива.

Нажмите кнопку Дополнительный.

Нажмите кнопку Текущий массив.

Нажмите клавишу DELETE.

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

Выделите ячейку или диапазон ячеек, содержащих формулу.

Щелкните главная > Копировать (или нажмите клавиши CTRL + C).

На вкладке главная > щелкните стрелку под кнопкой Вставить > Вставить значения.

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

Примечание: Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Была ли информация полезной? Для удобства также приводим ссылку на оригинал (на английском языке).

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Заключение

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

Копирование, перемещение и удаление содержимого ячеек в Numbers на Mac

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

Выберите ячейки, которые нужно скопировать или переместить.
Выполните одно из описанных ниже действий.
- Перемещение данных. После выбора ячеек нажмите и удерживайте их, пока ячейки не поднимутся из таблицы, затем перетяните их, поместив в другое место в таблице. Существующие данные будут замены новыми данными.
- Вставка и перезапись существующих данных. Выберите «Правка» > «Скопировать» (меню «Правка» расположено у верхнего края экрана). Выберите верхнюю левую ячейку, в которую нужно вставить данные (или выберите область того же размера, что и ячейки, которые Вы вставляете), затем выберите «Правка» > «Вставить».
  Если в диапазоне данных содержатся формулы, но Вы хотите вставить только результаты вычислений, выберите «Вставить результаты формулы».
- Вставить без перезаписи. Выберите «Правка» > «Скопировать», выберите ячейки назначения, затем выберите «Вставка» > «Скопированные строки» или «Вставка» > «Скопированные столбцы» (меню «Вставка» расположено у верхнего края экрана). Новые строки или столбцы добавляются в скопированные ячейки.
- Вставка стиля ячейки. Выберите «Формат» > «Скопировать стиль» (меню «Формат» расположено у верхнего края экрана), выберите ячейки, в которые требуется вставить стиль, затем выберите «Формат» > «Вставить стиль».
- Вставка содержимого ячейки без стиля. Выберите «Правка» > «Скопировать», выберите ячейки для вставки, затем выберите «Правка» > «Вставить и согласовать стиль». Вставленные ячейки принимают форматирование нового местоположения.
- Вставить за пределами имеющейся таблицы для создания новой таблицы. Перетяните ячейки за пределы таблицы. Будет создана новая таблица со скопированными ячейками.

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

Как убрать формулу в Экселе

Процедура удаления

Способ 1: копирование значений через параметры вставки

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

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

Выделяем ячейку, которая будет верхней левой ячейкой вставляемой таблицы. Выполняем щелчок по ней правой кнопкой мышки. Будет активировано контекстное меню. В блоке «Параметры вставки» останавливаем выбор на пункте «Значения». Он представлен в виде пиктограммы с изображением цифр «123».

Способ 2: копирование специальной вставкой

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

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

Способ 3: удаление формулы из исходной таблицы

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

Выделяем первоначальный диапазон. Щелкаем по нему правой кнопкой мыши. В контекстном списке в группе «Параметры вставки» выбираем пункт «Значения».

После того, как данные были вставлены, можно удалить транзитный диапазон. Выделяем его. Вызываем контекстное меню кликом правой кнопки мыши. Выбираем в нем пункт «Удалить…».

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

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

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

Затем, не снимая выделения, кликаем правой кнопкой мыши. Запускается контекстное меню. В блоке «Параметры вставки» жмем на пиктограмму «Значения».

Способ 5: использование макроса

Переходим во вкладку «Разработчик». Кликаем по кнопке «Visual Basic», размещенной на ленте в блоке инструментов «Код».

Запускается редактор макросов. Вставляем в него указанный ниже код:
Sub Удаление_формул() Selection.Value = Selection.Value End Sub
После этого закрываем окно редактора стандартным способом, нажав на кнопку в правом верхнем углу.

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

Открывается окно запуска макросов. Ищем в нем элемент под названием «Удаление_формул», выделяем его и жмем на кнопку «Выполнить».

Урок: Как включить или отключить макросы в Excel

Урок: Как создать макрос в Excel

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

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

Выделяем диапазон, в котором размещены формулы. Кликаем правой кнопкой мыши. В контекстном меню останавливаем выбор на пункте «Очистить содержимое». Если вы не хотите вызывать меню, то можно просто после выделения нажать клавишу Delete на клавиатуре.

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

Мы рады, что смогли помочь Вам в решении проблемы.
Опишите, что у вас не получилось. Наши специалисты постараются ответить максимально быстро.

Помогла ли вам эта статья?

ДА НЕТ

Как разорвать связи в Excel

Описание проблемы

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

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

При нарушении связи, ячейки со ссылками на другие книги будут содержать ошибки #ССЫЛКА.

Как разорвать связь

Один из способов решения данной проблемы — разрыв связи. Если в файле только одна связь, то сделать это довольно просто:

Перейдите на вкладку Данные.
Выберите команду Изменить связи в разделе Подключения.
Нажмите Разорвать связь.

ВАЖНО! При разрыве связи все формулы ссылающиеся на книгу-источник будут преобразованы в значения! Отмена данной операции невозможна!

Как разорвать связь со всеми книгами

Для удобства, можно воспользоваться макросом, который разорвет связи со всеми книгами. Макрос входит в состав надстройки VBA-Excel. Чтобы им воспользоваться необходимо:

Перейти на вкладку VBA-Excel.
В меню Связи выбрать команду Разорвать все связи.

Код на VBA

Код макроса удаляющего все связи с книгой представлен ниже. Можете скопировать его в свой проект.


Sub UnlinkWorkBooks()
    Dim WbLinks
    Dim i As Long
    Select Case MsgBox("Все ссылки на другие книги будут удалены из этого файла, а формулы, ссылающиеся на другие книги будут заменены на значения." & vbCrLf & "Вы уверены, что хотите продолжить?", 36, "Разорвать связь?")
    Case 7 ' Нет
        Exit Sub
    End Select
    WbLinks = ActiveWorkbook.LinkSources(Type:=xlLinkTypeExcelLinks)
    If Not IsEmpty(WbLinks) Then
        For i = 1 To UBound(WbLinks)
            ActiveWorkbook.BreakLink Name:=WbLinks(i), Type:=xlLinkTypeExcelLinks
        Next
    Else
        MsgBox "В данном файле отсутствуют ссылки на другие книги.", 64, "Связи с другими книгами"
    End If
End Sub

Как разорваться связи только в выделенном диапазоне

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

Выделите диапазон данных.
Перейдите на вкладку VBA-Excel (доступна после установки).
В меню Связи выберите команду Разорвать связи в выделенных ячейках.

Excel. Перемещение формул без изменения относительных ссылок

На днях дочь обратилась с проблемой. Она построила сложную таблицу в Excel с большим числом формул, основанных на относительных ссылках, и возникла потребность скопировать эти формулы в новую область листа с сохранением ссылок на те же ячейки, что и исходные формулы (подробнее о типе ссылок см. Относительные, абсолютные и смешанные ссылки на ячейки в Excel). «Зайти» во все ячейки с формулами и изменить ссылки на абсолютные было затруднительно, так как таких ячеек было больше ста…

К сожалению, стандартные средства Excel не позволяют выполнить подобное копирование. Что вообще-то говоря, удивительно! Попробуйте, например, перенести формулу =В1+С1, хранящуюся в ячейке D1, в ячейку D4 (рис. 1). Если выполнить копирование с помощью специальной вставки и опции вставить формулы, в ячейке D4 обнаружите формулу =В4+С4.

Рис. 1. Специальная вставка; чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке

Скачать заметку в формате Word или pdf, примеры в формате Excel

Решение пришло из моего прошлого опыта: когда я был верстальщиком, я очень широко использовал контекстные замены, и был мастером в этом искусстве. 🙂

Выделите диапазон ячеек, который хотите скопировать. В нашем примере это С4:С13 (область 1 на рис. 2), и выберите команду Главная → Найти и выделить → Заменить (область 2 на рис. 2), или нажмите Ctrl + H (английская H).

Рис. 2. Найти и заменить

В открывшемся диалоговом окне «Найти и заменить» (рис. 3) в поле «Найти» введите знак = (с него начинаются все формулы). В поле «Заменить на» введите знаки && или любой иной символ который, как вы уверены, не используется ни в одной из формул. Нажмите «Заменить все».

Рис. 3. Заменить знак = на знаки &&

Во всех формулах на рабочем листе вместо знака равенства теперь стоит && (рис. 4).

Рис. 4. После замены

Скопируйте ячейки С4:С13 в требуемое место, и выполните обратную замену всех && на =. И первоначальные, и новые формулы ссылаются на одни и те же ячейки (рис. 5), причем формулы используют относительные ссылки, то есть их можно «протягивать».

Рис. 5. Формулы удалось перенести

Дополнение от 1 октября 2016

Еще один вариант решения проблемы можно найти у Джона Уокенбаха. [1] Переключите Excel в режим просмотра формул, пройдя по меню Формулы –> Зависимости формул –> Показывать формулы (рис. 6). Выделите диапазон для копирования. В данном примере – С4:С13. Скопируйте его в буфер. Откройте текстовый редактор, например, Word или Блокнот. Вставьте скопированные данные. Выделите весь текст, и снова скопируйте его в буфер. Вернитесь в Excel и активизируйте верхнюю левую ячейку диапазона, в который хотите вставить ваши формулы. Убедитесь, что лист, на который копируются данные, находится в режиме просмотра формул. Вставьте формулы. Выйдете из режима показа формул, повторно пройдя по меню пройдя по меню Формулы –> Зависимости формул –> Показывать формулы. Формулы в целевом диапазоне будут ссылаться на те же ячейки, что и в исходном.

Рис. 6. Режим Показывать формулы

Примечание. В некоторых случаях операция вставки в Excel выполняется с ошибкой и программа разбивает формулу на две и более ячейки. Если так происходит, то, возможно, недавно вы пользовались функцией Excel Текст по столбцам и приложение напоминает вам, как данные разбирались при последнем сеансе. Откройте Мастер распределения текста по столбцам и измените параметры. Выполните команду Данные –> Работа с данными –> Текст по столбцам. В диалоговом окне Мастера распределения текста по столбцам выберите С разделителями и нажмите Далее. Снимите флажки со всех вариантов разделителей, кроме варианта знак табуляции, и нажмите Отмена. После этих изменений формулы будут вставляться правильно.

[1] Джон Уокенбах. Excel 2013. Трюки и советы. – СПб.: Питер, 2014. – С. 144, 145.

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

Рассмотрим ситуацию копирования листа с формулами в другую книгу (Другой документ) .

Существует файл с перечнем городов России и количеством людей в каждом городе, которым положена некая компенсация. Размер компенсации по каждому городу записан во второй вкладке документа (на втором листе). В столбце «бюджет компенсации» перемножается количество людей, которым положена компенсация на размер компенсации для каждого конкретного города.

Копирование листа в другой документ.

Произведем копирование таблицы в другой документ. Для этого необходимо открыть оба документа.

Далее в документе с таблицей кликнуть правой кнопкой мыши по ярлыку с названием листа и в появившемся контекстном меню выбрать позицию «Переместить или скопировать…».

В появившемся меню указать документ, в который будет скопирован лист. Нажать кнопку «ОК».

При копировании листа с расчетами в другую книгу (другой документ), формулы ссылаются на старый документ.

Как быстро избавится от ссылок на старый документ в формулах.

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

Чтобы избавиться от ссылок на старый документ в формулах на листе «Эксель», можно воспользоваться опцией поиска.

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

Вызвать поиск можно нажав сочетание клавиш «ctrl+F» (в русской раскладке «ctrl+А» ), либо воспользоваться пиктограммой «Найти и выделить…» на главной вкладке.

В появившейся форме для поиска нужно выбрать вкладку «Заменить». На вкладке «Заменить» есть 2 поля. Первое поле — то значение, которое ищем, второе поле – то, на что меняем.

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

3 быстрых способа удаления формулы в Excel

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

Это очень просто сделать! Продолжайте читать, чтобы узнать больше.

Удаление формул и сохранение данных в Excel может быть необходимо по следующим причинам:

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

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

Вот наш образец рабочего листа , который содержит следующие формулы в столбце E :

Вместо отображения формулы = ЗАМЕНИТЬ (D9, 1, НАЙТИ («-», D9), «») в адресной строке вам нужно значение i.е. будет отображен новый телефонный номер .

Это можно легко сделать в Excel, используя любой из двух методов:

Удалить формулу с помощью правой кнопки мыши

Посмотрите ее на YouTube и поставьте лайк!

Загрузите эту книгу Excel и следуйте инструкциям, чтобы понять, как Excel удалить формулу с рабочего листа:

ШАГ 1: Выберите все ячейки с формулами:

ШАГ 2: Щелкните правой кнопкой мыши и выберите Копировать:

ШАГ 3: Еще раз щелкните правой кнопкой мыши и выберите Вставить значения :

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

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

ШАГ 1: Выберите все ячейки с формулами:

ШАГ 2: Перейдите на вкладку «Главная» в группе Буфер обмена , щелкните стрелку под Вставьте и нажмите «Вставить значения».

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

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

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

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

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

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

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

ШАГ 1: Нажмите Ctrl + G , чтобы открыть диалоговое окно «Перейти», а затем выберите Special .

ШАГ 2: Выберите Формулы для списка и нажмите OK.

Будут выделены все ячейки, содержащие формулы.

ШАГ 3: Выделите эти ячейки цветом.

Ячейки будут выделены желтым цветом!

ШАГ 4: Нажмите Ctrl + C , чтобы скопировать ячейки.

ШАГ 5: Нажмите Alt + E + S , чтобы открыть диалоговое окно «Специальная вставка».

ШАГ 6: Нажмите V , чтобы выбрать значения, и нажмите OK.

Формула заменена значением.

ШАГ 7: Повторите те же шаги для других выделенных ячеек.

Заключение

Существует 3 способа удаления формулы в Excel с помощью щелчка правой кнопкой мыши, вкладки «Главная» или сочетаний клавиш. Это поможет вам удалить формулу без удаления данных.

Вы можете легко уменьшить размер файла или скрыть формулу от получателей!

ПОЛЕЗНЫЕ РЕСУРСЫ:

Не забудьте загрузить наш БЕСПЛАТНЫЙ PDF-файл на клавиатуре 333 Excel. Сочетания клавиш здесь:

Вы можете узнать больше о том, как использовать Excel, просмотрев БЕСПЛАТНЫЙ веб-семинар по Excel на Формулы, сводные таблицы, Power Query, макросы и и VBA !

Об авторе

Брайан

Брайан — автор бестселлеров из серии 101 Excel в мягкой обложке.

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

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

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

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

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

Обычно это объясняется двумя причинами:

кто-то забыл фактически заменить формулы на значения; или
кто-то забыл НАУЧИТЬ кого-нибудь менять формулы на значения.

Давайте вместе рассмотрим пример.

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

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

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

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

Но хотим ли мы поделиться этой информацией с нашим клиентом ?

В большинстве случаев нет, мы не делаем.

Поэтому имеет смысл заменить формулы фактическим РЕЗУЛЬТАТОМ вычислений, значением, которое вы можете увидеть в таблице.

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

Некоторые называют это «Вставить значения».Другие называют это Paste 123. И некоторые называют это Paste Special.

Все, что вам нужно помнить, это то, что процесс, который я собираюсь вам провести, удаляет КОНФИДЕНЦИАЛЬНУЮ информацию, которую мы предпочли бы не раскрывать, и заменяет формулу ТОЛЬКО числом.

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

Замените формулы на значения
Сначала выполните F файл , Сохранить как и сохраните его как копию исходного шаблона. Возможно, используйте имя клиента в имени файла, чтобы его было легко идентифицировать.
Теперь выберите ячейки, которые в настоящее время содержат ваши формулы, в которых есть конфиденциальные вычисления. В этом случае это будут ячейки с E15 по E17.
Теперь Скопируйте формулу (нажмите Ctrl + C или щелкните правой кнопкой мыши выделенную область, а затем щелкните левой кнопкой мыши Копировать).
Теперь оставьте те же ячейки по-прежнему выделенными, а затем щелкните правой кнопкой мыши, а затем щелкните левой кнопкой мыши, чтобы выбрать Вставить 123.
Теперь формулы будут заменены только числами. И теперь вы можете конфиденциально поделиться файлом со своим клиентом, зная, что он может делать с ним все, что угодно, И они не могут видеть эти формулы в фоновом режиме.
Я могу вас немного напугать, я знаю, и мне нужно, потому что это действительно, очень важно .
Вы знаете, что нам нужно хранить в тайне так много вещей, о которых мы не рассказываем другим людям.
Итак, если вы думаете, что раньше , вы отправляете электронную таблицу Excel, и многие люди требуют от вас этого сейчас, клиенты просят об этом.
Прежде чем отправить его, если вы думаете, что там есть вещи, я бы предпочел, чтобы они не видели. Там есть формулы, которые я бы предпочел, чтобы они не видели, используйте Copy, Paste 123, Paste Values .
PDF или распечатанная копия — это всегда хорошая идея, если вам не нужно давать кому-то копию Excel, но если вам действительно нужно дать им электронную копию, ВСЕГДА замедляйтесь и спросите себя …
«Мне нужно использовать Копировать, Вставить значения ? ». 🙂
Как быстро преобразовать формулы в значения в Excel 2016, 2013 и 2010
Вот хорошие советы, которые сэкономят ваше время — 2 самых быстрых способа заменить формулы в ячейках Excel их значениями. Обе подсказки работают в Excel 2016, 2013 и 2010.
У вас могут быть разные причины преобразования формул в значения:
Чтобы иметь возможность быстро вставлять значения в другие книги или листы, не тратя время на копирование / вставку.
Чтобы ваши исходные формулы оставались неизвестными, когда вы отправляете книгу другому человеку (например, ваша розничная наценка к оптовой цене).
Для предотвращения изменения результата при изменении чисел в связывающих ячейках.
Сохраните результат формулы rand ().
Если в вашей книге много сложных формул, из-за которых пересчет будет очень медленным. И вы не можете переключить опцию «Расчет рабочей книги» в ручной режим.
Преобразование формул в значения с помощью ярлыков Excel
Замена формул на значения в пару кликов мышкой
Преобразование формул в значения с помощью ярлыков Excel
Предположим, у вас есть формула для извлечения доменных имен из URL-адресов.
Вам нужно заменить его результаты на значения.
Просто выполните следующие простые шаги:
Выделите все ячейки с формулами, которые нужно преобразовать.
Нажмите Ctrl + C или Ctrl + Ins, чтобы скопировать формулы и их результаты в буфер обмена.
Нажмите Shift + F10, а затем V, чтобы вставить обратно в ячейки Excel только значения.
Shift + F10 + V — это самый короткий способ использования диалогового окна Excel « Вставить специальные — только значения ».
Вот и все! Если этот способ все еще недостаточно быстр для вас, обратите внимание на следующий совет.
У вас когда-нибудь было ощущение, что некоторые рутинные задачи в Excel, которые можно выполнить за несколько кликов, отнимают у вас слишком много времени? Если да, то добро пожаловать в наш Ultimate Suite for Excel.
С помощью этой коллекции из более чем 60 инструментов, позволяющих сэкономить время, вы можете быстро удалить все пустые ячейки, строки и столбцы; перемещать столбцы путем перетаскивания; подсчет и суммирование по цвету, фильтрация по выбранному значению и многое другое.
Если в Excel установлен Ultimate Suite, выполните следующие действия, чтобы заставить его работать:
Выделите все ячейки с формулами, которые вы хотите заменить вычисленными значениями.
Перейдите на вкладку Ablebits Tools > группа Utilities .
Щелкните Преобразовать формулы > в значение .
Готово!
Я рекомендую вам изучить другие возможности нашего Ultimate Suite. Могу вас заверить, что это сэкономит 4-5 минут на одной задаче Excel, 5-10 минут на другой задаче, а к концу дня это сэкономит вам час или больше. Сколько стоит час вашей работы? 🙂
Вас также может заинтересовать
Функция CLEAN — формула, примеры, как использовать CLEAN в Excel
Что такое функция CLEAN?
Функция ОЧИСТКА относится к текстовым функциям Excel.Функция удаляет непечатаемые символы из заданного текста. В качестве финансового аналитика Описание работы финансового аналитика В описании должности финансового аналитика ниже приводится типичный пример всех навыков, образования и опыта, необходимых для приема на работу аналитика в банке, учреждении или корпорации. Выполняйте финансовое прогнозирование, отчетность и отслеживание операционных показателей, анализируйте финансовые данные, создавайте финансовые модели, мы часто импортируем данные из различных источников, а функция CLEAN может помочь удалить непечатаемые символы из предоставленной текстовой строки.Это также полезно при удалении разрывов строк.
Функция была введена для удаления непечатаемых символов, представленных числами от 0 до 31 в 7-битном коде ASCII, которые часто встречаются в начале данных, импортируемых в Excel из других приложений.
Формула
= CLEAN (текст)
Функция CLEAN включает следующий аргумент:
Text (обязательный аргумент) — информация рабочего листа, из которого мы собираемся удалить непечатаемые символы .
Помните, что функция CLEAN отличается от функции TRIM, поскольку первая удаляет непечатаемые символы, представленные числовыми кодами ASCII от 0 до 31. С другой стороны, функция TRIM помогает избавиться от дополнительных пробелов, которые представлены числовой код ASCII 32.
Как использовать функцию ОЧИСТКА в Excel?
Функция ОЧИСТКА — это встроенная функция, которую можно использовать как функцию рабочего листа в Excel. Как функцию рабочего листа, его можно ввести как часть формулы в ячейку рабочего листа.
Чтобы понять использование функции, давайте рассмотрим несколько примеров:
Пример 1
Предположим, мы импортировали некоторые данные в следующем формате:
Используя функцию CLEAN, мы можем удалить нежелательные символы, как показано ниже:
Во всех приведенных выше сценариях функция удаляла непечатаемые символы. Также были удалены непечатаемые символы CHAR (15) и CHAR (12).
Если мы хотим быстро применить эту формулу к большому количеству данных, мы можем выполнить следующие шаги:
Предполагая, что данные указаны в столбцах от A1 до A1000, затем щелкните правой кнопкой мыши заголовок столбца «B» и выберите «Вставить», чтобы создать новый столбец «B».
Теперь перейдите к B1. Используя клавишу SHIFT, выберите от B1 до B1000. В этом примере, удерживая «Shift», щелкните ячейку «B1000», чтобы выбрать ячейки от «B1» до «B1000».
Теперь введите «= CLEAN (A1)» (исключая кавычки), а затем нажмите «Ctrl-Enter», чтобы применить функцию CLEAN ко всему выделению и очистить каждую точку данных в нашем списке.
Функция будет работать, даже если между данными присутствуют непечатаемые символы. Предположим, нам предоставлены следующие данные: «Ежемесячный отчет»
В таком сценарии мы можем использовать формулу ЧИСТЫЙ (Ежемесячный отчет). Используя функцию CLEAN, мы можем удалить любые непечатаемые данные, присутствующие где-либо между символами. Мы получаем результат, показанный ниже:
Пример 2
Предположим, мы хотим удалить разрывы строк из ячеек, мы можем использовать TRIM с функцией CLEAN, как показано ниже:
В приведенном выше примере данные включают непечатаемые символы, а также разрывы строк.Для очистки и обрезки данных мы использовали формулу = ОБРЕЗАТЬ (CLEAN (текст)), как показано ниже:
Функция CLEAN не позволяет удалить все непечатаемые символы, особенно неразрывный пробел, который может отображаться в Excel как CHAR (160). В таком сценарии, добавляя функцию ЗАМЕНА в формулу, мы можем удалить определенные символы, как показано ниже:
Здесь мы использовали ОБРЕЗАТЬ, ОЧИСТИТЬ и ПОДСТАВИТЬ вместе.
Несколько вещей, которые следует помнить о функции CLEAN
Функция CLEAN выдаст значение с удалением всех непечатаемых символов.
Функция была представлена в Excel 2000 и доступна во всех последующих версиях.
Помните, что в наборе символов Unicode есть дополнительные непечатаемые символы. Сама по себе функция CLEAN не удаляет эти дополнительные непечатаемые символы.
Щелкните здесь, чтобы загрузить образец файла Excel
Дополнительные ресурсы
Спасибо, что прочитали руководство CFI по важным функциям Excel! Потратив время на изучение и освоение этих функций, вы значительно ускорите свой финансовый анализ.Чтобы узнать больше, ознакомьтесь с этими дополнительными ресурсами CFI:
Функции Excel для FinanceExcel for Finance Это руководство по Excel для финансов научит 10 основных формул и функций, которые вы должны знать, чтобы стать отличным финансовым аналитиком в Excel. В этом руководстве есть примеры, скриншоты и пошаговые инструкции. В конце скачайте бесплатный шаблон Excel, который включает в себя все финансовые функции, описанные в учебнике
Курс расширенных формул Excel
Расширенные формулы Excel, которые вы должны знать Расширенные формулы Excel, которые необходимо знать Эти расширенные формулы Excel очень важно знать и потребуют вашего финансового анализа навыки на новый уровень.Расширенные функции Excel
Ярлыки Excel для ПК и MacExcel Ярлыки ПК MacExcel Ярлыки — Список наиболее важных и распространенных ярлыков MS Excel для пользователей ПК и Mac, специалистов в области финансов и бухгалтерского учета. Сочетания клавиш ускоряют ваши навыки моделирования и экономят время. Изучите редактирование, форматирование, навигацию, ленту, специальную вставку, манипулирование данными, редактирование формул и ячеек и другие краткие сведения.
Как удалить значения из ячеек, но сохранить свои формулы в Microsoft Excel
Одним из преимуществ работы с электронными таблицами в Microsoft Excel является возможность повторно использовать формулы и применять их в новых ситуациях.В таких случаях вы хотите убедиться, что вы не скопируете по ошибке какие-либо из старых значений ввода, которые могут исказить и испортить результат. Когда вы получаете электронную таблицу от кого-то еще или даже когда вы работаете над одной из ваших таблиц, которая содержит изрядную долю сложности, вы хотите быстро изолировать и удалить все входные значения, которые используются в формулах, которые вы используете. Вместо проверки ячейки таблицы за ячейкой, выполняя изнурительную работу, существует метод быстрого удаления всех значений из электронной таблицы Excel и сохранения только формул.Посмотрим, как это работает:
ПРИМЕЧАНИЕ : Это руководство применимо к настольным версиям Microsoft Excel, которые можно найти в Microsoft Office и Office 365. Оно не применимо к мобильным версиям Excel, таким как бесплатные, на планшетах с Windows 10 или на устройствах. с Android и iOS.
Как удалить значения из ячеек, но сохранить формулы в Microsoft Excel
Мы используем в качестве примера график погашения кредита, предоставленный Microsoft как часть их шаблонов Office, доступных для загрузки.Мы хотим повторно использовать эту таблицу и построить собственное моделирование. Для этого мы хотим убедиться, что в электронной таблице не осталось значений из предыдущих данных, предоставленных Microsoft.
Электронная таблица Microsoft Excel, в которой мы хотим хранить только формулы
Сначала убедитесь, что активной вкладкой на ленте является Домашняя страница . Найдите раздел Editing и там найдите раскрывающееся меню Find & Select . Нажмите здесь. В меню выберите « Перейти к специальному… .”
Выберите Перейти к специальному в Microsoft Excel.
В появившемся всплывающем окне выберите Константы , а затем в разделе Формулы убедитесь, что установлен только флажок Числа .
По окончании щелкните или коснитесь ОК .
Выбирать числа только в Microsoft Excel
Microsoft Excel проанализирует вашу электронную таблицу и автоматически выберет все ячейки, содержащие чисел, только , оставив невыделенными ячейки, содержащие формулы или текст.
Таблица с числами, выбранными только в Microsoft Excel
Для следующего шага убедитесь, что вы не щелкаете и не касаетесь где-либо в электронной таблице, потому что это отменяет ваш текущий выбор. В том же разделе Editing на вкладке Home щелкните или коснитесь кнопки Clear . Кнопка представлена ластиком и, если в окне приложения достаточно места, также появляется слово Очистить . В раскрывающемся меню выберите Очистить содержимое .Или используйте клавиатуру и нажмите Удалить .
Очистить содержимое в Microsoft Excel
Теперь электронная таблица очищена от всех входных чисел, в то время как все формулы сохранены и готовы к использованию в следующей электронной таблице. Теперь вы можете быть уверены, что любые числа, которые используются в этих формулах, введены вами заново, и нет никаких остатков из прошлого.
Вам удалось очистить электронную таблицу?
Изучив это руководство, вы поймете, насколько легко очистить электронную таблицу от старых входных значений и сэкономить время, делая это быстро и точно.Сообщите нам, как это сработало для вас, оставив комментарий ниже. Кроме того, есть ли какие-либо вопросы, связанные с Excel, на которые вы хотите, чтобы мы ответили в наших руководствах?
Формула
для удаления первого или последнего слова из ячейки в Excel

Формула Excel для удаления первого или последнего слова из ячейки.
Вы можете скопировать и вставить приведенные ниже формулы для быстрого исправления, а также узнать, как они работают, если вам интересно.
Разделов:
Удалить первое слово из ячейки
Удалить последнее слово из ячейки
Более универсальные формулы — предотвращение ошибок
Банкноты
Удалить первое слово из ячейки
= ВПРАВО (A1; LEN (A1) -FIND ("", A1))
Результат:
Предполагается, что текст находится в ячейке A1, при необходимости измените его для вашей электронной таблицы.
Некосмические символы
Если в ваших словах используются тире или какой-либо другой разделитель, просто замените «» любым используемым разделителем. Если используется тире, замените его в формуле на «-» .
Пояснение
Здесь работают три функции: ВПРАВО (), ДЛСТР () и НАЙТИ ().
Функция ВПРАВО получает текст из правой части ячейки. Это необходимо, потому что мы извлекаем весь текст, кроме первого слова; это означает, что нам нужно получить все, что находится справа от первого слова в ячейке.
Функции LEN и FIND используются, чтобы сообщить функции RIGHT, сколько символов справа от ячейки мы хотим получить. Функция ВПРАВО извлекает определенное количество символов только справа от ячейки.
Чтобы получить все, кроме первого слова, нам нужно найти пробел, разделяющий первое слово и остальной текст. Используйте функцию НАЙТИ, ища пробел «» , чтобы получить количество символов от левого края ячейки до первого пробела. В приведенном выше примере возвращается 5.Это количество символов, которое мы хотим удалить из ячейки.
Чтобы получить число, которое мы хотим сохранить, нам нужно использовать функцию LEN для подсчета всех символов в ячейке. Результат функции LEN — 12, потому что в ячейке 12 символов.
В приведенной выше формуле мы вычитаем результат НАЙТИ из результата LEN, который равен 12-5, или 7.
Это указывает функции RIGHT получить 7 символов из правой части ячейки, что эффективно удаляет первое слово из ячейки.
Удалить последнее слово из ячейки
= ЛЕВЫЙ (A1, НАЙТИ ("~", ПОДСТАВИТЬ (A1, "", "~", LEN (A1) -LEN (ПОДСТАВИТЬ (A1, "", ""))))) - 1)
Результат:
Предполагается, что текст находится в ячейке A1, при необходимости измените его для вашей электронной таблицы.
Примечание: , если в ваших ячейках может быть символ ~ , измените эту часть формулы на символ, которого нет в ячейках.
Некосмические символы
Если в ваших словах используются тире или какой-либо другой разделитель, просто замените «» любым используемым разделителем.Если используется тире, замените его в формуле на «-» .
Пояснение
В этой формуле используются четыре функции: LEFT (), LEN (), FIND (), SUBSTITUTE ().
Это может немного сбить с толку, но вам не нужно читать этот раздел, чтобы использовать формулу.
Чтобы удалить последнее слово из ячейки, нам нужно извлечь все другие слова в новую ячейку, и тогда эта новая ячейка не будет содержать последнее слово. По сути, мы получаем все слова, которые находятся слева от последнего слова, поэтому мы используем функцию LEFT.
Для этого нам нужно сообщить функции LEFT, сколько символов слева от ячейки мы хотим в новой ячейке, и это число может варьироваться. Сложная часть формулы — это то, что дает нам это число.
НАЙТИ ("~", ПОДСТАВИТЬ (A1, "", "~", LEN (A1) -LEN (ПОДСТАВИТЬ (A1, "", ""))))
Этот фрагмент формулы ЗАМЕНА (A1, «», «») удаляет все пробелы в ячейке, а затем подсчитывает количество оставшихся символов с помощью функции ДСТР. LEN (ПОДСТАВИТЬ (A1, «», «»)) .В приведенном выше примере результат равен 10.
.
Затем он подсчитывает, сколько символов находится в ячейке, когда в ней оставлены пробелы: LEN (A1) . Результат 12.
Затем формула вычитает два вышеуказанных числа, чтобы получить 2 в этом примере. Это число представляет собой общее количество пробелов в ячейке, и это сообщает другой функции ЗАМЕНА, какой пробел следует заменить; мы хотим заменить последний пробел, чтобы его было легче найти.
Используя только что полученное число, функция ЗАМЕНА используется снова для замены последнего пробела в ячейке на ~ , а затем функция НАЙТИ используется для нахождения этого числа ~ , а затем формула знает, сколько символов слева ячейки последний пробел.
Затем формула вычитает 1 из этого числа, поскольку мы не хотим включать последний пробел в новую ячейку.
Теперь у нас есть общее количество символов от левого края ячейки до пробела, который стоит перед последним словом в ячейке, и мы можем использовать функцию LEFT, чтобы вернуть все, кроме последнего слова.
Если вы все еще немного запутались, ознакомьтесь с нашим руководством по функции ЗАМЕНА в Excel
Более универсальные формулы — предотвращение ошибок
Если вы используете одну из приведенных выше формул в списке, и любая из ячеек в этом списке содержит одно слово, появится ошибка.
Чтобы избежать ошибок, мы можем использовать простую функцию ЕСЛИОШИБКА ().
Формула 1:
= ЕСЛИ ОШИБКА (ВПРАВО (A1; LEN (A1) -НАЙТИ ("", A1)); A1)
Формула 2:
= ЕСЛИОШИБКА (ЛЕВО (A1, НАЙТИ ("~", ПОДСТАВИТЬ (A1, "", "~", LEN (A1) -LEN (ПОДСТАВИТЬ (A1, "", "")))) - 1), A1)
Это говорит о том, что в случае ошибки вывести содержимое исходной ячейки. Подобные ошибки обычно возникают, если в ячейке есть только одно слово, поэтому это будет выводить это единственное слово.
Прочтите наше руководство, чтобы узнать больше о функции ЕСЛИОШИБКА в Excel и подавлении ошибок в целом.
Банкноты
Эти формулы могут сбивать с толку. Я рекомендую просто скопировать / вставить их в вашу электронную таблицу и при необходимости изменить ссылки на ячейки.
Чтобы сделать новый диапазон значений текстом вместо формул, следуйте этому руководству по преобразованию формул в значения.
Обязательно загрузите образец файла, прилагаемый к этому руководству, чтобы получить эти примеры в Excel.
15 формул Excel, сочетания клавиш и приемы, которые сэкономят много времени
Для большинства маркетологов попытки организовать и проанализировать электронные таблицы в Microsoft Excel могут снова и снова походить на кирпичную стену. Вы вручную копируете столбцы и записываете длинные математические формулы на клочке бумаги, при этом думая про себя: «Вот , — лучший способ сделать это».
По правде говоря, есть — вы просто еще этого не знаете.
Excel может быть непростой задачей. С одной стороны, это исключительно мощный инструмент для составления отчетов и анализа маркетинговых данных. С другой стороны, без надлежащей подготовки легко почувствовать, что это работает против вас. Во-первых, существует более десятка важных формул, которые Excel может автоматически запускать для вас, поэтому вам не придется перебирать сотни ячеек с помощью калькулятора на своем столе.
Что такое формулы Excel?
Формулы
Excel помогают определять отношения между значениями в ячейках электронной таблицы, выполнять математические вычисления с использованием этих значений и возвращать полученное значение в выбранной вами ячейке.Формулы, которые вы можете выполнять автоматически, включают сумму, вычитание, процентное соотношение, деление, среднее значение и даже дату / время.
Мы рассмотрим все это и многое другое в этом сообщении в блоге.
Как вставлять формулы в Excel
Вы можете спросить, что означает вкладка «Формулы» на верхней панели инструментов навигации в Excel. В более поздних версиях Excel это горизонтальное меню, показанное ниже, позволяет находить и вставлять формулы Excel в определенные ячейки электронной таблицы.
Чем больше вы используете различных формул в Excel, тем легче будет их запомнить и выполнить вручную. Тем не менее, набор значков выше представляет собой удобный каталог формул, который вы можете просматривать и ссылаться на них, оттачивая свои навыки работы с электронными таблицами.
Формулы
Excel также называют «функциями». Чтобы вставить формулу в электронную таблицу, выделите ячейку, в которой вы хотите запустить формулу, затем щелкните крайний левый значок «Вставить функцию», чтобы просмотреть популярные формулы и их назначение.Это окно просмотра будет выглядеть так:
Хотите более упорядоченный просмотр веб-страниц? Используйте любой из выделенных нами значков (внутри длинного красного прямоугольника на первом скриншоте выше), чтобы найти формулы, относящиеся к множеству общих вопросов, таких как финансы, логика и т. Д. Найдя формулу, которая соответствует вашим потребностям, нажмите «Вставить функцию», как показано в окне выше.
Теперь давайте глубже погрузимся в некоторые из наиболее важных формул Excel и то, как выполнять каждую из них в типичных ситуациях.
Чтобы помочь вам использовать Excel более эффективно (и сэкономить массу времени), мы составили список основных формул, сочетаний клавиш и других небольших приемов и функций, которые вам следует знать.
ПРИМЕЧАНИЕ. Следующие формулы применимы к Excel 2017 . Если вы используете более старую версию Excel, расположение каждой функции, упомянутой ниже, может немного отличаться.
1. СУММА
Все формулы Excel начинаются со знака равенства =, за которым следует специальный текстовый тег, обозначающий формулу, которую должен выполнять Excel.
Формула СУММ в Excel — одна из самых простых формул, которые вы можете ввести в электронную таблицу, позволяющую найти сумму (или итог) двух или более значений. Чтобы выполнить формулу СУММ, введите значения, которые вы хотите сложить, используя формат = СУММ (значение 1, значение 2 и т. Д.) .
Значения, которые вы вводите в формулу СУММ, могут быть либо фактическими числами, либо равными числу в определенной ячейке вашей электронной таблицы.
Чтобы найти СУММ 30 и 80, например, введите следующую формулу в ячейку своей электронной таблицы: = СУММ (30, 80) .Нажмите «Enter», и в ячейке появится сумма обоих чисел: 110.
Чтобы найти СУММУ значений в ячейках B2 и B11, например, введите следующую формулу в ячейку своей электронной таблицы: = СУММ (B2, B11) . Нажмите «Enter», и в ячейке появится сумма чисел, которые в настоящее время заполнены в ячейках B2 и B11. Если ни в одной из ячеек нет чисел, формула вернет 0.
Имейте в виду, что вы также можете найти общее значение списка чисел в Excel.Чтобы найти СУММУ значений в ячейках B2 с по B11, введите следующую формулу в ячейку своей электронной таблицы: = СУММ (B2: B11) . Обратите внимание на двоеточие между обеими ячейками, а не на запятую. Посмотрите, как это может выглядеть в таблице Excel для контент-маркетолога, ниже:
2. IF
Формула ЕСЛИ в Excel обозначается как = ЕСЛИ (логический_тест, значение_если_ истинно, значение_если_ ложь) . Это позволяет вам ввести текстовое значение в ячейку «если» что-то еще в вашей электронной таблице истинно или ложно.Например, = IF (D2 = «Гриффиндор», «10», «0») присудит 10 баллов ячейке D2, если эта ячейка содержит слово «Гриффиндор».
Бывают случаи, когда мы хотим знать, сколько раз значение появляется в наших таблицах. Но бывают случаи, когда мы хотим найти ячейки, содержащие эти значения, и ввести рядом с ними определенные данные.
Мы вернемся к примеру Спранга для этого. Если мы хотим присвоить 10 баллов всем, кто принадлежит к Гриффиндорскому дому, вместо того, чтобы вручную вводить десятки рядом с именем каждого гриффиндорского ученика, мы воспользуемся формулой ЕСЛИ-ТО, чтобы сказать: Если ученик находится в Гриффиндоре, , затем он или она должны получить десять очков.
Формула: ЕСЛИ (логический_тест, значение_если_ истинно, значение_если_ ложь)
Logical_Test: Логический тест — это часть оператора «ЕСЛИ». В данном случае логика D2 = «Гриффиндор». Убедитесь, что ваше значение Logical_Test заключено в кавычки.
Value_if_True: Если значение истинно, т. Е. Если студент живет в Гриффиндоре, то это значение мы хотим отобразить. В данном случае мы хотим, чтобы это было число 10, чтобы указать, что ученику были присвоены 10 баллов.Примечание. Используйте кавычки только в том случае, если вы хотите, чтобы результат был текстом, а не числом.
Value_if_False: Если значение false — а ученик , а не живет в Гриффиндоре, — мы хотим, чтобы в ячейке отображалось «0» за 0 баллов.
Формула в примере ниже: = IF (D2 = «Гриффиндор», «10», «0»)

3. В процентах
Чтобы вычислить процентную формулу в Excel, введите ячейки, для которых вы найдете процентное значение, в формате = A1 / B1 .Чтобы преобразовать полученное десятичное значение в процент, выделите ячейку, щелкните вкладку «Главная» и выберите «Процент» в раскрывающемся списке чисел.
Не существует «формулы» Excel для процентов как таковой, но Excel позволяет легко преобразовать значение любой ячейки в проценты, чтобы вам не приходилось вычислять и повторно вводить числа самостоятельно.
Базовая настройка для преобразования значения ячейки в процент находится на вкладке «Главная страница» в Excel . Выберите эту вкладку, выделите ячейки, которые вы хотите преобразовать в процент, и щелкните раскрывающееся меню рядом с «Условное форматирование» (эта кнопка меню может сначала сказать «Общие»).Затем выберите «Процент» из появившегося списка опций. Это преобразует значение каждой выделенной ячейки в процент. См. Эту функцию ниже.
Имейте в виду, что если вы используете другие формулы, например формулу деления (обозначенную = A1 / B1 ), для возврата новых значений, ваши значения могут отображаться как десятичные по умолчанию. Просто выделите ячейки до или после выполнения этой формулы и установите для формата этих ячеек значение «Процент» на вкладке «Главная», как показано выше.
4. Вычитание
Чтобы выполнить формулу вычитания в Excel, введите вычитаемые ячейки в формате = СУММ (A1, -B1) . Это позволит вычесть ячейку по формуле СУММ, добавив знак минус перед ячейкой, которую вы вычитаете. Например, если A1 было 10, а B1 было 6, = SUM (A1, -B1) выполнит 10 + -6, вернув значение 4.
Как и проценты, вычитание не имеет собственной формулы в Excel, но это не значит, что это невозможно.Вы можете вычесть любые значения (или значения внутри ячеек) двумя разными способами.
Использование формулы = СУММ. Чтобы вычесть несколько значений друг из друга, введите ячейки, которые вы хотите вычесть, в формате = СУММ (A1, -B1) со знаком минус (обозначается дефисом) перед ячейкой, значение которой вы вычитаете. Нажмите клавишу ВВОД, чтобы вернуть разницу между обеими ячейками, заключенную в круглые скобки. Посмотрите, как это выглядит на скриншоте выше.
В формате = A1-B1 .Чтобы вычесть несколько значений друг из друга, просто введите знак равенства, за которым следует первое значение или ячейка, дефис и значение или ячейка, которые вы вычитаете. Нажмите Enter, чтобы вернуть разницу между обоими значениями.
5. Умножение
Чтобы выполнить формулу умножения в Excel, введите умножаемые ячейки в формате = A1 * B1 . В этой формуле звездочка используется для умножения ячейки A1 на ячейку B1. Например, если A1 было 10, а B1 было 6, = A1 * B1 вернет значение 60.
Вы можете подумать, что умножение значений в Excel имеет свою собственную формулу или использует символ «x» для обозначения умножения между несколькими значениями. На самом деле это просто как звездочка — *.
Чтобы умножить два или более значений в электронной таблице Excel, выделите пустую ячейку. Затем введите значения или ячейки, которые вы хотите умножить, в формате = A1 * B1 * C1 … и т. Д. Звездочка эффективно умножает каждое значение, включенное в формулу.
Нажмите Enter, чтобы вернуть желаемый продукт.Посмотрите, как это выглядит на скриншоте выше.
6. Отдел
Чтобы выполнить формулу деления в Excel, введите разделяемые ячейки в формате = A1 / B1 . В этой формуле используется косая черта «/» для разделения ячейки A1 на ячейку B1. Например, если A1 было 5, а B1 было 10, = A1 / B1 вернет десятичное значение 0,5.
Деление в Excel — одна из самых простых функций, которые вы можете выполнить. Для этого выделите пустую ячейку, введите знак равенства «=» и введите два (или более) значения, которые вы хотите разделить, с помощью косой черты «/» между ними.Результат должен быть в следующем формате: = B2 / A2 , как показано на скриншоте ниже.
Нажмите Enter, и желаемое частное должно появиться в первоначально выделенной ячейке.
7. ДАТА
Формула Excel ДАТА обозначается = ДАТА (год, месяц, день) . Эта формула вернет дату, соответствующую значениям, указанным в скобках, — даже значениям, полученным из других ячеек. Например, если A1 было 2018, B1 было 7, а C1 было 11, = DATE (A1, B1, C1) вернет 7/11/2018.
Создание дат в ячейках электронной таблицы Excel может быть непростой задачей время от времени. К счастью, есть удобная формула, которая упрощает форматирование дат. Эту формулу можно использовать двумя способами:
Создайте даты из серии значений ячеек. Для этого выделите пустую ячейку, введите «= ДАТА» и в скобках введите ячейки, значения которых создают желаемую дату — начиная с года, затем с номера месяца, затем с дня. Окончательный формат должен выглядеть так: = ДАТА (год, месяц, день).Посмотрите, как это выглядит на скриншоте ниже.
Автоматически устанавливает сегодняшнюю дату. Для этого выделите пустую ячейку и введите следующую строку текста: = ДАТА (ГОД (СЕГОДНЯ ()), МЕСЯЦ (СЕГОДНЯ ()), ДЕНЬ (СЕГОДНЯ ())). Нажатие клавиши ВВОД вернет текущую дату, когда вы работаете в электронной таблице Excel.
При любом использовании формулы даты Excel ваша возвращаемая дата должна быть в форме «мм / дд / гг» — если ваша программа Excel не отформатирована по-другому.
8. Массив
Формула массива в Excel окружает простую формулу в скобках в формате {= (Начальное значение 1: Конечное значение 1) * (Начальное значение 2: Конечное значение 2)} . При нажатии ctrl + shift + center будет вычислено и возвращено значение из нескольких диапазонов, а не только отдельные ячейки, добавленные или умноженные друг на друга.
Вычислить сумму, произведение или частное отдельных ячеек легко — просто используйте формулу = СУММ и введите ячейки, значения или диапазон ячеек, над которыми вы хотите выполнить эту арифметику.Но как насчет нескольких диапазонов? Как найти совокупное значение большой группы ячеек?
Числовые массивы — это удобный способ одновременного выполнения нескольких формул в одной ячейке, чтобы вы могли видеть одну итоговую сумму, разницу, произведение или частное. Например, если вы хотите узнать общий доход от нескольких проданных единиц, формула массива в Excel идеально подходит для вас. Вот как бы вы это сделали:
Чтобы начать использовать формулу массива, введите «= СУММ» и в скобках введите первые из двух (или трех, или четырех) диапазонов ячеек, которые вы хотите умножить вместе.Вот как может выглядеть ваш прогресс: = СУММ (C2: C5
Затем добавьте звездочку после последней ячейки первого диапазона, который вы включили в формулу. Это означает умножение. После этой звездочки введите второй диапазон ячеек. Вы умножите этот второй диапазон ячеек на первый. Ваш прогресс в этой формуле теперь должен выглядеть так: = СУММ (C2: C5 * D2: D5)
Готовы нажать Enter? Не так быстро … Поскольку эта формула настолько сложна, Excel резервирует другую команду клавиатуры для массивов.После того, как вы закроете круглые скобки в формуле массива, нажмите C trl + Shift + Enter . Это распознает вашу формулу как массив, заключит формулу в фигурные скобки и успешно вернет ваш продукт обоих диапазонов вместе.
При расчете доходов это может значительно сократить ваше время и усилия. См. Окончательную формулу на скриншоте выше.
9. СЧЕТ
Формула COUNT в Excel обозначается как = COUNT (Начальная ячейка: Конечная ячейка) .Эта формула вернет значение, равное количеству записей, найденных в желаемом диапазоне ячеек. Например, если есть восемь ячеек с введенными значениями от A1 до A10, = COUNT (A1: A10) вернет значение 8.
Формула СЧЁТ в Excel особенно полезна для больших электронных таблиц , в которых вы хотите увидеть, сколько ячеек содержит фактические записи. Не дайте себя обмануть: Эта формула не выполняет никаких вычислений со значениями самих ячеек. Эта формула предназначена просто для того, чтобы узнать, сколько ячеек в выбранном диапазоне чем-то занято.
Используя формулу, выделенную полужирным шрифтом выше, вы можете легко выполнить подсчет активных ячеек в своей электронной таблице. Результат будет выглядеть примерно так:
10. СРЕДНЕЕ
Чтобы выполнить формулу среднего значения в Excel, введите значения, ячейки или диапазон ячеек, для которых вы вычисляете среднее значение, в формате = СРЕДНЕЕ (число1, число2 и т. Д.) или = СРЕДНЕЕ (Начальное значение: Конечное значение). Это вычислит среднее значение всех значений или диапазона ячеек, включенных в круглые скобки.
Нахождение среднего значения диапазона ячеек в Excel избавляет вас от необходимости находить отдельные суммы и затем выполнять отдельное уравнение деления для вашей общей суммы. Используя = СРЕДНЕЕ в качестве начального ввода текста, вы можете позволить Excel сделать всю работу за вас.
Для справки, среднее значение группы чисел равно сумме этих чисел, деленной на количество элементов в этой группе.
11. СУММЕСЛИ
Формула СУММЕСЛИ в Excel обозначается как = СУММЕСЛИ (диапазон, критерий, [диапазон суммы]) .Это вернет сумму значений в желаемом диапазоне ячеек, которые все соответствуют одному критерию. Например, = СУММЕСЛИ (C3: C12, «> 70 000») вернет сумму значений между ячейками C3 и C12 только из ячеек, размер которых превышает 70 000.
Допустим, вы хотите определить прибыль, полученную вами из списка потенциальных клиентов, связанных с определенными кодами регионов, или рассчитать сумму заработной платы определенных сотрудников — но только если она превышает определенную сумму. Выполнение этого вручную, мягко говоря, занимает немного времени.
С функцией СУММЕСЛИ этого не должно быть — вы можете легко сложить сумму ячеек, соответствующих определенным критериям, как в примере с зарплатой выше.
Формула : = СУММЕСЛИ (диапазон, критерии, [диапазон_суммы])
Диапазон: Диапазон, который проверяется с использованием ваших критериев.
Критерии: Критерии, определяющие, какие ячейки в Criteria_range1 будут добавлены вместе
[Sum_range]: Необязательный диапазон ячеек, который вы собираетесь сложить в дополнение к первому введенному диапазону Range .Это поле может быть опущено.
В приведенном ниже примере мы хотели вычислить сумму зарплат, превышающих 70 000 долларов. Функция СУММЕСЛИ суммирует суммы в долларах, которые превышают это число в ячейках с C3 по C12, по формуле = СУММЕСЛИ (C3: C12, «> 70 000») .
12. ОТДЕЛКА
Формула TRIM в Excel обозначается как = TRIM (текст) . Эта формула удалит все пробелы, введенные до и после текста, введенного в ячейку.Например, если A2 включает имя «Стив Петерсон» с нежелательными пробелами перед именем, = TRIM (A2) вернет «Стив Петерсон» без пробелов в новой ячейке.
Электронная почта и обмен файлами — прекрасные инструменты на современном рабочем месте. То есть до тех пор, пока один из ваших коллег не пришлет вам рабочий лист с каким-то действительно забавным интервалом. Эти ложные пробелы не только затрудняют поиск данных, но также влияют на результаты, когда вы пытаетесь сложить столбцы чисел.
Вместо того, чтобы кропотливо удалять и добавлять пробелы по мере необходимости, вы можете убрать любые нерегулярные интервалы с помощью функции TRIM, которая используется для удаления лишних пробелов из данных (за исключением одиночных пробелов между словами).
Формула : = ОБРЕЗАТЬ (текст).
Текст: Текст или ячейка, из которых вы хотите удалить пробелы.
Вот пример того, как мы использовали функцию TRIM для удаления лишних пробелов перед списком имен. Для этого мы ввели = TRIM («A2») в строку формул и скопировали это для каждого имени под ним в новом столбце рядом со столбцом с нежелательными пробелами.
Ниже приведены некоторые другие формулы Excel, которые могут оказаться полезными по мере роста потребностей в управлении данными.
13. ЛЕВЫЙ, СРЕДНИЙ и ПРАВЫЙ
Допустим, у вас есть строка текста в ячейке, которую вы хотите разбить на несколько разных сегментов. Вместо того, чтобы вручную вводить каждый фрагмент кода в соответствующий столбец, пользователи могут использовать ряд строковых функций для деконструкции последовательности по мере необходимости: LEFT, MID или RIGHT.
ЛЕВЫЙ
Цель : Используется для извлечения первых X чисел или символов в ячейке.
Формула : = ЛЕВЫЙ (текст, число_символов)
Текст : строка, из которой вы хотите извлечь.
Number_of_characters : количество символов, которые вы хотите извлечь, начиная с самого левого символа.
В приведенном ниже примере мы ввели = ЛЕВЫЙ (A2,4) в ячейку B2 и скопировали его в B3: B6. Это позволило нам извлечь первые 4 символа кода.
MID
Цель : Используется для извлечения символов или цифр в середине в зависимости от позиции.
Формула : = MID (текст, начальная_позиция, число_символов)
Текст : строка, из которой вы хотите извлечь.
Start_position : позиция в строке, с которой вы хотите начать извлечение. Например, первая позиция в строке — 1.
Number_of_characters : количество символов, которые вы хотите извлечь.
В этом примере мы ввели = MID (A2,5,2) в ячейку B2 и скопировали его в B3: B6. Это позволило нам извлечь два числа, начиная с пятой позиции кода.
СПРАВА
Назначение: Используется для извлечения последних X чисел или символов в ячейке.
Формула: = ПРАВО (текст, число_символов)
Текст : строка, из которой вы хотите извлечь.
Number_of_characters : количество символов, которые вы хотите извлечь, начиная с самого правого символа.
Для этого примера мы ввели = ПРАВО (A2,2) в ячейку B2 и скопировали его в B3: B6. Это позволило нам извлечь два последних числа кода.
14. ВПР
Это старый, но полезный — и он немного более подробный, чем некоторые другие формулы, которые мы здесь перечислили. Но это особенно полезно в тех случаях, когда у вас есть два набора данных в двух разных таблицах, и вы хотите объединить их в одну таблицу.
Моя коллега Рэйчел Спранг, чей учебник «Как использовать Excel» является обязательным к прочтению для всех, кто хочет учиться, использует в качестве примера список имен, адресов электронной почты и компаний.Если у вас есть список имен людей рядом с их адресами электронной почты в одной электронной таблице и список адресов электронной почты тех же людей рядом с названиями их компаний в другой, но вы хотите, чтобы имена, адреса электронной почты и названия компаний этих людей появиться в одном месте — вот тут-то и пригодится ВПР.
Примечание. При использовании этой формулы необходимо убедиться, что хотя бы один столбец идентичен в обеих таблицах. Просмотрите свои наборы данных, чтобы убедиться, что столбец данных, который вы используете для объединения информации, точно такой же, без лишних пробелов.
Формула: ВПР (значение поиска, массив таблицы, номер столбца, [поиск диапазона])
Значение поиска: Идентичное значение в обеих таблицах. Выберите первое значение в своей первой таблице. В следующем примере Спрунга это означает первый адрес электронной почты в списке или ячейку 2 (C2).
Table Array: Диапазон столбцов на листе 2, из которых вы собираетесь извлекать данные, включая столбец данных, идентичный вашему значению поиска (в нашем примере, адреса электронной почты) на листе 1, а также столбец данные, которые вы пытаетесь скопировать на лист 1.В нашем примере это «Sheet2! A: B». «A» означает столбец A на листе 2, который является столбцом на листе 2, где перечислены данные, идентичные нашему поисковому значению (электронная почта) в листе 1. «B» означает столбец B, содержащий информацию, доступную только на листе 2, которую вы хотите перевести на лист 1.
Номер столбца: Массив таблицы сообщает Excel, где (какой столбец) находятся новые данные, которые вы хотите скопировать на лист 1. В нашем примере это будет столбец «Дом», второй в нашем массиве таблиц, что делает его столбцом номер 2.
Поиск диапазона: Используйте FALSE, чтобы обеспечить получение только точных совпадений значений.
Формула с переменными из примера Спрунга ниже: = ВПР (C2, Sheet2! A: B, 2, FALSE)
В этом примере Лист 1 и Лист 2 содержат списки, описывающие различную информацию об одних и тех же людях, а общая связь между ними — их адреса электронной почты. Допустим, мы хотим объединить оба набора данных, чтобы вся информация о доме из Листа 2 переводилась в Лист 1.Вот как это будет работать:
15. СЛУЧАЙНОЕ ИЗМЕНЕНИЕ
Есть отличная статья, в которой формула RANDOMIZE в Excel сравнивается с перетасовкой колоды карт. Вся колода представляет собой столбец, а каждая карта — 52 в колоде — представляет собой ряд. «Чтобы перемешать колоду, — пишет Стив МакДоннелл, — вы можете вычислить новый столбец данных, заполнить каждую ячейку столбца случайным числом и отсортировать книгу на основе поля случайных чисел».
В маркетинге вы можете использовать эту функцию, когда хотите присвоить случайный номер списку контактов — например, если вы хотите поэкспериментировать с новой почтовой кампанией и должны использовать слепые критерии, чтобы выбрать, кто его получит.Назначая номера указанным контактам, вы можете применить правило: «Любой контакт с цифрой 6 или выше будет добавлен в новую кампанию».
Формула: RAND ()
Начните с одного столбца контактов. Затем в соседнем столбце введите «RAND ()» без кавычек, начиная со строки верхнего контакта.
Для примера ниже: RANDBETWEEN (внизу, вверху)
RANDBETWEEN позволяет вам определять диапазон номеров, которые вы хотите назначить.В случае этого примера я хотел использовать от одного до 10.
внизу: Наименьшее число в диапазоне.
верх: Наибольшее число в диапазоне,
Формула в примере ниже: = СЛУЧМЕЖДУ (1,10)
Полезный материал, правда? Теперь о вишенке на торте: после того, как вы освоите нужную формулу Excel, вы захотите воспроизвести ее для других ячеек, не переписывая формулу. И, к счастью, для этого есть функция Excel.Посмотрите это ниже.
Как вставить формулу в Excel для всего столбца
Чтобы вставить формулу в Excel для всего столбца электронной таблицы, введите формулу в самую верхнюю ячейку нужного столбца и нажмите «Ввод». Затем выделите и дважды щелкните нижний правый угол этой ячейки, чтобы скопировать формулу в каждую ячейку под ней в столбце.
Иногда вам может понадобиться запустить одну и ту же формулу для всей строки или столбца вашей электронной таблицы. Скажем, например, у вас есть список чисел в столбцах A и B электронной таблицы, и вы хотите ввести отдельные итоги каждой строки в столбец C.
Очевидно, было бы слишком утомительно настраивать значения формулы для каждой ячейки, чтобы вы находили сумму соответствующих чисел каждой строки. К счастью, Excel позволяет автоматически составлять столбец; все, что вам нужно сделать, это ввести формулу в первую строку. Проверьте следующие шаги:
Введите формулу в пустую ячейку и нажмите «Ввод», чтобы запустить формулу.
Наведите курсор на правый нижний угол ячейки, содержащей формулу.Вы увидите маленький жирный символ «+».
Хотя вы можете дважды щелкнуть этот символ, чтобы автоматически заполнить весь столбец формулой, вы также можете щелкнуть и перетащить курсор вниз вручную, чтобы заполнить только определенную длину столбца. столбец, в который вы хотите ввести формулу, отпустите кнопку мыши, чтобы скопировать формулу. Затем просто проверяйте каждое новое значение, чтобы убедиться, что оно соответствует правильным ячейкам.
Сочетания клавиш Excel
1.Быстро выбирайте строки, столбцы или всю таблицу.
Возможно, у вас не хватает времени. Я имею в виду, а кто нет? Нет времени, нет проблем. Вы можете выбрать всю электронную таблицу одним щелчком мыши. Все, что вам нужно сделать, это просто щелкнуть вкладку в верхнем левом углу листа, чтобы выделить все сразу.
Просто хотите выделить все в определенном столбце строки? Это так же просто с этими ярлыками:
Для Mac:
Выбрать столбец = Command + Shift + Down / Up
Выбрать строку = Command + Shift + Right / Left
Для ПК:
Выбрать столбец = Control + Shift + Down / Up
Выбрать строку = Control + Shift + Right / Left
Этот ярлык особенно полезен, когда вы работаете с большими наборами данных, но вам нужно выбрать только конкретную его часть.
2. Быстро откройте, закройте или создайте книгу.
Необходимо на лету открывать, закрывать или создавать книгу? Следующие сочетания клавиш позволят вам выполнить любое из вышеперечисленных действий менее чем за минуту.
Для Mac:
Открыть = Команда + O
Закрыть = Command + W
Создать = Команда + N
Для ПК:
Открыть = Контроль + O
Закрыть = Control + F4
Создать = Control + N
3.Отформатируйте числа в валюту.
Есть необработанные данные, которые вы хотите превратить в валюту? Будь то зарплаты, маркетинговые бюджеты или продажа билетов на мероприятие, решение простое. Просто выделите ячейки, которые вы хотите переформатировать, и выберите Control + Shift + долларов США.
Числа будут автоматически переведены в суммы в долларах — со знаками доллара, запятыми и десятичными точками.
Примечание. Этот ярлык также работает с процентами. Если вы хотите пометить столбец числовых значений как «проценты», замените «$» на «%».
4. Вставьте текущую дату и время в ячейку.
Независимо от того, регистрируете ли вы сообщения в социальных сетях или отслеживаете задачи, которые вы отмечаете в своем списке дел, вы можете добавить дату и время на свой рабочий лист. Начните с выбора ячейки, в которую вы хотите добавить эту информацию.
Затем, в зависимости от того, что вы хотите вставить, выполните одно из следующих действий:
Вставить текущую дату = Control +; (точка с запятой)
Вставить текущее время = Control + Shift +; (точка с запятой)
Вставить текущую дату и время = Control +; (точка с запятой) , ПРОБЕЛ, а затем Control + Shift +; (точка с запятой) .
Другие уловки Excel
1. Настройте цвет вкладок.
Если у вас есть тонна разных листов в одной книге — что случается с лучшими из нас — упростите определение того, куда вам нужно идти, выделив вкладки цветом. Например, вы можете пометить маркетинговые отчеты за прошлый месяц красным, а текущие — оранжевым.
Просто щелкните вкладку правой кнопкой мыши и выберите «Цвет вкладки». Появится всплывающее окно, в котором вы сможете выбрать цвет из существующей темы или настроить его в соответствии с вашими потребностями.
2. Добавьте комментарий к ячейке.
Если вы хотите сделать заметку или добавить комментарий к определенной ячейке на листе, просто щелкните правой кнопкой мыши ячейку, которую вы хотите прокомментировать, затем нажмите Вставить комментарий . Введите свой комментарий в текстовое поле и щелкните за пределами поля комментария, чтобы сохранить его.
Ячейки, содержащие комментарии, отображаются в углу маленьким красным треугольником. Чтобы просмотреть комментарий, наведите на него курсор.
3. Скопируйте и продублируйте форматирование.
Если вы когда-нибудь тратили время на форматирование листа по своему вкусу, вы, вероятно, согласитесь, что это не самое приятное занятие.На самом деле это довольно утомительно.
По этой причине, вероятно, вы не захотите повторять процесс в следующий раз — да и не обязаны. Благодаря программе Excel Format Painter вы можете легко скопировать форматирование из одной области рабочего листа в другую.
Выберите то, что вы хотите воспроизвести, затем выберите опцию Format Painter — значок кисти — на панели управления. Затем указатель отобразит кисть, предлагая вам выбрать ячейку, текст или весь рабочий лист, к которому вы хотите применить это форматирование, как показано ниже:
4.Определите повторяющиеся значения.
Во многих случаях повторяющиеся значения — например, повторяющийся контент при управлении поисковой оптимизацией — могут стать проблемой, если не будут исправлены. Однако в некоторых случаях вам просто нужно знать об этом.
Какова бы ни была ситуация, легко обнаружить любые существующие повторяющиеся значения в вашем рабочем листе всего за несколько быстрых шагов. Для этого щелкните опцию Conditional Formatting и выберите Highlight Cell Rules> Duplicate Values 
Используя всплывающее окно, создайте желаемое правило форматирования, чтобы указать, какой тип дублированного содержимого вы хотите перенести.
В приведенном выше примере мы искали любые повторяющиеся зарплаты в выбранном диапазоне и отформатировали повторяющиеся ячейки желтым цветом.
В маркетинге использование Excel неизбежно, но с этими уловками это не должно быть так пугающе. Как говорится, практика ведет к совершенству. Чем больше вы будете использовать эти формулы, ярлыки и уловки, тем больше они станут вашей второй натурой.
Чтобы копнуть глубже, ознакомьтесь с некоторыми из наших любимых ресурсов для изучения Excel.