Как убрать формулу в excel оставив значения
Удаление формул
Удаление формулы с сохранением результатов
Для этого нужно скопировать формулу, а затем вкопировать ее в ту же ячейку с помощью параметра Вировать значения.
-
Выберем ячейку или диапазон ячеек, которые содержат формулу.
Если формула является формулой массива, необходимо сначала выбрать все ячейки в диапазоне ячеек, которые содержат формулу массива:-
Щелкните ячейку в формуле массива.
-
На вкладке Главная в группе Редактирование нажмите кнопку Найти и выделить, а затем выберите команду Перейти.
-
Нажмите кнопку Дополнительный.
-
Выберите текущий массив.
-
-
На вкладке Главная в группе Буфер обмена нажмите кнопку Копировать .
-
На вкладке Главная в группе Буфер обмена щелкните стрелку под кнопкой , а затем выберите вкладки Значения.
Удаление формулы массива
Чтобы удалить формулу массива, выберите все ячейки в диапазоне ячеек, которые содержат формулу массива. Для этого:
-
Щелкните ячейку в формуле массива.
-
На вкладке Главная в группе Редактирование нажмите кнопку Найти и выделить, а затем выберите команду Перейти.
-
Нажмите кнопку Дополнительный.
-
Выберите текущий массив.
-
Нажмите клавишу DELETE.
Замена формулы на ее результат
Замена формул вычисляемой величиной
При замене формул их значениями Excel окончательно удаляет их. Если вы случайно заменяете формулу значением и хотите ее восстановить, нажмите кнопку Отменить сразу после ввода или вжатия значения.
-
Вы можете выбрать ячейку или диапазон ячеек, которые содержат формулы.
Если формула является формула массива, выделите диапазон ячеек, содержащих формулу массива.
Как выбрать диапазон, содержащий формулу массива
-
Щелкните ячейку в формуле массива.
-
На вкладке Главная в группе Редактирование нажмите кнопку Найти и выделить, а затем выберите команду Перейти.
-
Нажмите кнопку Дополнительный.
-
Щелкните Текущий массив.
-
-
Нажмите кнопку Копировать .
-
Нажмите кнопку В .
-
Щелкните стрелку рядом с кнопкой Параметры в и выберите только значения.
В следующем примере показана формула в ячейке D2, которая умножает ячейки A2, B2 и скидку из ячейки C2 для расчета суммы счета для продажи. Чтобы скопировать фактическое значение вместо формулы из ячейки на другой книгу или на другой, можно преобразовать формулу в ячейку в ее значение, выстроив следующее:
-
Нажмите F2, чтобы изменить ячейку.
-
Нажмите F9, а затем ввод.
После преобразования ячейки из формулы в значение в области формул отображается значение 1932,322. Обратите внимание, что 1932,322 — фактическое вычисляемого значения, а 1932,32 — значение, отображаемого в ячейке в валютном формате.
Совет: При редактировании ячейки с формулой можно нажать F9, чтобы окончательно заменить формулу ее вычисляемой величиной.
Замена части формулы значением, полученным при ее вычислении
Иногда требуется заменить только часть формулы ее вычисляемой величиной. Например, вы хотите заблокировать значение, которое используется в качестве аванса по кредиту на автомобиль. Этот первый взнос вычислялся на основе процента от годового дохода компании. В настоящее время сумма дохода не изменится, поэтому вы хотите заблокировать первый взнос в формуле, которая вычисляет платеж на основе различных сумм ссуды.
При замене части формулы ее значением ее часть восстановить невозможно.
-
Щелкните ячейку с формулой.
-
В строка формул выберите часть формулы, которую нужно заменить ее вычисляемой величиной. При выборе части формулы, которую вы хотите заменить, убедитесь, что вы включили все операнд. Например, при выборе функции необходимо выбрать имя функции целиком, открываемую скобки, аргументы и закрываюю скобки.
-
Чтобы вычислить выбранную часть, нажмите F9.
-
Чтобы заменить выбранную часть формулы ее вычисляемой величиной, нажмите ввод.
В Excel в Интернете результаты уже отображаются в ячейке книги, а формула отображается только в .
Как в эксель убрать формулы оставив значения
Удаление формул
Смотрите также формулу. Это возможно? End Sub формулу, а расчет о том, что в выделенном диапазоне(ах)Специальная вставка). оно будет всякий указанные ниже действия. 2007: на вкладке список них отображался шрифтомУбедитесь, что в разделе — в ячейку A1.и нажмите кнопкуНа вкладкеПримечание:JayBhagavanПопробуйте такой код, введите вручную действия выполненные макросом
Dim smallrng Asи затем опциюВы хотите скопировать диапазон раз перерассчитываться.Ячейка с ошибкой вПараметрыЦвет
Удаление формулы
-
белого цвета. ВФорматировать только ячейки, для
-
В ячейке A1
Удаление формулы, но сохранить результаты
ПерейтиГлавнаяМы стараемся как: Saifer, выложите файл-пример. он также лечит
-
Saifer невозможно отменить -
Range For EachЗначения (Paste Special - с данными изДенис денисов формулев группе-
, и в разделе результате текст ошибки
-
которых выполняется следующее отобразится значение ошибки.в группе можно оперативнее обеспечивать Лень за Вас после того, как: Доброго времени суток, применяйте их с
-
smallrng In Selection.Areas Values) одного места в
-
: Ответ верен. НеВ Excel 2016, Excel 2013Сводная таблица
-
-
Цвета темы в таких ячейках условие #ДЕЛ/0!.Нажмите кнопкуРедактирование
вас актуальными справочными -
его делать. сделано Paste as уважаемые форумчане! осторожностью. smallrng.Value = smallrng.Valueв открывшемся диалоговом другое, но при
пересчитывает) и Excel 2010:щелкните стрелку рядом
Удаление формулы массива
выберите белый цвет. становится невидимым.в первом спискеВыделите ячейку A1 иВыделитьнажмите кнопку
-
материалами на вашемКазанский
-
values, когда числовыеЕсть небольшая проблема,Если ломает делать все Next smallrng End окне: копировании "сползут" всеФормулы – это хорошо. выберите с командойВозможны ситуации, когда необходимо,
-
Выделите диапазон ячеек, содержащих выбран пункт нажмите клавишу F2,
-
.Найти и выделить языке. Эта страница
-
: Sub Formulas_To_Values() Dim
support.office.com>
Скрытие значений и индикаторов ошибок в ячейках
данные всеравно остаются как можно удалить вышеперечисленное, то можно SubЭтот способ требует определенной ссылки в формулах. Они автоматически пересчитываютсяФайлПараметры чтобы вместо значений значение ошибки.
Значение ячейки чтобы изменить формулу.Установите флажоки выберите команду переведена автоматически, поэтому c As Range текстом. формула и диапазона поступить еще прощеЕсли вам нужно преобразовать
Преобразование ошибки в нулевое значение и использование формата для скрытия значения
сноровки, но будетВ любой подобной ситуации при любом изменении >и выберите пункт ошибок в ячейках
На вкладке
-
, а во втором —После знака равенства (=)
-
Текущий массивПерейти ее текст может For Each cSaifer после того, как - установить надстройку в значения текущий
заметно быстрее предыдущего. можно легко удалить исходных данных, превращая -
ПараметрыПараметры отображалась текстовая строка,
-
Главнаяравно введите..
содержать неточности и -
In Selection.SpecialCells(xlCellTypeFormulas) If: Как этот макрос
-
ячейка (из этогоPLEX лист, то макрос Делаем следующее: формулы, оставив в
Excel из "калькулятора-переростка" >. например "#Н/Д", прочеркв группе -
. Затем в текстовомЕСЛИОШИБКА
Нажмите клавишу DELETE.Нажмите кнопку грамматические ошибки. Для c <> ""
по диапазон загнать.
-
диапазона) станет не, где уже есть будет таким:выделяем диапазон с формулами ячейках только их в мощную автоматизированную
-
ФормулыОткройте вкладку или "НД". Сделать
-
Стили поле справа введитес открывающей круглойРассмотрим ситуацию, когда формулыВыделить нас важно, чтобы
-
Then c.Value = У меня листе пустой? готовые макросы дляSub Formulas_To_Values_Sheet() 'преобразование на листе значения. Давайте рассмотрим систему обработки поступающих.Разметка и формат это можно сщелкните стрелку рядом
-
значение 0. скобкой: в таблице содержат
-
. эта статья была c.Value Next End много формул.Такой макрос не конвертации формул в формул в значения
-
хватаем за край выделенной несколько способов и данных. Они позволяютВ Excel 2007 нажмитеи выполните одно помощью функций с командойНажмите кнопкуЕСЛИОШИБКА( заранее ожидаемые значенияУстановите флажок
вам полезна. Просим SubThe_Prist подходит: значения и делать на текущем листе области (толстая черная ситуаций. выполнять сложные вычислениякнопку Microsoft Office
Скрытие значений ошибок путем изменения цвета текста на белый
или оба указанныхЕСЛИОШИБКАУсловное форматированиеФорматПереместите курсор в конец ошибок. Они неТекущий массив вас уделить паруSaifer
-
: Вычисление отдельной формулыSub Formulas_To_Values() Selection.Value
-
все одним касанием ActiveSheet.UsedRange.Value = ActiveSheet.UsedRange.Value линия по периметру)Этот способ прост, известен с хитрой логикойи выберите пункты ниже действия.ии выберите пункт.
формулы. требуют немедленного исправления,. -
секунд и сообщить,: Последний вариант подошел. отследить нельзя -
= Selection.Value End мыши: End Sub И, -
и, удерживая ПРАВУЮ большинству пользователей и и структурой. НоПараметры ExcelИзмените способ отображения ошибок.НД
-
Управление правиламиНа вкладкеВведите однако требуется улучшитьНа вкладке помогла ли она Заменил selection на они все вычисляются Sub
-
Произвела расчёт в Excel, наконец, для превращения клавишу мыши, перетаскиваем заключается в использовании иногда возникают ситуации,
-
> В группе, как показано в.Число,0)
Отображение тире, #Н/Д или НД вместо значения ошибки
представление результатов. СуществуетГлавная вам, с помощью range. А как либо исходя изЯ так понимаю получилась очень большая всех формул в на пару сантиметров специальной вставки: когда лучше быФормулыФормат
примере ниже.
Появится диалоговое окнов списке, т. е. запятую несколько способов скрытияв группе кнопок внизу страницы.
его на автоматический взаимосвязей, либо все надо связать проверку табличка. Как мне книге на всех в любую сторону,
Скрытие значений ошибок в отчете сводной таблицы
-
Выделите диапазон с формулами,
вместо формул в.установите флажок -
Описание функцииДиспетчер правил условного форматированияЧисловые форматы с нулем и значений ошибок иБуфер обмена Для удобства также режим поставить? разом, если выполняется на пустоту и скопировать её, на
листах придется использовать а потом возвращаем которые нужно заменить ячейках остались значения.В разделеДля ошибок отображатьЕСЛИОШИБКА.выберите пункт закрывающей круглой скобкой. индикаторов ошибки в
-
нажмите кнопку приводим ссылку наThe_Prist пересчет формул листа/книги. удаление формулы.
-
другой лит, чтобы вот такую конструкцию: на то же на значения. Например:Контроль ошибок. Введите в поле . С помощью этойВыберите команду(все форматы)Формула ячейках.
-
Копировать оригинал (на английском: Автоматический от чего? Формула может вычислиться,Спасибо! не потерять результаты Sub Formulas_To_Values_Book() 'преобразование местоСкопируйте его (Вы хотите зафиксировать цифрыснимите флажок значение, которое должно функции можно определить,Создать правило
-
Скрытие индикаторов ошибок в ячейках
.=B1/C1Формулы могут возвращать ошибки. языке) . Я задавал вопрос но вернуть все
Юрий М расчётов. При простом
-
формул в значенияв появившемся контекстном менюCtrl+C в вашем отчетеВключить фоновый поиск ошибок отображаться вместо ошибок. содержит ли ячейка.В поле
примет вид по многим причинам.
На вкладкеПри удалении формулы, также - Вы проигнорировали. равно пустое значение.: Почему? -
копировании пишет "решёточки"! во всей книге после перетаскивания выбираемили правой кнопкой на текущую дату.
support.office.com>
Как убрать формулу но оставить конечное значение в microsoft excel
. Чтобы в пустых или возвращает лиОткроется диалоговое окноТип=ЕСЛИОШИБКА(B1/C1,0) Например, формула =1/0Главная будет удалено результат Что Вы под Следовательно надо знать,SaiferКирилл иванов For Each wsКопировать только значения (Copy мыши –
Вы не хотите, чтобыКак убрать формулу из полях отображались ошибки, формула ошибку.Создание правила форматирования
введите. возвращает ошибку #ДЕЛ/0!,в группе
формулы. Если вы автоматически подразумеваете? Какое что Вы понимаете
Преобразование формул в значения
: Он удаляет Все: там есть вставить In ActiveWorkbook.Worksheets ws.UsedRange.Value As Values Only)Копировать клиент увидел формулы, ячейки но оставить удалите из поляНД.;;;Нажмите клавишу ВВОД, чтобы поскольку деление наБуфер обмена не хотите удалить событие? Что должно
- под фразой "после формулы. А мне только результаты, офис
- = ws.UsedRange.Value Next.). по которым вы конечное значение в весь текст. Эта функция возвращаетВ списке(три точки с
- завершить редактирование формулы. 0 недопустимо. Предусмотреныщелкните стрелку под значение, вместо этого произойти, чтобы код того, как формула нужно удаление после то какой? ws End SubПри некотором навыке делаетсяЩелкните правой кнопкой мыши рассчитывали для него Microsoft Excel 2007,Измените способ отображения пустых в ячейке строку
- Выберите тип правила запятой) и нажмитеТеперь в ячейке следующие значения ошибок: кнопкой можно удалить только
сработал? вычислелась". того, как формулаВик лКод нужных макросов можно такое действие очень по выделенным ячейкам
Способ 1. Классический
стоимость проекта (а к примеру у ячеек. "#Н/Д". Синтаксис функции: =
- выберите пункт кнопку вместо ошибки #ДЕЛ/0!
- #ДЕЛ/0!, #Н/Д, #ИМЯ?,Вставить формулы.SaiferЕсли надо пропускать вычислелась.
- : Копируешь, за тем скопировать в новый легко и быстро. и выберите либо то поймет, что меня 3 ячейки
Установите флажокНД()Форматировать только ячейки, которыеОК
должно отображаться значение #ПУСТО!, #ЧИСЛО!, #ССЫЛКА!и выберите пунктВыделите ячейку или диапазон: прошу прощение, если
ячейки с пустымЮрий М правая кнопка мыши, модуль вашего файла Главное, чтобы сосед значок вы заложили 300% со значениями, 4Для пустых ячеек отображать. содержат
Способ 2. Ловкость рук
. Нажмите кнопку 0. и #ЗНАЧ!.Вставить значения
- ячеек, содержащих формулы. чем то обидел.
- значением - то: Ну так и выбираешь "спец. вставка" (жмем под локоть неЗначения (Values) маржи на всякий ячейка это сумма. Введите в полеЩелкните отчет сводной таблицы.
- .ОКПрименение условного форматаЧтобы скрыть значения ошибок,.
Нажмите клавишу DELETE. Сам разобрался. Записал нужен цикл по выполняйте этот макрос и далее ставишьAlt+F11 толкал и руки
Способ 3. Макросами для выделенного диапазона, целого листа или всей книги сразу
: случай). этих трех (С1 значение, которое должно
Появится вкладкаВ разделееще раз.Выделите ячейку с ошибкой можно преобразовать их,Чтобы удалить формулу массива,Чтобы сделать это, скопируйте
макрос в код каждой ячейке с после работы формулы. галочку "значения"чтобы попасть в не дрожали ;)либо наведитесь мышьюВаш файл содержит такое =сумм (С3;С5), вот отображаться в пустыхРабота со сводными таблицамиИзмените описание правилаЗначение 0 в
и на вкладке например, в число убедитесь, что выделить формулу и затем листа, где он проверкой на значение.alessandro2981Красавица южная Visual Basic, далееЕсли вас не пугает на команду больше количество формул, как сделать чтобы ячейках. Чтобы в.в списке ячейке исчезнет. ЭтоГлавная 0, а затем все ячейки в вставьте в одной будет сам проверятьSaifer: Sub ConvertToVaules() If: Копировать- спец. вставка-формулаInsert - Module слово "макросы", тоСпециальная вставка (Paste Special) что Excel начал в ячейки С1 пустых ячейках ничегоExcel 2016 и Excel 2013:Форматировать только ячейки, для связано с тем,нажмите кнопку применить условный формат, диапазоне ячеек, содержащих ячейке с помощью ячейки при заполнении.: В моем случае, ActiveSheet.AutoFilterMode = True
Способ 4. Для ленивых
*****). Запускать их потом это будет, пожалуй,, чтобы увидеть подменю: жутко тормозить при значение осталось, ибо не отображалось, удалите на вкладке которых выполняется следующее что пользовательский форматУсловное форматирование
planetaexcel.ru>
Как в Excel избавиться от формулы, но оставить результат вычисления?
позволяющий скрыть значение. формулу массива. Это параметра вставить значения. Всем спасибо, кто когда ячейка с Then Dim rArea: внизу наведите курсор можно через вкладку
самый быстрый способ.Из него можно любых, даже самых я собираюсь удалить
из поля весьАнализ условие;;;.Создание образца ошибки
можно сделать:Выделите ячейку или диапазон
отписался по этой формулой стала Не As Range For на "лист" иРазработчик - Макросы (DeveloperМакрос для превращения всех выбрать варианты вставки простых изменениях в значения из ячеек текст. Чтобы отображалисьв группевыберите пунктпредписывает скрывать любые
Удалить формулу и оставить значение
Выберите командуОткройте чистый лист илиЩелкните любую ячейку в
ячеек, содержащих формулу. теме. пустой, необходимо удалить Each rArea In выберите переместить/скопировать и - Macros) формул в значения
значений с сохранением нем, т.к. постоянно
С3;С5 нулевые значения, снимитеСводная таблица
Ошибки числа в ячейке.Создать правило создайте новый.
формуле массива.
Если формула являетсяThe_Prist
формулу, чтобы остались Selection.SpecialCells(xlCellTypeVisible).Areas rArea.Value = галочку поставьте "создатьили сочетанием клавиш в выделенном диапазоне дизайна или числовых
их пересчитывает (хотя,Александр васильев этот флажок.щелкните стрелку рядом
. Однако фактическое значение.ВведитеНа вкладке " формулой массива, необходимо: Да ничем не значения. Т. Е. rArea.Value rArea.NumberFormat = копию " ентерAlt+F8 (или нескольких диапазонах, форматов исходных ячеек. честности ради, надо
: Копируеш ячейку, потомВ левом верхнем углу с командойНажмите кнопку (0) по-прежнему хранитсяВ диалоговом окне3
Главная сначала выбрать все обидели. Просто Вы Это цикл на "0" Next Else
все должно скопироваться. Макросы будут работать выделенных одновременно сВ старых версиях сказать, что это на ней же ячейки с формулой,ПараметрыФормат в ячейке.Создание правила форматированияв ячейку B1," в группе ячейки в диапазоне просите автоматизировать, а проверку пустых ячеек,
With Selection .NumberFormat на другой лист. в любой книге,Ctrl Excel таких удобных можно решить временным
правый клик, специальная которая возвращает ошибку,и выберите пункти откройте вкладкуОписанная ниже процедура позволяетвыберите параметр0 " ячеек, содержащих формулу в какой момент и если она = "0" .Value а что бы
пока открыт файл,) выглядит так: желтых кнопочек нет, отключением автоматических вычислений
вставка, только значения появляется треугольник (индикаторПараметрыШрифт отформатировать ячейки сФорматировать только ячейки, которые — в ячейку C1Редактирование массива:
не проясняете. Хорошо, Не пустая, то = .Value End от формулы избавиться- где они хранятся.Sub Formulas_To_Values_Selection() 'преобразование
но можно просто на вкладкеПользователь удален ошибки). Чтобы отключить.. ошибками таким образом, содержат и формулу
" нажмите кнопкуЩелкните любую ячейку в что разобрались сами. удалить в ней With End If в ячейке уберите И помните, пожалуйста, формул в значения выбрать командуФормулы – Параметры вычислений: Никак видимо -
его отображение, выполнитеExcel 2010 и ExcelЩелкните стрелку, чтобы открыть чтобы текст в.=B1/C1Найти и выделить
planetaexcel.ru>
формуле массива.
Убрать формулу в Excel оставив значения - 3 способа как находить дубликаты в Excel
Работа с формулами в Экселе позволяет значительно облегчить и автоматизировать различные вычисления. Вместе с тем, далеко не всегда нужно, чтобы результат был привязан к выражению. Например, при изменении значений в связанных ячейках, изменятся и итоговые данные, а в некоторых случаях это не нужно. Кроме того, при переносе скопированной таблицы с формулами в другую область значения могут «потеряться». Ещё одним поводом их спрятать может служить ситуация, когда вы не хотите, чтобы другие лица видели, как проводятся в таблице расчеты. Давайте выясним, какими способами можно удалить формулу в ячейках, оставив только результат вычислений.
Содержание
Процедура удаления
К сожалению, в Excel нет инструмента, который моментально убирал бы формулы из ячеек, а оставлял там только значения. Поэтому приходится искать более сложные пути решения проблемы.
Способ 1: копирование значений через параметры вставки
Скопировать данные без формулы в другую область можно при помощи параметров вставки.
- Выделяем таблицу или диапазон, для чего обводим его курсором с зажатой левой кнопкой мыши. Пребывая во вкладке «Главная», делаем щелчок по значку «Копировать», который размещен на ленте в блоке «Буфер обмена».
- Выделяем ячейку, которая будет верхней левой ячейкой вставляемой таблицы. Выполняем щелчок по ней правой кнопкой мышки. Будет активировано контекстное меню. В блоке «Параметры вставки» останавливаем выбор на пункте «Значения». Он представлен в виде пиктограммы с изображением цифр «123».
После выполнения этой процедуры диапазон будет вставлен, но только в виде значений без формул. Правда при этом потеряется также и исходное форматирование. Поэтому форматировать таблицу придется вручную.
Способ 2: копирование специальной вставкой
Если вам нужно сохранить исходное форматирование, но вы не хотите тратить время на ручную обработку таблицы, то существует возможность для этих целей использовать «Специальную вставку».
- Копируем тем же способом, что и прошлый раз содержимое таблицы или диапазона.
- Выделяем всю область вставки или её левую верхнюю ячейку. Делаем щелчок правой кнопкой мыши, тем самым вызывая контекстное меню. В открывшемся списке выбираем пункт «Специальная вставка». Далее в дополнительном меню жмем на кнопку «Значения и исходное форматирование», которая размещается в группе «Вставить значения» и представляет собой пиктограмму в виде квадрата, где изображены цифры и кисточка.
После этой операции данные будут скопированы без формул, но при этом сохранится исходное форматирование.
Способ 3: удаление формулы из исходной таблицы
До этого мы говорили, как убрать формулу при копировании, а теперь давайте выясним, как её удалить из исходного диапазона.
- Производим копирование таблицы любым из тех методов, о которых шел разговор выше, в пустую область листа. Выбор конкретного способа в нашем случае значения иметь не будет.
- Выделяем скопированный диапазон. Щелкаем по кнопке «Копировать» на ленте.
- Выделяем первоначальный диапазон. Щелкаем по нему правой кнопкой мыши. В контекстном списке в группе «Параметры вставки» выбираем пункт «Значения».
- После того, как данные были вставлены, можно удалить транзитный диапазон. Выделяем его. Вызываем контекстное меню кликом правой кнопки мыши. Выбираем в нем пункт «Удалить...».
- Открывается небольшое окошко, в котором нужно установить, что именно нужно удалить. В конкретно нашем случае транзитный диапазон находится внизу от исходной таблицы, поэтому нам нужно удалить строки. Но если бы он располагался сбоку от неё, то следовало бы удалить столбцы, тут очень важно не перепутать, так как можно уничтожить основную таблицу. Итак, выставляем настройки удаления и жмем на кнопку «OK».
После выполнения указанных действий все ненужные элементы будут удалены, а формулы из исходной таблицы исчезнут.
Способ 4: удаление формул без создания транзитного диапазона
Можно сделать ещё проще и вообще не создавать транзитный диапазон. Правда в этом случае нужно действовать особенно внимательно, ведь все действия будут выполняться в границах таблицы, а это значит, что любая ошибка может нарушить целостность данных.
- Выделяем диапазон, в котором нужно удалить формулы. Кликаем по кнопке «Копировать», размещенной на ленте или набираем на клавиатуре комбинацию клавиш Ctrl+C. Эти действия равнозначны.
- Затем, не снимая выделения, кликаем правой кнопкой мыши. Запускается контекстное меню. В блоке «Параметры вставки» жмем на пиктограмму «Значения».
Таким образом, все данные будут скопированы и тут же вставлены, как значения. После этих действий формул в выделенной области не останется.
Способ 5: использование макроса
Для удаления формул из ячеек можно также использовать макросы. Но для этого нужно сначала активировать вкладку разработчика, а также включить саму работу макросов, если они у вас не активны. Как это сделать, можно узнать в отдельной теме. Мы же поговорим непосредственно о добавлении и использовании макроса для удаления формул.
- Переходим во вкладку «Разработчик». Кликаем по кнопке «Visual Basic», размещенной на ленте в блоке инструментов «Код».
- Запускается редактор макросов. Вставляем в него указанный ниже код:
Sub Удаление_формул() Selection.Value = Selection.Value End Sub
После этого закрываем окно редактора стандартным способом, нажав на кнопку в правом верхнем углу.
- Возвращаемся к листу, на котором расположена интересующая нас таблица. Выделяем фрагмент, где расположены формулы, которые нужно удалить. Во вкладке «Разработчик» жмем на кнопку «Макросы», размещенную на ленте в группе «Код».
- Открывается окно запуска макросов. Ищем в нем элемент под названием «Удаление_формул», выделяем его и жмем на кнопку «Выполнить».
После этого действия все формулы в выделенной области будут удалены, а останутся только результаты вычислений.
Урок: Как включить или отключить макросы в Excel
Урок: Как создать макрос в Excel
Способ 6: Удаление формулы вместе с результатом
Впрочем, бывают случаи, когда нужно удалить не только формулу, но и результат. Сделать это ещё проще.
- Выделяем диапазон, в котором размещены формулы. Кликаем правой кнопкой мыши. В контекстном меню останавливаем выбор на пункте «Очистить содержимое». Если вы не хотите вызывать меню, то можно просто после выделения нажать клавишу Delete на клавиатуре.
- После этих действий все содержимое ячеек, включая формулы и значения, будет удалено.
Как видим, существует целый ряд способов, с помощью которых можно удалить формулы, как при копировании данных, так и непосредственно в самой таблице. Правда, штатного инструмента Excel, который автоматически убирал бы выражение одним кликом, к сожалению, пока не существует. Таким способом можно удалить только формулы вместе со значениями. Поэтому приходиться действовать обходными путями через параметры вставки или же с применением макросов.
Как удалить функцию, оставив текущее значение в Excel
При работе с Excel-таблицами пользователи часто прибегают к различным функциям, которые являются динамическими. Они связаны с несколькими ячейками и выводят результат, отталкиваясь непосредственно от текущих данных. Однако иногда от такой динамики нужно избавиться, оставив только текущее статическое значение.
В рамках этой статьи я расскажу о двух способах, которые помогут реализовать это, выполнив минимальное количество действий.
Для чего нужно удаление функций
Краткая информация о том, когда вообще может понадобиться осуществление рассматриваемого действия. Например, вы закончили определенные расчеты и получили число, с которым нужно в дальнейшем взаимодействовать. Однако может возникнуть ситуация, когда в одной из связанных ячеек появятся изменения, что приведет к изменению и финального числа. Это помешает использовать его старое значение и может привести к тому, что вы его просто потеряете и не вспомните.
Можно удалить формулу, которая отвечает за перерасчет, оставив только текущее число. Это не вызовет проблем с его последующим использованием и вы можете быть уверены в том, что изначальное значение не поменяется. Как уже было сказано выше, удаляется функция двумя разными методами, которые показаны далее.
Вариант 1: Вставка с заменой
Начнем с первого, самого распространенного метода. Он заключается в копировании данных в ячейках с дальнейшей вставкой в те же самые клетки, но как обычного значения. Это приведет к замене старых значений, обеспечив удаление функции с сохранением текущего числа. Вам достаточно выполнить следующие действия:
-
Зажмите левую кнопку мыши, выделите все необходимые ячейки и используйте комбинацию Ctrl+C для копирования.
-
После этого не снимайте выделение, а кликните по одной из клеток правой кнопкой мыши. На следующем скриншоте вы видите, какой вариант специальной вставки нужно выбрать.
-
Обратите внимание на то, что теперь в ячейке отображается только число без старой формулы – цель достигнута.
Вариант 2: Опция «Копировать только значения»
В рамках этого варианта сохранение значений произойдет тоже с их копированием и вставкой, однако для этого не придется использовать горячие клавиши, поскольку весь алгоритм заключается в переносе ячеек в другое место с последующим возвращением в предыдущее. Таким образом, удалится старая формула и сохранятся только числа.
-
Выделите весь диапазон данных с зажатой левой кнопкой мыши.
-
Зажмите правый край клетки правой кнопкой мыши и передвиньте его вправо, чтобы на экране появился зеленый прямоугольник.
-
Не отпускайте кнопку мыши и сразу же передвиньте этот прямоугольник в изначальное положение. Отпустите ПКМ и из появившегося контекстного меню выберите пункт «Копировать только значения».
-
Вы достигли того же самого результата, удалив формулы и сохранив значения, однако выполнили немного другие действия.
Теперь решайте сами, какой из этих вариантов вам больше подходит. Используйте их для удаления функций и не забывайте о том, что всегда можно осуществить те же самые операции без мыши, обратившись к горячим клавишам, полное описание которых есть в официальной документации Excel от Microsoft.
Как удалить значение и оставить формулу в Excel
Как удалить значение и оставить формулу в Excel
Добрый день, уважаемые читатели и подписчики блога! Сегодня мы поговорим о том, как удалять из таблиц только значения ячеек так, чтобы остались формулы для расчётов.
Представим такую ситуацию: есть таблица с данными и нужными мне формулами, она повторяется из квартала в квартал, она довольно большая и я не знаю где точно располагаются формулы. Как в этой таблице удалить только числа не затронув формулы?
Можно воспользоваться кнопкой "Показать формулы" на вкладке "Формулы".
Да, формулы отобразятся, но если таблица будет занимать гораздо больше места? Некоторые формулы будут не видны и их можно по ошибке удалить. Что недопустимо.
Поэтому пойдём другим путём. Воспользуемся возможностями поиска и выделения программы Excel.
Выделяем все числа в нашей таблице.
На вкладке "Главная" в блоке кнопок "Редактирование" нажимаем кнопку "Найти и выделить", а затем "Выделить группу ячеек".
В появившемся окне устанавливаем переключатель на пункт "Константы" и отмечаем галкой "Числа". Жмём "ОК".
Вуаля, в таблице останутся выделенными только числа, которые затем можно смело удалить.
После нажатия кнопки "Delete" значения формул будут равны нолю. Всё готово. Таким образом можно работать с любыми объёмами данных и не бояться случайно удалить формулу.
Вас также может заинтересовать урок - Данные таблицы на карте в Excel.
Преобразование формул в значения
Формулы – это хорошо. Они автоматически пересчитываются при любом изменении исходных данных, превращая Excel из "калькулятора-переростка" в мощную автоматизированную систему обработки поступающих данных. Они позволяют выполнять сложные вычисления с хитрой логикой и структурой. Но иногда возникают ситуации, когда лучше бы вместо формул в ячейках остались значения. Например:
- Вы хотите зафиксировать цифры в вашем отчете на текущую дату.
- Вы не хотите, чтобы клиент увидел формулы, по которым вы рассчитывали для него стоимость проекта (а то поймет, что вы заложили 300% маржи на всякий случай).
- Ваш файл содержит такое больше количество формул, что Excel начал жутко тормозить при любых, даже самых простых изменениях в нем, т.к. постоянно их пересчитывает (хотя, честности ради, надо сказать, что это можно решить временным отключением автоматических вычислений на вкладке Формулы – Параметры вычислений).
- Вы хотите скопировать диапазон с данными из одного места в другое, но при копировании "сползут" все ссылки в формулах.
В любой подобной ситуации можно легко удалить формулы, оставив в ячейках только их значения. Давайте рассмотрим несколько способов и ситуаций.
Способ 1. Классический
Этот способ прост, известен большинству пользователей и заключается в использовании специальной вставки:
- Выделите диапазон с формулами, которые нужно заменить на значения.
- Скопируйте его правой кнопкой мыши – Копировать (Copy).
- Щелкните правой кнопкой мыши по выделенным ячейкам и выберите либо значок Значения (Values):
либо наведитесь мышью на команду Специальная вставка (Paste Special), чтобы увидеть подменю:
Из него можно выбрать варианты вставки значений с сохранением дизайна или числовых форматов исходных ячеек.В старых версиях Excel таких удобных желтых кнопочек нет, но можно просто выбрать команду Специальная вставка и затем опцию Значения (Paste Special - Values) в открывшемся диалоговом окне:
Способ 2. Только клавишами без мыши
При некотором навыке, можно проделать всё вышеперечисленное вообще на касаясь мыши:
- Копируем выделенный диапазон Ctrl+C
- Тут же вставляем обратно сочетанием Ctrl+V
- Жмём Ctrl, чтобы вызвать меню вариантов вставки
- Нажимаем клавишу с русской буквой З или используем стрелки, чтобы выбрать вариант Значения и подтверждаем выбор клавишей Enter:
Способ 3. Только мышью без клавиш или Ловкость Рук
Этот способ требует определенной сноровки, но будет заметно быстрее предыдущего. Делаем следующее:
- Выделяем диапазон с формулами на листе
- Хватаем за край выделенной области (толстая черная линия по периметру) и, удерживая ПРАВУЮ клавишу мыши, перетаскиваем на пару сантиметров в любую сторону, а потом возвращаем на то же место
- В появившемся контекстном меню после перетаскивания выбираем Копировать только значения (Copy As Values Only).
После небольшой тренировки делается такое действие очень легко и быстро. Главное, чтобы сосед под локоть не толкал и руки не дрожали ;)
Способ 4. Кнопка для вставки значений на Панели быстрого доступа
Ускорить специальную вставку можно, если добавить на панель быстрого доступа в левый верхний угол окна кнопку Вставить как значения. Для этого выберите Файл - Параметры - Панель быстрого доступа (File - Options - Customize Quick Access Toolbar). В открывшемся окне выберите Все команды (All commands) в выпадающем списке, найдите кнопку Вставить значения (Paste Values) и добавьте ее на панель:
Теперь после копирования ячеек с формулами будет достаточно нажать на эту кнопку на панели быстрого доступа:
Кроме того, по умолчанию всем кнопкам на этой панели присваивается сочетание клавиш Alt + цифра (нажимать последовательно). Если нажать на клавишу Alt, то Excel подскажет цифру, которая за это отвечает:
Способ 5. Макросы для выделенного диапазона, целого листа или всей книги сразу
Если вас не пугает слово "макросы", то это будет, пожалуй, самый быстрый способ.
Макрос для превращения всех формул в значения в выделенном диапазоне (или нескольких диапазонах, выделенных одновременно с Ctrl) выглядит так:
Sub Formulas_To_Values_Selection() 'преобразование формул в значения в выделенном диапазоне(ах) Dim smallrng As Range For Each smallrng In Selection.Areas smallrng.Value = smallrng.Value Next smallrng End Sub
Если вам нужно преобразовать в значения текущий лист, то макрос будет таким:
Sub Formulas_To_Values_Sheet() 'преобразование формул в значения на текущем листе ActiveSheet.UsedRange.Value = ActiveSheet.UsedRange.Value End SubИ, наконец, для превращения всех формул в книге на всех листах придется использовать вот такую конструкцию:
Sub Formulas_To_Values_Book() 'преобразование формул в значения во всей книге For Each ws In ActiveWorkbook.Worksheets ws.UsedRange.Value = ws.UsedRange.Value Next ws End Sub
Код нужных макросов можно скопировать в новый модуль вашего файла (жмем Alt+F11 чтобы попасть в Visual Basic, далее Insert - Module). Запускать их потом можно через вкладку Разработчик - Макросы (Developer - Macros) или сочетанием клавиш Alt+F8. Макросы будут работать в любой книге, пока открыт файл, где они хранятся. И помните, пожалуйста, о том, что действия выполненные макросом невозможно отменить - применяйте их с осторожностью.
Способ 6. Для ленивых
Если ломает делать все вышеперечисленное, то можно поступить еще проще - установить надстройку PLEX, где уже есть готовые макросы для конвертации формул в значения и делать все одним касанием мыши:
В этом случае:
- всё будет максимально быстро и просто
- можно откатить ошибочную конвертацию отменой последнего действия или сочетанием Ctrl+Z как обычно
- в отличие от предыдущего способа, этот макрос корректно работает, если на листе есть скрытые строки/столбцы или включены фильтры
- любой из этих команд можно назначить любое удобное вам сочетание клавиш в Диспетчере горячих клавиш PLEX
Ссылки по теме
Удалить или очистить формулу
Удалить формулу, но сохранить результаты
Для этого скопируйте формулу и вставьте ее в ту же ячейку, используя опцию Вставить значения.
-
Выберите ячейку или диапазон ячеек, содержащих формулу.
Если формула является формулой массива, необходимо сначала выбрать все ячейки в диапазоне ячеек, содержащих формулу массива:-
Щелкните ячейку в формуле массива.
-
На вкладке Главная в группе Редактирование щелкните Найдите и выберите , а затем щелкните Перейти к .
-
Нажмите кнопку Специально .
-
Щелкните Текущий массив.
-
-
На вкладке Домашние инструменты в группе Буфер обмена щелкните Копировать.
-
На вкладке Домашние инструменты в группе Буфер обмена щелкните стрелку под Вставить , а затем щелкните Вставить Значения .
Удалить формулу массива
Чтобы удалить формулу массива, необходимо выбрать все ячейки в диапазоне ячеек, содержащих формулу массива. Для этого:
-
Щелкните ячейку в формуле массива.
-
На вкладке Главная в группе Редактирование щелкните Найдите и выберите , а затем щелкните Перейти к .
-
Нажмите кнопку Специально .
-
Щелкните Текущий массив.
-
Нажмите клавишу DELETE.
Быстрая замена формул
значениемВы создаете отчет о продажах в Excel. Сводная таблица находится на отдельном рабочем листе и содержит формулы со ссылками на ячейки из других рабочих листов и даже внешних рабочих книг. Для дальнейшего анализа вам понадобятся только значения, поэтому вы хотите удалить формулы из списка, не теряя слишком много времени. Хотите знать, как это сделать? Используйте один из следующих методов. Первый основан на команде Excel, второй основан на макросе.Выбирайте, какой из них вам удобнее.
Предположим, что таблица, содержащая формулы, выглядит так, как показано на рисунке 1.
Рис. 1. Пример выписки с ненужными формулами
В столбце Е и строке 11 хотелось бы оставить только суммы.
Способ 1: Вставить специальную команду
Для удаления формул:
1. Выделите диапазон ячеек с формулами (в примере Е2:Е10) и нажмите Копировать на панели инструментов (или воспользуйтесь комбинацией клавиш Ctrl+C) .
3. Установите флажок «Значения» и подтвердите, нажав кнопку «ОК».
Рис. 2. Выделенное поле значений
4. Аналогичным образом замените формулы значениями в строке 11.
Все кажется простым, когда вы знаете, в каких ячейках находятся формулы. Если у вас возникли проблемы с их поиском, вы можете сначала использовать команду «Перейти», а затем раскрасить ячейки.
Для этого:
1. В меню Правка выберите Перейти (можно также использовать комбинацию клавиш Ctrl + G).
3. Установите флажок «Формулы» и нажмите «ОК».
Ячейки формулы будут выделены, и теперь вы можете задать им любой цвет заливки, нажав кнопку на панели инструментов.
Если вы хотите использовать показанный метод удаления формул, вы столкнетесь с определенным ограничением.Нельзя копировать несогласованные диапазоны ячеек. Если выделить диапазон ячеек E2:E10, а затем, удерживая клавишу Ctrl, второй диапазон B11:D11 и попытаться запустить команду копирования, появится следующее сообщение:
Рис. 3. Несовместимые диапазоны не могут быть скопированы
Способ 2: макрос
Вместо выполнения всех представленных шагов можно использовать соответствующий макрос. Просто запустите их, и формулы исчезнут.
Для этого:
2. Выберите «Модуль» в меню «Вставка».
Sub Remove_formuly ()
Selection.Copy
Selection.PasteSpecial Paste: = xlValues, _
Operation: = xlNone, SkipBlanks: = False, Transpose: = False
Application.CutCopyMode = False
End Sub
Чтобы использовать макрос:
1. Выберите диапазон ячеек, из которых формулы должны быть удалены (в примере E2: E10 или B11: D11) и в меню Сервис выберите команду Макрос / Макросы (вы можете также используйте левый Alt + F8).
Формулы будут удалены.
Совет
Если вы планируете часто использовать макрос, мы предлагаем назначить его сочетанию клавиш.
Для этого:
2. Выберите имя макроса и нажмите кнопку «Параметры».
3. Поместите курсор в поле быстрого доступа и нажмите, например, Shift + Y на клавиатуре
С этого момента вы сможете запускать свой макрос намного эффективнее.
.Вставка формул и функций в Numbers на Mac
Операторы сравнения позволяют проверить, равны ли значения в двух ячейках или одно из них больше другого. Для этого введите в ячейку соответствующее выражение, например А1>А2, что означает, что значение в ячейке А1 больше, чем значение в ячейке А2. Результат оператора сравнения выражается как «истина» или «ложь».
-
Щелкните ячейку, в которой должен появиться результат сравнения, затем введите знак равенства (=).
Откроется редактор формул. Перетащите левую часть редактора формул, чтобы переместить его. Вы можете изменить его размер, перетащив любой из его внешних краев.
-
Щелкните ячейку, значение которой вы хотите сравнить, или введите значение для сравнения.
-
Введите оператор сравнения (>,> =, =, <>, <или <=), а затем выберите ячейку, значение которой вы хотите сравнить, или введите статическое значение для сравнения.
-
По завершении нажмите клавишу «Ввод» или щелкните в редакторе формул.
Если щелкнуть, вы выйдете из редактора формул без сохранения изменений.
Значок появляется в ячейке результатов, если формула содержит ошибку. Нажмите на этот значок, чтобы увидеть сообщение об ошибке. Если в сообщении указано, что ошибка вызвана другой ячейкой, можно щелкнуть ссылку на ячейку, чтобы выбрать ячейку с ошибкой.
.Скрыть нулевые значения в Excel
Смотреть видео - Как скрыть нулевые значения в Excel + Как удалить нулевые значения
Если вы предпочитаете читать, а не смотреть видео, вот полное письменное руководство.
Иногда в Excel может потребоваться , чтобы скрыть нулевые значения в наборе данных и показать эти ячейки как пустые.
Предположим, у вас есть набор данных, как показано ниже, и вы хотите скрыть значение 0 во всех этих ячейках (или заменить его чем-то вроде дефиса или текста «Недоступно»).
Хотя для небольшого набора данных это можно сделать вручную, при работе с большими наборами данных вам потребуются более эффективные способы.
В этом уроке я покажу вам несколько способов скрыть нулевые значения в Excel и один способ выбрать и удалить все нулевые значения из набора данных.
Важное примечание . Если вы скроете 0 в ячейке с помощью методов, описанных в этом руководстве, он скроет только 0, но не удалит его.Хотя ячейки могут казаться пустыми, они по-прежнему содержат нули. И если вы используете эти ячейки (в которых есть скрытые нули) в какой-либо формуле, они будут использоваться в формуле.Давайте начнем и погрузимся в каждый из этих методов!
Автоматически скрывать нулевое значение в ячейках
Excel имеет встроенную функцию, позволяющую автоматически скрывать все нулевые значения для всего рабочего листа.
Просто снимите флажок в параметрах Excel, и изменение будет применено ко всему рабочему листу.
Предположим, у вас есть набор данных о продажах, как показано ниже, и вы хотите скрыть все нулевые значения и вместо этого отобразить пустые ячейки.
Ниже приведены шаги, чтобы скрыть нули во всех ячейках книги в Excel:
- Откройте книгу, в которой вы хотите скрыть все нули
- Перейдите на вкладку «Файл»
- Нажмите Опции
- В открывшемся диалоговом окне «Параметры Excel» нажмите «Дополнительно» на левой панели
- Прокрутите вниз до раздела «Параметры отображения для этого листа» и выберите лист, на котором вы хотите скрыть нули.
- Снимите флажок «Показывать ноль в ячейках с нулевым значением»
- Нажмите OK
Приведенные выше шаги немедленно скроют нули во всех ячейках выбранного листа.
Это изменение также затрагивает ячейки, в которых нуль является результатом формулы.
Помните, что этот метод только скрывает значение 0 в ячейках и не удаляет их. 0 все еще там, он просто скрыт.
Это лучший и самый быстрый способ скрыть нулевые значения в Excel.Но это полезно только в том случае, если вы хотите скрыть нулевые значения на всем листе. Если вы хотите скрыть нулевые значения только в определенном диапазоне ячеек, вам лучше использовать другие методы, обсуждаемые далее в этом руководстве.Скрыть нулевое значение в ячейках с
условным форматированиемХотя описанный выше метод является наиболее удобным, он не скрывает нули только в пределах определенного диапазона (скорее, он скрывает нули по всему рабочему листу).
Если вы хотите скрыть нули только для определенного набора данных (или нескольких наборов данных), вы можете использовать условное форматирование.
С условным форматированием вы можете применить определенный формат на основе значений в ячейках. Итак, если значение в некоторых ячейках равно 0, вы можете просто запрограммировать условное форматирование, чтобы скрыть их (или даже выделить их, если хотите).
Предположим, у вас есть набор данных, как показано ниже, и вы хотите скрыть нули в ячейках.
Ниже приведены шаги, чтобы скрыть нули в Excel с помощью условного форматирования:
- Выберите набор данных
- Перейдите на вкладку «Главная»
- В группе «Стили» нажмите «Условное форматирование».
- Наведите курсор на параметры «Правило выделения ячейки» и нажмите «Равно» в появившихся параметрах.
- В диалоговом окне "Равно" введите "0" в левом поле.
- Выберите раскрывающееся меню «Формат» и нажмите «Пользовательский формат».
- В диалоговом окне «Формат ячеек» выберите вкладку «Шрифт».
- Выберите белый цвет в раскрывающемся списке Цвет.
- Нажмите кнопку ОК.
- Нажмите кнопку ОК.
Вышеуказанные шаги изменяют цвет шрифта ячеек с нулями на белый, делая ячейки пустыми.
Этот обходной путь работает, только если лист имеет белый фон. Когда ячейки имеют другой цвет фона, белый цвет может сделать нули видимыми.
Если вы действительно хотите скрыть нули в ячейках, где ячейки кажутся пустыми, независимо от цвета фона, присвоенного ячейкам, вы можете сделать, как показано ниже.
- Выберите набор данных
- Перейдите на вкладку «Главная»
- В группе «Стили» нажмите «Условное форматирование».
- Наведите курсор на параметры «Правило выделения ячейки» и нажмите «Равно» в появившихся параметрах.
- В диалоговом окне «Равно» введите 0 в левом поле.
- Выберите раскрывающееся меню «Формат» и нажмите «Пользовательский формат».
- В диалоговом окне «Формат ячеек» выберите вкладку «Число» и нажмите «Пользовательский» на левой панели.
- В опции Пользовательский формат введите следующее в поле Тип: ;;; (три точки с запятой без пробелов)
- Нажмите кнопку ОК.
- Нажмите кнопку ОК.
Вышеуказанные шаги изменяют пользовательское форматирование ячеек, которые имеют значение 0.
При использовании трех точек с запятой в качестве формата скрывает все в ячейке (будь то числовой или текстовый). А поскольку этот формат применяется только к ячейкам со значением 0, он скрывает только нули, а не остальную часть набора данных.
Вы также можете использовать этот метод, чтобы выделить эти ячейки другим цветом (например, красным или желтым).Для этого просто добавьте еще один шаг (после шага 8), где вам нужно выбрать вкладку «Заливка» в диалоговом окне «Формат ячеек», а затем выбрать цвет, которым вы хотите выделить ячейки.Оба описанных выше метода также работают, когда 0 является результатом формулы в ячейке.
Примечание. Оба метода в этом разделе скрывают только нулевые значения. Он не удаляет эти нули. Это означает, что если вы используете эти ячейки в своих расчетах, то все нули также будут использоваться.Скрыть нулевое значение с пользовательским форматированием
Другой способ скрыть нулевые значения в наборе данных — создать собственный формат, который скрывает значение в ячейках, содержащих нули, в то время как любое другое значение отображается должным образом.
Ниже приведены шаги по использованию пользовательского формата для скрытия нулей в ячейках:
- Выберите весь набор данных, к которому вы хотите применить этот формат. Если вы хотите это для всего листа, выберите все ячейки
- Выделив ячейки, перейдите на вкладку «Главная».
- В группе Ячейки щелкните Формат
- В раскрывающемся списке выберите Формат ячеек. Откроется диалоговое окно «Формат ячеек ».
- В диалоговом окне «Формат ячеек» выберите вкладку «Число»
- В параметрах на левой панели щелкните Пользовательский.90 164
- В параметрах справа введите следующий формат в поле Тип: 0;-0 ;;@
- Нажмите OK
Вышеуказанные шаги применили бы пользовательский формат к выбранным ячейкам, где только те ячейки, которые имеют значение 0, форматируются так, чтобы скрыть значение, в то время как другие ячейки отображают данные, как ожидалось.
Как работает этот пользовательский формат?
В Excel можно указать четыре типа форматов данных:
- Положительные числа
- Отрицательные числа
- Ноль
- Текст
Вы можете указать формат для каждого из этих типов данных.
В большинстве случаев существует формат по умолчанию, Общий, который делает видимыми все эти типы данных. Однако вы можете создать специальный формат для каждого из этих типов данных.
Ниже приведена структура пользовательского формата, которой необходимо следовать при применении пользовательского формата к каждому из этих типов данных:
;;;
В описанном выше методе я указал формат:
0;-0 ;@
Приведенный выше формат делает следующее:
- Положительные числа отображаются как есть
- Отрицательные числа отображаются со знаком минус
- Нули скрыты (поскольку формат не указан)
- Текст отображается как есть
Заменить нули тире или другим текстом
В некоторых случаях может потребоваться не только скрыть нули в ячейках, но и заменить их чем-то более значимым.
Например, вы можете удалить нули и заменить их тире или текстом, таким как «Недоступно» или «Данные еще не получены»
Это снова возможно с пользовательским форматированием чисел, где вы можете указать, что ячейка должна отображать вместо нуля.
Ниже приведены шаги для преобразования 0 в тире в Excel:
- Выберите весь набор данных, к которому вы хотите применить этот формат. Если вы хотите это для всего листа, выберите все ячейки
- Выделив ячейки, перейдите на вкладку «Главная».
- В группе Ячейки щелкните Формат
- В раскрывающемся списке выберите Формат ячеек.Откроется диалоговое окно «Формат ячеек ».
- В диалоговом окне «Формат ячеек» выберите вкладку «Число»
- В параметрах на левой панели щелкните Пользовательский.
- В параметрах справа введите следующий формат в поле Тип: 0;-0;-;@
- Нажмите OK
Вышеуказанные шаги сохранят все остальные ячейки такими, какие они есть, и заменят ячейки со значением 0 на прочерк (-).
Примечание : В этом примере я решил заменить ноль тире, но вы также можете использовать любой текст.Например, если вы хотите отобразить NA в ячейках, вы можете использовать следующий формат: 0; -0; «Неприменимо»; @ Совет для профессионалов : Вы также можете указать несколько цветов текста в пользовательском форматировании чисел. Например, если вы хотите, чтобы в ячейках с нулями текст NA отображался красным цветом, вы можете использовать следующий формат: 0;-0;[Красный] "Неприменимо";@Скрыть нулевые значения в сводных таблицах
Может быть два сценария, когда сводная таблица показывает значение 0:
- Ячейки исходных данных, сведенные в сводную таблицу, имеют значения 0
- Ячейки исходных данных, суммированные в сводной таблице, пусты, а сводная таблица отформатирована таким образом, что пустые ячейки отображаются как нулевые
Давайте посмотрим, как скрыть нули в сводной таблице в каждом сценарии:
Когда ячейки исходных данных равны 0
Ниже приведен пример набора данных, который я использовал для создания сводной таблицы.Обратите внимание, что цифры количества/дохода/прибыли для всех записей для "Запада" равны 0,
.
Сводная таблица, созданная с использованием этого набора данных, выглядит следующим образом:
Поскольку в наборе данных для всех записей для западного региона были нули, в сводной таблице для западного региона отображается ноль.
Если вы хотите скрыть ноль в этой сводной таблице, вы можете использовать для этого условное форматирование.
Однако условное форматирование в сводной таблице в Excel работает немного иначе, чем обычные данные.
Вот шаги, чтобы применить условное форматирование к сводной таблице, чтобы скрыть нули:
- Выберите любую ячейку в столбце с нулями в сводке сводной таблицы (не выбирайте все ячейки, а только одну ячейку)
- Перейдите на вкладку «Главная»
- В группе «Стили» нажмите «Условное форматирование».
- Наведите курсор на параметры «Правило выделения ячейки» и нажмите «Равно» в появившихся параметрах.
- В диалоговом окне «Равно» введите 0 в левом поле.
- Выберите раскрывающееся меню «Формат» и нажмите «Пользовательский формат».
- В диалоговом окне «Формат ячеек» выберите вкладку «Число» и нажмите «Пользовательский» на левой панели.
- В опции Пользовательский формат введите следующее в поле Тип: ;;; (три точки с запятой без пробелов)
- Нажмите кнопку ОК. Вы увидите небольшой значок параметров форматирования справа от выбранной ячейки.
- Нажмите значок «Параметры форматирования»
- В доступных параметрах выберите - Все ячейки, показывающие значения «Общего дохода» для «Региона».
Приведенные выше шаги скрывают нули в сводной таблице, поэтому, если вы измените структуру сводной таблицы (добавив в нее больше заголовков строк/столбцов, нули все равно будут скрыты).
Если вы хотите узнать больше о том, как это работает, вот подробное руководство о правильном пути к для применения условного форматирования к сводным таблицам .
Когда ячейки исходных данных имеют пустые ячейки
Ниже приведен пример набора данных с пустыми ячейками для всех записей для Запада.
90 340
Если я создам сводную таблицу, используя эти данные, я получу результат, как показано ниже.
По умолчанию в сводной таблице отображаются пустые ячейки, если все ячейки в исходных данных пусты. Но если вы все еще видите 0 в этом случае, выполните следующие шаги, чтобы скрыть ноль и показать пустые ячейки:
- Щелкните правой кнопкой мыши любую ячейку в сводной таблице
- Нажмите «Параметры сводной таблицы»
- В диалоговом окне "Параметры сводной таблицы" перейдите на вкладку "Макет и формат"
- В параметрах формата установите флажок и «Для пустых ячеек показывать:» и оставьте поле пустым.
- Нажмите кнопку ОК.
Вышеуказанные шаги скроют нули в сводной таблице и вместо этого отобразят пустую ячейку.
Если вы хотите, чтобы сводная таблица отображала что-то вместо 0, вы можете указать это на шаге 4. Например, если вы хотите отобразить «Данные недоступны» вместо 0, введите этот текст в поле поле (как показано ниже) .
Поиск и удаление нулей в Excel
Во всех приведенных выше методах я показал вам способы скрыть значение 0 в ячейке, но значение все еще остается в ячейке.
Если вы хотите удалить нулевые значения из набора данных (оставив ячейки пустыми), выполните следующие действия:
- Выбрать набор данных (или весь лист)
- Перейдите на вкладку «Главная»
- В группе "Правка" нажмите "Найти и выделить"
- В параметрах, которые отображаются в меню, нажмите «Заменить»
- В диалоговом окне «Найти и заменить» внесите следующие изменения:
- В поле «Найти что» введите 0
- В поле "Заменить на" ничего не вводите (оставьте пустым)
- Нажмите кнопку «Параметры» и выберите «Соответствовать всему содержимому ячейки»
- Нажмите «Заменить все»
Вышеуказанные шаги немедленно заменят все ячейки с нулевым значением пустыми.
Если вы не хотите сразу удалять нули и сначала выделять ячейки, сделайте следующее:
- Выбрать набор данных (или весь лист)
- Перейдите на вкладку «Главная»
- В группе "Правка" нажмите "Найти и выделить"
- В параметрах, отображаемых в меню, нажмите «Заменить».
- В диалоговом окне «Найти и заменить» внесите следующие изменения:
- В поле «Найти что» введите 0
- В поле "Заменить на" ничего не вводите (оставьте пустым)
- Нажмите кнопку «Параметры» и выберите «Соответствовать всему содержимому ячейки»
- Нажмите кнопку «Найти все».Это даст вам список всех ячеек, в которых есть 0 .
- Нажмите Control-A (удерживая нажатой клавишу Control, нажмите A). Это выберет все ячейки, в которых есть 0, .
Теперь, когда у вас выделены все ячейки с 0, вы можете удалить их, заменить чем-то другим или изменить цвет и выделить их.
Скрытие нулей против. Удаление нуля
Когда вы обрабатываете ячейки с нулями внутри, вам нужно знать разницу между их скрытием и удалением.
Когда вы скрываете значение нуля в ячейке, оно вам не видно, но все равно остается в ячейке. Это означает, что если у вас есть набор данных и вы используете его для расчетов, это значение 0 также будет использоваться в расчетах.
Напротив, если у вас есть пустая ячейка, использование ее в формулах может означать, что Excel автоматически проигнорирует эти ячейки (в зависимости от используемой вами формулы).
Например, ниже показано, что я получаю, когда использую формулу СРЗНАЧ для получения среднего значения для двух столбцов (результат находится в строке 14).Столбец A содержит ячейки, в которых значение 0 скрыто (но все еще находится в ячейке), а столбец B содержит ячейки, в которых значение 0 удалено.
Вы можете увидеть разницу в результате, несмотря на то, что набор данных для обоих наборов данных выглядит одинаково.
Это связано с тем, что СРЗНАЧ игнорирует пустые ячейки в столбце B, но использует ячейки в столбце A (поскольку значения в столбце A по-прежнему равны 0).
.Excel - как удалить дубликаты?
Одной из наиболее распространенных проблем, с которыми сталкиваются пользователи Excel, являются повторяющиеся, повторяющиеся значения. Если наличие дубликатов на данном рабочем листе нежелательно, это может быть связано либо с чисто человеческой ошибкой, либо с плохим импортом данных из внешнего источника.
Такие дубликаты могут исказить полученные результаты и затруднить работу с такой таблицей.К счастью, Microsoft предусмотрела эту ситуацию, и в Excel у нас есть несколько инструментов, которые могут помочь нам избавиться от ненужных повторений. Они здесь:
Инструмент удаления дубликатов
Первый способ удаления дубликатов — использование специального инструмента, созданного именно для этой цели. Находим их на вкладке Данные — Инструменты данных — Удалить дубликаты . Для этого сначала выбираем столбец, в котором мы хотим искать и удалять дубликаты.
Условное форматирование
Удаление всех повторов одним щелчком мыши — не всегда самое разумное решение.Другой, более трудоемкий, но более безопасный способ обнаружения дубликатов — применение условного форматирования. Таким образом, мы сможем подсвечивать дубликаты в листе, чтобы легче было определить, нужны они или нет.
Для этого выберите область, которую вы хотите найти, перейдите на вкладку Главная - Условное форматирование - Правила выделения ячеек и выберите опцию Дублировать значения .Затем выберите цвет для форматирования и подтвердите. Как видите, все повторы отмечены и можно переходить к их дальнейшему анализу.
Фильтрация
Третье решение — одна из основных функций Excel — фильтрация данных. Как и условное форматирование, этот метод не удаляет дубликаты. В то время как форматирование показало нам дубликаты, фильтрация их скроет, оставив только уникальные записи.
Чтобы использовать фильтрацию, нам нужно убедиться, что у наших столбцов есть заголовки, иначе фильтрация не будет работать.Затем мы помещаем курсор в любое место в области данных. Чтобы включить фильтр, мы должны перейти в категорию Данные и выбрать Дополнительно в группе Сортировка и фильтрация.
В появившемся окне нам нужно выбрать опцию Только уникальные записи . Когда мы нажмем OK , наш лист будет отфильтрован. Это видно по тому, что нумерация строк данных выделена синим цветом, а строки, содержащие повторяющиеся данные, скрыты.
Вот три чрезвычайно простых способа удалить, найти или скрыть дубликаты в нашей таблице данных. Каждый из этих методов будет применяться к разным сценариям, поэтому их можно использовать взаимозаменяемо.
Вам нравится эта статья? Поделись с другими!
.КАК: Преобразование текста в числовую формулу Excel
Когда значения импортируются или копируются на рабочий лист Excel, значения могут заканчиваться текстом, а не числовыми данными. Эта ситуация вызывает проблемы, если данные сортируются или данные используются в расчетах со встроенными функциями Excel.
Информация в этой статье относится к Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel 2019 для Mac, Excel 2016 для Mac и Excel для Mac 2011.
Преобразовать импортированные данные из текста в числовой формат
На изображении выше функция СУММ настроена на суммирование трех значений (23, 45 и 78) в ячейках с D1 по D3.
Вместо того, чтобы возвращать 146 в качестве ответа, функция возвращает ноль, потому что три значения были введены как текст, а не числовые данные.
Рабочий лист Направления
Форматирование Excel по умолчанию для различных типов данных часто указывает на то, что данные импортируются или вводятся неправильно.По умолчанию числовые данные, а также результаты формул и функций выравниваются по правой стороне ячейки, а текстовые значения выравниваются по левой стороне.
Числа 23, 45 и 78 на изображении выше выровнены по левому краю ячеек, поскольку они являются текстовыми значениями. Функция СУММ заставляет ячейку D4 выравниваться по правому краю.
Кроме того, Excel указывает на потенциальные проблемы с содержимым ячейки, отображая маленький зеленый треугольник в верхнем левом углу ячейки.В этом случае зеленый треугольник означает, что значения в ячейках с D1 по D3 отформатированы как текст.
Продолжить чтение ниже
Исправление проблемных данных с помощью специальной вставки
Чтобы преобразовать эти данные обратно в числовой формат, используйте функции Excel ЗНАЧЕНИЕ и Специальная вставка.
Специальная вставка — это расширенная версия команды вставки, предоставляющая множество вариантов того, что будет перенесено между ячейками во время операции копирования/вставки.Эти параметры включают основные математические операции, такие как сложение и умножение.
Продолжить чтение ниже
Умножить значения на 1 с помощью специальной вставки
Параметр «Специальная вставка» умножает все числа на определенную величину и вставляет результат в целевую ячейку. Он также преобразует текстовые значения в числовые данные, когда каждая запись умножается на 1,
.В примере, показанном на изображении ниже, используется эта специальная функция вставки.Вот результаты операции:
- Ответ на функцию СУММ меняется с нуля на 146.
- Эти три значения изменяют выравнивание ячейки слева направо.
- Зеленые треугольники индикатора ошибки удалены из ячеек, содержащих данные.
Подготовьте рабочий лист
Чтобы преобразовать текстовые значения в числовые данные, сначала введите несколько чисел в виде текста. Это делается путем ввода апострофа ( ') перед каждым числом, введенным в ячейку.
- Откройте новую электронную таблицу в Excel.
- Выберите ячейку D1 , чтобы сделать ее активной.
- Введите в ячейку апостроф, за которым следует число 23 ( '23 ).
- нажмите Enter. Как показано на изображении выше, ячейка D1 имеет зеленый треугольник в верхнем левом углу ячейки, а число 23 выровнено по правому краю. Апостроф не виден в ячейке.
- Выберите ячейку D2 .
- Введите в ячейку апостроф, за которым следует цифра 45 ( '45 ).
- нажмите Enter.
- Выберите ячейку D3.
- Введите в ячейку апостроф, за которым следует число 78 ( '78 ).
- нажмите Enter.
- Выберите ячейку E1.
- Введите в ячейку число 1 (без апострофа) и нажмите Введите .
Число 1 выравнивается справа от ячейки, как показано на изображении выше.
Чтобы увидеть апостроф перед числами, введенными в ячейки с D1 по D3, выберите одну из этих ячеек, например D3. В строке формул над рабочим листом отображается ' 78.
Продолжить чтение ниже
Введите функцию СУММ
- Выберите ячейку D4 .
- Введите = СУММ (D1:D3) .
- нажмите Введите .
- Ответ 0 появляется в ячейке D4, потому что значения в ячейках с D1 по D3 были введены как текст.
Помимо записи, другими способами ввода функции СУММ в ячейку рабочего листа являются:
- Горячие клавиши
- Диалоговое окно функции СУММ
- Автосумма
Преобразование текста в числа с помощью специальной вставки
- Выберите ячейку E1 , чтобы сделать ее активной.
- Выберите Главная > Скопируйте . Пунктирная линия появляется вокруг ячейки E1, чтобы указать, что содержимое этой ячейки копируется в буфер обмена.
- Выберите ячейки от D1 до D3.
- Выберите Вставьте стрелку вниз, чтобы открыть раскрывающееся меню.
- Выберите Специальная вставка , чтобы открыть диалоговое окно Специальная вставка.
- В разделе «Операция» выберите Несколько , чтобы активировать эту операцию.
- Выберите OK Закройте диалоговое окно и вернитесь к рабочему листу.
Продолжить чтение ниже
Результаты рабочего листа
Как показано на изображении выше, вот как результаты этой операции изменяют рабочий лист:
- Три значения в ячейках с D1 по D3 выровнены по правому краю в каждой ячейке.
- В правом верхнем углу каждой ячейки исчезли зеленые треугольники.
- Результат функции СУММ в ячейке D4 равен 146, то есть сумме трех чисел в ячейках от D1 до D3.
Удаление дубликатов, или как очистить данные в MS Excel
Когда мы очищаем или импортируем записи в MS Excel, могут появляться дубликаты. Чтобы иметь возможность эффективно анализировать данные, стоит очистить набор данных перед началом анализа. Мы можем это сделать, в том числе с помощью функции «удалить дубликаты». В следующей статье мы познакомим вас с возможностями использования этой опции.
MS Excel: удалить дубликаты в одном столбце
Цель задачи — создать список уникумов в категории школ на основе дублирующих данных, представленных на рисунке 1.Этот список можно использовать, например, для создания двухуровневого списка.
Рис. 1. Пример данных
Для использования опции Удалить дубликаты выберите диапазон данных, из которого вы хотите избавиться от дубликатов, а затем в карточке Данные в группе Инструменты обработки данных выберите опцию Удалить дубликаты (рис. 2).
Рис. 2. Опция Удалить дубликаты на вкладке Данные
При выборе этой опции появится окно, показанное на рис. 3, только с одним доступным столбцом (в котором мы ищем повторяющиеся значения).Когда вы нажмете OK, Excel оставит сложный список со значениями, уникальными для выбора.
Рис. 3. Окно удаления дубликатов с выбранными данными
MS Excel: удаление дубликатов в нескольких столбцах
В этом разделе мы имеем дело с удалением повторяющихся значений для нескольких столбцов. Примерный объем данных, на котором будет основан пример, представлен на рисунке 4.
Рис. 4. Пример данных
После выбора данных (сохраненных во всех 4 столбцах) и выбора опции Удалить дубликаты (Данные -> Инструменты данных -> Удалить дубликаты) появится окно, как показано на рисунке ниже.
Рис. 5. Удаление дубликатов для нескольких столбцов
Если мы хотим создать уникальное значение Название категории и Название тренировки , оставляем эти опции выбранными, остальные снимаем галочки и нажимаем кнопку ОК.Excel отобразит информацию о количестве повторяющихся значений и оставит уникальные значения в соответствии с шаблоном пользователя.
На рис. 6 показаны данные, которые Excel ограничит для первых пяти значений с рис. 4.
Рис. 6. Левые значения
Здесь стоит отметить, что базовое обучение Excel для 690
было удалено
В том случае, если в окне удалить дубликаты , в заголовках будут отмечены: Название категории, Название тренинга и Цена (рис.7) изменится количество отображаемых записей (рис. 8)
Рис. 7. Выбор трех столбцов в опции удаления дубликатов
Рис. 8. Эффект от использования опции удаления дубликатов для трех столбцов
Автор статьи Гжегож Плак, тренер по познанию.
Если вас интересует возможность удаления дубликатов в MS Excel, посетите:
Также рекомендуем ознакомиться со статьями наших специалистов:
Удаление дубликатов или как очистить данные в электронной таблице MS Excel от Cognity под лицензией Creative Commons Attribution-Noncommercial 4.0 Международная лицензия.