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

Функция ЕСЛИ в Excel

Функция имеет следующий синтаксис.

ЕСЛИ(лог_выражение; значение_если_истина; )

лог_выражение – это проверяемое условие. Например, A2<100. Если значение в ячейке A2 действительно меньше 100, то в памяти эксель формируется ответ ИСТИНА и функция возвращает то, что указано в следующем поле. Если это не так, в памяти формируется ответ ЛОЖЬ и возвращается значение из последнего поля.

значение_если_истина – значение или формула, которое возвращается при наступлении указанного в первом параметре события.

значение_если_ложь – это альтернативное значение или формула, которая возвращается при невыполнении условия. Данное поле не обязательно заполнять. В этом случае при наступлении альтернативного события функция вернет значение ЛОЖЬ.

Очень простой пример. Нужно проверить, превышают ли продажи отдельных товаров 30 шт. или нет. Если превышают, то формула должна вернуть «Ок», в противном случае – «Удалить». Ниже показан расчет с результатом.

Продажи первого товара равны 75, т.е. условие о том, что оно больше 30, выполняется. Следовательно, функция возвращает то, что указано в следующем поле – «Ок». Продажи второго товара менее 30, поэтому условие (>30) не выполняется и возвращается альтернативное значение, указанное в третьем поле. В этом вся суть функции ЕСЛИ. Протягивая расчет вниз, получаем результат по каждому товару.

Однако это был демонстрационный пример. Чаще формулу Эксель ЕСЛИ используют для более сложных проверок. Допустим, есть средненедельные продажи товаров и их остатки на текущий момент. Закупщику нужно сделать прогноз остатков через 2 недели. Для этого нужно от текущих запасов отнять удвоенные средненедельные продажи.

Пока все логично, но смущают минусы. Разве бывают отрицательные остатки? Нет, конечно. Запасы не могут быть ниже нуля. Чтобы прогноз был корректным, нужно отрицательные значения заменить нулями. Здесь отлично поможет формула ЕСЛИ. Она будет проверять полученное по прогнозу значение и если оно окажется меньше нуля, то принудительно выдаст ответ 0, в противном случае — результат расчета, т.е. некоторое положительное число. В общем, та же логика, только вместо значений используем формулу в качестве условия.

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

Как удалить разбросанные ячейки

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

1. Отмечаем первый элемент привычным методом. Далее, зажав клавишу “Ctrl” по очереди выделяем все оставшиеся нужные ячейки либо диапазоны (вне зависимости от того, на каком расстоянии они находятся друг от друга).
2. После того, как мы успешно справились с первым шагом, приступаем к самому удалению, которое можно выполнить ранее рассмотренными способами.

Как сделать данные в Excel невидимыми

В некоторых случаях необходимо сделать данные, в каких либо ячейках невидимыми, не удаляя их. Например, промежуточные результаты вычислений, нулевые значения, значение параметра ИСТИНА или ЛОЖЬ и т.д.

Для этого можно воспользоваться, как минимум, двумя способами:

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

Во-вторых, можно изменить тип данных, сделав их невидимыми.

Для этого выделяем необходимый для скрытия диапазон ячеек с данными, кликаем правой кнопкой мыши и в появившемся контекстном меню выбираем Формат ячеек → вкладка Число → в поле Числовые форматы: выбираем (все форматы). Справа в поле Тип: устанавливаем три знака точки с запятой «;;;» (без кавычек) и нажимаем ОК.

В приведенном ниже примере показаны два типа данных: «Основной» — видимый, и «;;;» — невидимый, а также сумма чисел, одинаковая в обоих диапазонах.

Для увеличения нажмите на рисунке

Чтобы сделать данные в ячейках видимыми, нужно проделать аналогичную операцию. Снова выделить необходимые ячейки и установить Тип: Основной, или просто  удалить «;;;» из этого поля.

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

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

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

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

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

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

Для увеличения нажмите на рисунке

•  Как видим, второй вариант воспринимается гораздо легче.
•  Для скрытия нулевых значений мы, также, можем воспользоваться минимум двумя способами.
• Во-первых, мы можем использовать формулу на основе функции ЕСЛИ.
• Так, например, если у нас, изначально, величина выручки в ячейке В40 получается, как произведение количества проданного товара из ячейки В5 на цену товара из ячейки В25 (В40=В5*В25), то формула для скрытия нулевого значения выручки в ячейке В40 будет выглядеть следующим образом:
• =ЕСЛИ(В5=0;””;В5*В25)
• В том случае, если в этом периоде не было продаж и значение В5=0, возможное нулевое значение выручки скрывается при помощи двух кавычек.
• Скопировав формулу во все ячейки расчётного диапазона по всем периодам и товарам, мы скроем возможные нулевые значения.
• Во-вторых, мы можем воспользоваться возможностями условного форматирования, и окрасить нулевые значения в цвет фона, в данном случае – белый.
• В Excel2010 это можно сделать следующим образом:
• Выделяем необходимый диапазон ячеек, в которых мы будем скрывать нулевые значения.
• На вкладке Главная →в группе Стили → выбираем команду Условное форматирование → в раскрывающемся списке выбираем Правила выделения ячеек → Равно…

Для увеличения нажмите на рисунке

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

В поле Форматировать ячейки, которые РАВНЫ: устанавливаем значение 0 (1).

В Раскрывающемся списке (2) выбираем Пользовательский формат… и в открывшемся диалоговом окне Формат ячеек на вкладке Шрифт в раскрывающемся списке Цвет: (3) меняем Цвет темы «Авто» на «Белый, Фон 1» (4). Нажимаем ОК (5) и ОК (6).

Для увеличения нажмите на рисунке

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

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

Заходим в меню Файл → элемент Параметры → пункт Допомлнительно → раздел показать параметры для следующего листа. Здесь выбираем параметр Показывать нули в ячейках, которые содержат нулевые значения, и убираем галочку. Теперь нулевые значения не будут отображаться на листе для которого установлен этот параметр.

Для увеличения нажмите на рисунке

Удаление формул из ячеек таблицы Excel

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

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

2 способа удалить пробелы между словами или числами в ячейках Excel

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

​Перевел: Антон Андронов​ миллиарды) разделены пробелами.​ лишние пробелы при​ содержится имя​

• ​ способа удалить лишние​ Поищите по форуму,​ в конце строки​ которая во вспомогательной​
  • ​: Функция удаляет не​
  • ​ в котором прописаны​Использование функции СЖПРОБЕЛЫ вместе​ могли заполняться дилетантами.​
• ​ функцию​ Вы копируете текст​

Удаляем все лишние пробелы между словами, отсекаем начальные и конечные пробелы

Используем функцию «СЖПРОБЕЛЫ» для удаления лишних пробелов

​TRIM​​, записанное корректно, т.е.​​ или все пробелы​ массива.​ПОИСК(«ссейн»;I2)+4 = 3+4​ текстовое значение из​ в тексте остаются.​ нажимаем CTRL+H. В​ расширяет возможности пользователя.​

1. ​ и встречаются опечатки​(ПОДСТАВИТЬ) или макрос.​ может содержать символы​​ сделать в ней​​ и заменить». Вставляем​

2. ​ пробелом.​лишние пробелы в Excel​ как текст и​(СЖПРОБЕЛЫ). К сожалению,​

3. ​fjeka​ = 7​ сводной​В конце один​ появившемся окне напротив​ Логично, что она​ и ненужные пробелы.​
4. ​ Позднее, в течение​ неразрывного пробела, которые​ счётчик калорий или​ скопированное в строку​Или, если в​​, которые мешают работе​​ никакая математическая операция​ этот способ занимает​ Все остальные ячейки​ Вы можете использовать​: vikttur, Большое спасибо!​=ЛЕВСИМВ(I2;7) что Вам​​LVL​​ лишний пробел или​ НАЙТИ проставляем пробел,​ будет использоваться вместе​​​​ нашего марафона​
5. ​ функция​

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

​ покажет?​​: Обрабатывайте UDF в​ несколько? Во всех​ а напротив ЗАМЕНИТЬ​ с теми функциями,​На небольшом примере рассмотрим,​30 функций Excel за​TRIM​ К сожалению, функция​В строке «Заменить» ничего​

Применяем инструмент «Найти и заменить», чтобы удалить лишние пробелы между словами

​ пробел. Например, при​ списки, мешают посчитать​ может.​ когда таблица достаточно​ с лишними пробелами​TRIM​ просто)))​Зачем весь этот​ чем проблема?​

1. ​ ячейках одинаково?​ НА не пишем​ которые исследуют массивы​ как пользоваться функцией​ 30 дней​
2. ​(СЖПРОБЕЛЫ) удалить не​​TRIM​​ не пишем, оставляем​ копировании данных из​​ формуле, другое. Например,​​Простейший способ избавиться от​
3. ​ велика.​ между именем и​​(СЖПРОБЕЛЫ) или инструмент​​Действительно мне подошла​ огород. если нужно​​GIG_ant​​Покажите пример в​
4. ​ ничего.​​ и возвращают данные.​​ СЖПРОБЕЛЫ. Имеем таблицу,​, Вы узнаете еще​​ сможет.​​(СЖПРОБЕЛЫ) не поможет​ пустую. Нажимаем кнопку​
5. ​ Word, перешел и​ нам нужно сравнить​ лишних пробелов –​​Замечание:​ фамилией, а также​​Find & Replace​ функция СЖПРОБЕЛЫ​ удалить пробел в​

Удаляем все пробелы между числами

​: Формула массива:​ файле.​Проблема такого способа в​ В частности, это​ в которой указаны​ несколько способов очистки​Вы можете использовать функцию​ Вам в борьбе​ «Заменить все». Нажимаем​ неразрывный пробел. В​ два списка, но​

​ это воспользоваться стандартным​Если после применения​ в начале и​(Найти и заменить),​​Спасибо!​​ конце строки?​

• ​=ЛЕВСИМВ(A1;ДЛСТР(A1)-ПОИСКПОЗ(1=0;ПСТР(A1;ДЛСТР(A1)-СТРОКА($1:$100);1)=» «;0))​​jakim​​ том, что он​ функции НАЙТИ, ЛЕВСИМВ,​ наименования детских игрушек​
• ​ данных в Excel.​​TRIM​​ с лишними килограммами,​ кнопку «Закрыть». Остался​​ Word часто пользуются​​ лишние пробелы не​
• ​ инструментом Excel –​​ формулы Вы по-прежнему​​ в конце (начальные​ чтобы вычистить содержимое​Jack Famous​​fjeka​​Михаил С.​: Я предлагаю так.​
• ​ убирает все, в​​ ПРАВСИМВ и др.​​ и их количество.​​Урок подготовлен для Вас​​(СЖПРОБЕЛЫ), чтобы удалить​ зато может удалить​

Удаляем все пробелы при помощи формулы

​ один пробел. Получилось​ неразрывными пробелами.​ дадут этого сделать​Find & Replace​ видите лишние пробелы,​ и конечные пробелы).​ ячеек в Excel.​

​: =ЛЕВСИМВ(A1;ПОИСК(ПРАВСИМВ(СЖПРОБЕЛЫ(A1);5);A1)+5)удаляет все пробелы​​Юрий М​​ том числе между​ На практике оказывается,​ Не очень грамотный​ командой сайта office-guru.ru​ все пробелы в​ лишние пробелы из​

​ так.​Убрать такие пробелы​ правильно. Поэтому лишние​(Найти и заменить).​ скорее всего в​ Во втором столбце,​

​Когда Вы вставляете данные​ первом же ответе​​А каким способом​​ в конце строки.​

​: jakim, кавычки в​

office-guru.ru>

Формула ЕСЛИ в Excel – примеры нескольких условий

Довольно часто количество возможных условий не 2 (проверяемое и альтернативное), а 3, 4 и более. В этом случае также можно использовать функцию ЕСЛИ, но теперь ее придется вкладывать друг в друга, указывая все условия по очереди. Рассмотрим следующий пример.

Нескольким менеджерам по продажам нужно начислить премию в зависимости от выполнения плана продаж. Система мотивации следующая. Если план выполнен менее, чем на 90%, то премия не полагается, если от 90% до 95% — премия 10%, от 95% до 100% — премия 20% и если план перевыполнен, то 30%. Как видно здесь 4 варианта. Чтобы их указать в одной формуле потребуется следующая логическая структура. Если выполняется первое условие, то наступает первый вариант, в противном случае, если выполняется второе условие, то наступает второй вариант, в противном случае если… и т.д. Количество условий может быть довольно большим. В конце формулы указывается последний альтернативный вариант, для которого не выполняется ни одно из перечисленных ранее условий (как третье поле в обычной формуле ЕСЛИ). В итоге формула имеет следующий вид.

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

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

они входят в интервал B2

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

В конце нужно обязательно закрыть все скобки, иначе эксель выдаст ошибку

Как быстро заменить точки на запятые, убрать апострофы. (Формулы/Formulas)

​ Установить формат ячеек​​не показывать нули в​ их отрицательными в​ я б добавил.​ разбиратся надо (может​ ячейке цифры, они​ не лезть со​ символов), то преобразование​сентябрь​.Replace What:=».», Replacement:=»,»​ Интересно, об этом​ секретная информация).​ Excel,программа перестала считать​ (меню​ спецсимволы в тексте,​ используем инструмент Заменить​, выложил решение здесь​ Дата.​

​ Excel,​​ числовом формате, чтобы​После пункта два​ кто-нибудь анимацию скинет).​ не суммируются​ своими советами на​ в число не​: подскажите пожалуйста как​.Replace What:=»‘», Replacement:=»»​​ в справке где-нибудь​Скажите, пожалуйста, что​ формулы, и преобразовывать​Insert — Module​ скопировать их (​ с вкладки Г​Pelena​Serge_007​заменить ноль на​ считались.​

​ чтобы они суммировались?​​ простить за неловкое​ Вы не собираетесь​

excelworld.ru>

Перемещение и копирование формулы

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

• ​ ячейку области вставки.​​ диапазон ячеек, поместите​Буфер обмена​Когда указатель примет вид​A1 (относительный столбец и​.​Мы стараемся как​ в формуле, то​

• ​ без ошибок.​​ быть равен по​#​ D2:D8 с формулами​

Перемещение формулы

1. ​ можно использовать в​Выделение ячеек и диапазонов​

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

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

3. ​ куда-нибудь в другое​ формуле, скопировав значение​

   • ​На вкладке​ которые нужно скопировать.​​ изменить ячейку. С​​При копировании значения последовательно​​Вставка скопированных ячеек​​ выделенного фрагмента, удерживая​​Вставить​​, перетащите ячейку​​C3​

   • ​ которую нужно скопировать.​​ вас актуальными справочными​​ когда я нажимаю​​vikttur​​ & vbCrLf &​​=​​ место на лист,​​ результат формулы в​​Главная​​Выделение ячеек и диапазонов​​ помощью клавиш со​​ вставляются в строки​

Копирование формулы

1. ​выберите направление, в​ нажатой клавишу CTRL.​

2. ​.​​ или диапазон ячеек​​Примечание:​​В группе​​ материалами на вашем​​ на строку вводу​​: Это простой вариант.​

3. ​ _ «диапазону копируемых​обратно с помощью​

   • ​ то Microsoft Excel​ другую ячейку на​в группе​​На вкладке​​ стрелками поместите курсор​​ и столбцы. Если​​ котором нужно сдвинуть​​ Когда указатель примет​​Сочетание клавиш​

   • ​ в другое место.​ Вы также можете скопировать​​буфер обмена​​ языке. Эта страница​​ и щелкаю на​​ Сложнее — писать​​ ячеек.», «Точное копирование​​ того же окна,​​ автоматически скорректирует ссылки​​ листе. В случае​​Буфер обмена​​Главная​

     ​ в нужное место,​​ область вставки содержит​ окружающие ячейки.​​ форму указателя копирования​​     Можно также нажать сочетание​​Перемещение ячеек с помощью​​ формулы в смежные​​на вкладке​​ переведена автоматически, поэтому​​ энтер. Точно также,​​ код в VBA​​ формул», _ Default:=Selection.Address,​​ возвращая функциональность формулам.​

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

5. 1. ​Примечание​

   2. ​, перетащите ячейку​

      ​ клавиш CTRL+V.​ команд «Вырезать» и​

   3. ​ ячейки с помощью​Главная​

      ​ ее текст может​ если я протягиваю​ для удаления лишних​ Type:=8) If pasteRange.Cells.Count​Этот способ существенно быстрее​ сдвинув их на​ фрагмент такие действия​

​ новое место и​​ приведет к возникновению​Чтобы переместить ячейки, нажмите​нажмите кнопку​ клавишу SHIFT.​

См. также

​ MsgBox «Диапазоны копирования​Нажмите сочетание клавиш​ перестав считать:​

​ ошибки ссылку Недопустимая​ кнопку​

support.office.com>

Удалить текст до первой цифры

Пожалуй, вы уже догадались, но я все же объясню, как работают формулы выше:

1. ищет позицию первого вхождения текстового паттерна (он всегда в кавычках). Соответствующий фрагмент выделен красным. принимает эту позицию как аргумент;
2. она же заменяет фрагмент от первого символа до этой позиции на пустоту (две кавычки подряд), таким образом, удаляя его.

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

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

Что за числа 48 и 57, можно прочитать подробнее тут:

Обратите внимание на фигурные скобки! Их не нужно вводить, они появятся сами при вводе формулы сочетанием клавиш Ctrl+Shift+Enter. Формула выдаст ошибку, если цифр в строке нет

Формула выдаст ошибку, если цифр в строке нет.

Проблемы с вычислением формул в Microsoft Excel

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

Устранение проблем с вычислением

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

Способ 1: изменение формата ячеек

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

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

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

Способ 2: отключение режима «Показать формулы»

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

1. Чтобы включить отображение итогов, переходим во вкладку «Формулы». На ленте в блоке инструментов «Зависимости формул», если кнопка «Показать формулы» активна, то кликаем по ней.

Способ 3: исправление ошибки в синтаксисе

Формула также может отображаться как текст, если в её синтаксисе были допущены ошибки, например, пропущена или изменена буква. Если вы вводили её вручную, а не через Мастер функций, то такое вполне вероятно. Очень распространенной ошибкой, связанной с отображением выражения, как текста, является наличие пробела перед знаком «=».

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

Способ 4: включение пересчета формулы

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

1. Перейдите во вкладку «Файл». Находясь в ней, следует кликнуть по пункту «Параметры».

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

Способ 5: ошибка в формуле

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

• #ЧИСЛО!;
• #ЗНАЧ!;
• #ПУСТО!;
• #ДЕЛ/0!;
• #Н/Д.

В этом случае нужно проверить, правильно ли записаны данные в ячейках, на которые ссылается выражение, нет ли в них ошибок в синтаксисе или не заложено ли в самой формуле какое-либо некорректное действие (например, деление на 0).

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

1. Выделяем ячейку с ошибкой. Переходим во вкладку «Формулы». На ленте в блоке инструментов «Зависимости формул» кликаем по кнопке «Вычислить формулу».

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

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

Стандартные средства для удаления значений ячеек

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

Для того чтобы очистить место в таблице для внесения новых значений можно выделять поочередно нужные диапазоны ячеек и нажимать кнопку «Delete» на клавиатуре, как говорится, медленно, но верно. Проделав такую операцию полтора десятка раз невольно появляется вопрос «а можно ли как-нибудь быстрее?». Конечно можно! Даже нужно. В Microsoft Excel 2007 и выше для этих целей подойдет такой инструмент как «Выделение группы ячеек». Для этого выбираем на ленте Excel вкладку «Главная», переходим в группу «Редактирование», нажимаем кнопку «Найти и выделить» и выбираем команду «Выделение группы ячеек». Появляется одноименное диалоговое окно, при помощи которого можно выделить определенные ячейки таблицы и после этого удалить их значения.

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

Замена формулы на ее результат

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

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

​ спрятать может служить​​ функции ЕСЛИ теперь​ со второго аргумента​ в Excel. Просто​ Одним из способов​ автоматически определит количество​ отображает ошибочное значение​ Казалось бы, 1000​При ошибочных вычислениях, формулы​ нельзя будет восстановить.​ ячейки C2, чтобы​

​ итерации или точности​ помощью которых можно​ в правом верхнем​ ошибка может нарушить​ по кнопке​ этом потеряется также​ ситуация, когда вы​ следует читать так:​ функции ЕСЛИ. В​

​ следует пропустить операцию​ для объяснения данного​

​ суммирующих ячеек.​ – #ПУСТО! Оператором​

Замена формул на вычисленные значения

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

1. ​ «Если значения в​ данном примере второй​

   ​ деления если в​ правила является анализ​Так же ошибка #ССЫЛКА!​

   ​ пересечения множеств является​ при возвращении его​

   1. ​ ошибок вместо значений.​В строке формул​

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

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

   4. ​ часто возникает при​​ одиночный пробел. Им​​ факториала получается слишком​

2. ​ Рассмотрим их на​​выделите часть формулы,​

   ​ скопировать из ячейки​

3. ​ значения​​ так и непосредственно​

   ​ котором расположена интересующая​

4. ​ нужно удалить формулы.​Выделяем первоначальный диапазон. Щелкаем​​ придется вручную.​

   ​ как проводятся в​​ C5 равно ноль,​​ 0 в качестве​

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

1. ​ одно число разделено​ листа в адресе​

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

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

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

​При замене формул на​

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

​ Excel, который автоматически​ формулы, которые нужно​, размещенной на ленте​ контекстном списке в​ вы не хотите​ можно удалить формулу​ Ну а дальше​ просто будет заполнена​ функции =ЕСЛИ():​В реальности операция деление​#ЗНАЧ! – ошибка в​В данном случаи пересечением​В ячейке А3 –​ результаты вычислений.​ формулы не забудьте​ действительное значение, можно​ вычисленные значения Microsoft​ убирал бы выражение​ удалить. Во вкладке​

​ или набираем на​ группе​ тратить время на​ в ячейках, оставив​

1. ​ как прочитать остальную​

2. ​ числом 0 если​

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

3. ​«Параметры вставки»​ ручную обработку таблицы,​ только результат вычислений.​

4. ​ часть формулы описано​ в ячейке напротив​ позволяет нам «делить»​

   ​ тоже что и​ пытаемся сложить число​ C3 и функция​

​ может быть с​

support.office.com>

Как удалить в ячейке формулу, оставив значения?

246348 просмотров

Иногда формула в ячейке нужна лишь на короткое время — только для получения результата. А затем уже не нужна ни сама формула, ни ячейки, на которые она ссылается. Но вот беда — если удалить ячейки, то формула «ломается», как это говорится. Т.е. в ячейке получается либо #ЗНАЧ! либо #ССЫЛКА!. Или Вам надо просто зафиксировать значение, полученное в результате работы формулы. Т.е. чтобы формулы там не было, а было только значение. Как ни странно, но делается это проще простого:

Выделяете ячейку/несколько ячеек с формулами — Копируете — Правая кнопка мыши — Специальная вставка — Значения

Вот и все. Больше формул нет. Так же подобное можно сделать при помощи кода VBA:

Sub Formulas_To_Values()
    Selection.Value = Selection.Value
End Sub

Sub Formulas_To_Values()
Selection.Value = Selection.Value
End Sub

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

Sub All_Formulas_To_Values()
    ActiveSheet.UsedRange.Value = ActiveSheet.UsedRange.Value
End Sub

Sub All_Formulas_To_Values()
ActiveSheet.UsedRange.Value = ActiveSheet.UsedRange.Value
End Sub

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

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

Sub All_Formulas_To_Values_In_All_Sheets()
    Dim wsSh As Worksheet
    For Each wsSh In Sheets
        wsSh.UsedRange.Value = wsSh.UsedRange.Value
    Next wsSh
End Sub

Sub All_Formulas_To_Values_In_All_Sheets()
Dim wsSh As Worksheet
For Each wsSh In Sheets
wsSh.UsedRange.Value = wsSh.UsedRange.Value
Next wsSh
End Sub

А этот код поможет заменить формулы на значения только в видимых ячейках(просматриваются выделенные ячейки):

Sub All_Formulas_To_Values_OnlyVisible()
    Dim rRng As Range, rArea As Range
    If Selection.Count = 1 Then
        Set rRng = ActiveCell
    Else
        Set rRng = Selection.SpecialCells(12)
    End If
    For Each rArea In rRng.Areas
        rArea.Value = rArea.Value
    Next rArea
End Sub

Sub All_Formulas_To_Values_OnlyVisible()
Dim rRng As Range, rArea As Range
If Selection.Count = 1 Then
Set rRng = ActiveCell
Else
Set rRng = Selection.SpecialCells(12)
End If
For Each rArea In rRng.Areas
rArea.Value = rArea.Value
Next rArea
End Sub

КАК БЫСТРО ВЫЗЫВАТЬ:Вообще данную команду можно вывести на Быструю панель и использовать в одно нажатие тогда, когда нужно.

• 2010 Excel Файл- Параметры- Настройка. Выбрать команды из: Все команды. Находите команду Вставить значения и добавляете;
• 2007 Excel Меню- Параметры Excel- Настройка. Выбрать команды из: Все команды. Находите команду Вставить значения и добавляете;
• 2003 Excel: Сервис- Настройка- вкладка Команды- категория Правка- Вставить значения.

Теперь у Вас появится значок на Быстрой панели.

ДОБАВИТЬ В КОНТЕКСТНОЕ МЕНЮ ЯЧЕЕК:Если один раз выполнить код:

Sub Add_PasteSpecials()
    Dim cbb
    Set cbb = Application.CommandBars("Cell").FindControl(ID:=370)
    If Not cbb Is Nothing Then cbb.Delete 'удаляем пункт, если он был уже добавлен ранее
    Application.CommandBars("Cell").Controls.Add ID:=370, before:=4
End Sub

Sub Add_PasteSpecials()
Dim cbb
Set cbb = Application.CommandBars(«Cell»).FindControl(ID:=370)
If Not cbb Is Nothing Then cbb.Delete ‘удаляем пункт, если он был уже добавлен ранее
Application.CommandBars(«Cell»).Controls.Add ID:=370, before:=4
End Sub

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

Также см.:]

С