Elettracompany.com

Компьютерный справочник
2 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Ссылка на пустую ячейку в excel

Отображение и скрытие нулевых значений

В этом курсе:

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

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

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

Скрытие и отображение всех нулевых значений на листе

Выберите Файл > Параметры > Дополнительно.

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

Чтобы отображать в ячейках нулевые значения (0), установите флажок Показывать нули в ячейках, которые содержат нулевые значения.

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

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

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

Выделите ячейки, содержащие нулевые значения (0), которые требуется скрыть.

Вы можете нажать клавиши CTRL+1 или на вкладке Главная щелкнуть Формат > Формат ячеек.

Щелкните Число > Все форматы.

В поле Тип введите выражение 0;-0;;@ и нажмите кнопку ОК.

Отображение скрытых значений.

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

Вы можете нажать клавиши CTRL+1 или на вкладке Главная щелкнуть Формат > Формат ячеек.

Для применения числового формата, определенного по умолчанию, выберите Число > Общий и нажмите кнопку ОК.

Скрытие нулевых значений, возвращенных формулой

Выделите ячейку, содержащую нулевое (0) значение.

На вкладке Главная щелкните стрелку рядом с кнопкой Условное форматирование и выберите «Правила выделения ячеек» > «Равно».

В левом поле введите .

В правом поле выберите Пользовательский формат.

В поле Формат ячейки откройте вкладку Шрифт.

В списке Цвет выберите белый цвет и нажмите кнопку ОК.

Отображение нулей в виде пробелов или тире

Для решения этой задачи воспользуйтесь функцией ЕСЛИ.

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

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

Скрытие нулевых значений в отчете сводной таблицы

Выберите отчет сводной таблицы.

На вкладке Анализ в группе Сводная таблица щелкните стрелку рядом с командой Параметры и выберите пункт Параметры.

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

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

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

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

Отображение и скрытие всех нулевых значений на листе

Выберите Файл > Параметры > Дополнительно.

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

Чтобы отображать в ячейках нулевые значения (0), установите флажок Показывать нули в ячейках, которые содержат нулевые значения.

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

Скрытие нулевых значений в выделенных ячейках с помощью числового формата

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

Выделите ячейки, содержащие нулевые значения (0), которые требуется скрыть.

Вы можете нажать клавиши CTRL+1 или на вкладке Главная щелкнуть Формат > Формат ячеек.

В списке Категория выберите элемент Пользовательский.

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

Чтобы снова отобразить скрытые значения, выделите ячейки, а затем нажмите клавиши CTRL+1 или на вкладке Главная в группе Ячейки наведите указатель мыши на элемент Формат и выберите Формат ячеек. Чтобы применить числовой формат по умолчанию, в списке Категория выберите Общий. Чтобы снова отобразить дату и время, выберите подходящий формат даты и времени на вкладке Число.

Скрытие нулевых значений, возвращенных формулой, с помощью условного форматирования

Выделите ячейку, содержащую нулевое (0) значение.

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

В левом поле введите .

В правом поле выберите Пользовательский формат.

В диалоговом окне Формат ячеек откройте вкладку Шрифт.

В поле Цвет выберите белый цвет.

Использование формулы для отображения нулей в виде пробелов или тире

Для выполнения этой задачи используйте функцию ЕСЛИ.

Чтобы этот пример проще было понять, скопируйте его на пустой лист.

Второе число вычитается из первого (0).

Возвращает пустую ячейку, если значение равно нулю

Возвращает дефис (-), если значение равно нулю

Дополнительные сведения об использовании этой функции см. в статье Функция ЕСЛИ.

Скрытие нулевых значений в отчете сводной таблицы

Щелкните отчет сводной таблицы.

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

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

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

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

Читать еще:  Создание базы данных в excel

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

Отображение и скрытие всех нулевых значений на листе

Нажмите кнопку Microsoft Office , щелкните Параметры Excelи выберите категорию Дополнительно .

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

Чтобы отображать в ячейках нулевые значения (0), установите флажок Показывать нули в ячейках, которые содержат нулевые значения.

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

Скрытие нулевых значений в выделенных ячейках с помощью числового формата

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

Выделите ячейки, содержащие нулевые значения (0), которые требуется скрыть.

Вы можете нажать клавиши CTRL+1 или на вкладке Главная в группе Ячейки щелкнуть Формат > Формат ячеек.

В списке Категория выберите элемент Пользовательский.

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

Чтобы снова отобразить скрытые значения, выделите ячейки, а затем на вкладке Главная в группе Ячейки наведите указатель мыши на элемент Формат и выберите Формат ячеек. Чтобы применить числовой формат по умолчанию, в списке Категория выберите Общий. Чтобы снова отобразить дату и время, выберите подходящий формат даты и времени на вкладке Число.

Скрытие нулевых значений, возвращенных формулой, с помощью условного форматирования

Выделите ячейку, содержащую нулевое (0) значение.

На вкладке Главная в группе Стили щелкните стрелку рядом с кнопкой Условное форматирование и выберите «Правила выделения ячеек» > «Равно».

В левом поле введите .

В правом поле выберите Пользовательский формат.

В диалоговом окне Формат ячеек откройте вкладку Шрифт.

В поле Цвет выберите белый цвет.

Использование формулы для отображения нулей в виде пробелов или тире

Для выполнения этой задачи используйте функцию ЕСЛИ.

Чтобы этот пример проще было понять, скопируйте его на пустой лист.

Выделите пример, приведенный в этой статье.

Важно: Не выделяйте заголовки строк или столбцов.

Выделение примера в справке

Нажмите клавиши CTRL+C.

В Excel создайте пустую книгу или лист.

Выделите на листе ячейку A1 и нажмите клавиши CTRL+V.

Важно: Чтобы пример правильно работал, его нужно вставить в ячейку A1.

Чтобы переключиться между просмотром результатов и просмотром формул, возвращающих эти результаты, нажмите клавиши CTRL+` (знак ударения) или на вкладке Формулы в группе «Зависимости формул» нажмите кнопку Показать формулы.

Скопировав пример на пустой лист, вы можете настроить его так, как вам нужно.

Отображать пустое значение при ссылке на пустую ячейку в Excel 2010

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

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

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

17 ответов

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

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

1) Предоставление other.cell.reference представляет собой ссылочную формулу, которую вы в настоящее время используете после = (например, Sheet17!$H$42 ), замените ссылку на ссылку с помощью

=IF( other.cell.reference <>«», other.cell.reference , «»)

2) Установите формат «Число» ваших связанных ячеек в «Пользовательский»: General;–General; .

3) В разделе «Параметры Excel», «Дополнительно» на странице «Параметры отображения для этой рабочей таблицы» снимите флажок «Показывать нуль в ячейках с нулевым значением». Предупреждение: это приведет к исчезновению всех нулей на листе.

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

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

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

A1 в этом случае может быть заменен любой ячейкой, включая другой лист, книгу или INDIRECT ().

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

Числовой метод . Строгое значение источника, затем умножьте на 1. Литеральная строка emtpy * 1 = #VALUE, String с числовым автоматически преобразуется в числовое значение и не возникает ошибка.

Я тоже не нашел лучшего решения, кроме Скотта.

Но в сочетании с подходом отсюда он может быть почти терпимым, я думаю:

Скажем, у меня есть такая формула

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

Я иду в Insert> Имя> Определите и в «Именах в книге» я создаю новое имя «RC_P_B». Затем в поле «Относится к» я копирую свою формулу (без <> символов — это формула массива).

Затем вы можете использовать формулу Скотта без необходимости повторять весь текст формулы:

Я считаю, что это лучше, чем копировать всю формулу.

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

Существует еще один трюк: установите пустую ячейку soucre в формулу =»» . Подробнее см. .

Если связанная ячейка не является числовой, вы можете использовать оператор IF с ISTEXT:

Я долго искал элегантное решение этой проблемы.

Я использовал формулу = if (a2 = «», «», a2) в течение многих лет, но я считаю, что это немного громоздко.

Я попробовал выше предложение = a2 & «», и хотя он, похоже, работает, он показывает число, которое на самом деле является текстом, поэтому форматирование номера не может применяться и статистические операции не работают, такие как сумма, средняя, ​​средняя и т. поэтому, если это работоспособные номера, которые вы ищете, это не соответствует счету.

Читать еще:  Fastreport excel export

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

= CELL («contents», A2)

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

Решение для Excel 2010:

  1. Откройте «Файл»
  2. Нажмите «Опции»
  3. Нажмите «Дополнительно»
  4. В разделе «Параметры отображения» для этого рабочего листа «BBBB»
  5. Разогнать поле «Показать нулевое значение для ячеек с нулевым значением».

Пустая ячейка определяется Excel-ем как не пустая. Что за глюк?

Эта статья будет близка тем, кому приходится часто работать с отчетами, выгруженными в Excel из программ вроде 1С, SAP и им подобных. Хотя, справедливости ради, надо отметить, что и в обычных файлах Excel такая проблема может так же встречаться, особенно, если в файле использовались различные формулы.
Вводные данные: есть отчет который выкачивается из сторонней программы. В нем есть ячейки, с виду пустые: в них нет пробелов, нет переносов на строки, никаких символов, нет объектов, условного форматирования, в настройках не стоит скрывать нули(ФайлПараметрыДополнительноПоказывать нули в ячейках, которые содержат нулевые значения). Даже если перейти в режим редактирования ячейки — там пусто.
Если попробовать найти такие «пустые» ячейки(выделить все ячейки листа — F5 — Выделить — Пустые ячейки ) — они не выделяются. Но фильтр при этом их видит как пустые и фильтрует как пустые.
Любые математические действия(умножение на ячейку, сложение, деление и т.п.) внутри формул с такими ячейками выдают ошибку #ЗНАЧ! (#VALUE!) , а функция ЕПУСТО (ISBLANK) считает ячейку не пустой. Формулы вроде СУММ (SUM) и СЧЁТ (COUNT) игнорируют такие ячейки, а СЧЁТЗ (COUNTA) считает их заполненными.

И самое удивительное — если выделить такую ячейку вручную и нажать Delete (или вкладка Главная -группа РедактированиеОчистить содержимое) — то ячейка становится действительно пустой и с ней начинают работать формулы и другие функции Excel как с реально пустой.
Здесь возникает главный вопрос: что с этой ячейкой не так, если там и до Delete ничего не было?
А не так с ней вот что: практически во всех программах есть такое понятие строковых(текстовых) данных, как строка нулевой длины(еще её часто называют нулевая строка. В Visual Basic for Application такая строка обозначается константой vbNullString , в ячейках её можно встретить как сочетание двух кавычек подряд — «» ). Визуально такой текст себя никак не проявляет, однако это все же текст. А любой текст это уже не пусто, но и не число.
Откуда может появится такой текст в ячейках? Здесь несколько вариантов:

  1. Он есть в ячейках изначально, потому что именно так настроена выгрузка и создание файлов в сторонней программе(вроде 1С). В некоторых случаях такие выгрузки настроены таким образом, что как таковых пустых ячеек нет — они просто заполняются строкой нулевой длины.
  2. в ячейке была записана формула, результатом которой стал текст нулевой длины. Самый простой пример такой формулы:
    =ЕСЛИ( A1 =1;10;»»)
    =IF(A1=1,10,»»)
    в итоге, если в ячейке A1 записано любое значение, отличное от 1 формула вернет строку нулевой длины. И если впоследствии формулу заменять значением(Как удалить в ячейке формулу, оставив значения?), то получим нашу псевдо пустую ячейку.
    Если формулы создаются Вами и в дальнейшем планируются производить с этими ячейками математические действия, то лучше вместо «» ставить 0. Тогда проблем не будет. Нули всегда можно заменить или скрыть: ФайлПараметрыДополнительноПоказывать нули в ячейках, которые содержат нулевые значения

А если такой файл делали не Вы — он достался «по наследству» или это выгрузка из другой программы, что делать тогда? Я предлагаю такой вот нехитрый код, который во всех выделенных ячейках заменит строки нулевой длины на нормальные пустые ячейки:

Sub ReplaceNullString() Dim rR As Range, rF As Range, rC As Range Dim avR, lr As Long, lc As Long Set rR = Intersect(ActiveSheet.UsedRange, Selection) On Error Resume Next Set rR = rR.SpecialCells(xlCellTypeConstants) On Error GoTo 0 If rR Is Nothing Then MsgBox «В выделенных ячейках нет значений!», vbInformation, «www.excel-vba.ru» Exit Sub End If Set rF = rR.Find(vbNullString, , xlFormulas, xlWhole) If Not rF Is Nothing Then avR = rR.Value For lr = 1 To UBound(avR, 1) For lc = 1 To UBound(avR, 2) If avR(lr, lc) = «» Then rR.Item(lr, lc).Value = Empty Next lc Next lr MsgBox «Строки нулевой длины заменены», vbInformation, «www.excel-vba.ru» Exit Sub End If MsgBox «Строк нулевой длины на листе нет или лист защищен», vbInformation, «www.excel-vba.ru» End Sub

Как это работает:
если раньше никогда не работали с макросами, то рекомендую ознакомиться со статьями:
Что такое макрос и где его искать?
Что такое модуль? Какие бывают модули?

  1. создаем в книге новый стандартный модуль: Alt+F11InsertModule()
  2. копируем в созданный модуль приведенный выше код
  3. выделяем нужный диапазон(если надо заменить на всем листе — то можно выделить все ячейки листа или целиком нужные столбцы — программа сама определить нужные данные)
  4. нажимаем Alt+F8 и в появившемся окне выбираем ReplaceNullString

Короткая видеоинструкция:

Статья помогла? Поделись ссылкой с друзьями!

как я могу проверить, является ли ячейка пустой или на самом деле ссылка на пустую ячейку в Excel или Google Sheets?

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

Мне нужно уметь различать пустую ячейку «really» — ничего в ней нет-и ссылку на пустую ячейку на другом листе.

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

1 Ответ

чтобы найти действительно пустой в Excel:

Который будет возвращать true только в том случае, если их нет, нет формулы или констант.

То, что вы можете использовать:

Чтобы проверить, если он возвращает пустое место.

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

A2 является пустым, а A3 имеет формулу =»»

Похожие вопросы:

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

Я искал функцию скрипта Google Sheets Apps, которую можно ввести в ячейку B1, чтобы проверить, содержит ли ячейка A1 простое число или нет. Пока я ничего не могу найти. У меня есть код VBA для.

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

В Codeigniter Как проверить, является ли загруженный файл на самом деле pdf или jpg или png? Потому что, если мы загрузим Ан .exe файл С.pdf расширение, то и он загружается без каких-либо проблем.

Мне нужно вернуть пустую ячейку из Excel formula, но похоже, что Excel обрабатывает пустую строку или ссылку на пустую ячейку иначе, чем истинная пустая ячейка. Так что по сути мне нужно что-то.

Используя jQuery, как проверить, является ли ячейка таблицы пустой или нет? Пожалуйста, помогите мне.

Я не использую числовую формулу. Все, что у меня есть, — это простая ссылка на другую ячейку. =(Sheet1!D8) Если эта ячейка пуста, я хочу, чтобы ячейка на этой странице тоже была пустой, а не 0. Как.

Ну, я пытаюсь прочитать ячейку из excel листа. И если ячейка не имеет значения или пуста, она вернет false. Я пробовал null ( sheet.getrow(a).getcell(b) == null и.

Я работаю в Google Sheets, а не excel. В настоящее время я работаю (и ниже формулы появляются) на листе 1. В Sheet2 у меня есть дополнительные данные. На листе 1 в ячейке G1 находится Sheet2 (без.

Я очень новичок в скрипте Google Apps, и Java он основан. На самом деле я только что нырнул в глубокий конец бассейна, даже не восемь часов назад! Я знаю VBA, SQL, и т. д., Но я отвлекся. Я понял.

Пустая ячейка определяется Excel-ем как не пустая. Что за глюк?

Эта статья будет близка тем, кому приходится часто работать с отчетами, выгруженными в Excel из программ вроде 1С, SAP и им подобных. Хотя, справедливости ради, надо отметить, что и в обычных файлах Excel такая проблема может так же встречаться, особенно, если в файле использовались различные формулы.
Вводные данные: есть отчет который выкачивается из сторонней программы. В нем есть ячейки, с виду пустые: в них нет пробелов, нет переносов на строки, никаких символов, нет объектов, условного форматирования, в настройках не стоит скрывать нули(ФайлПараметрыДополнительноПоказывать нули в ячейках, которые содержат нулевые значения). Даже если перейти в режим редактирования ячейки — там пусто.
Если попробовать найти такие «пустые» ячейки(выделить все ячейки листа — F5 — Выделить — Пустые ячейки ) — они не выделяются. Но фильтр при этом их видит как пустые и фильтрует как пустые.
Любые математические действия(умножение на ячейку, сложение, деление и т.п.) внутри формул с такими ячейками выдают ошибку #ЗНАЧ! (#VALUE!) , а функция ЕПУСТО (ISBLANK) считает ячейку не пустой. Формулы вроде СУММ (SUM) и СЧЁТ (COUNT) игнорируют такие ячейки, а СЧЁТЗ (COUNTA) считает их заполненными.

И самое удивительное — если выделить такую ячейку вручную и нажать Delete (или вкладка Главная -группа РедактированиеОчистить содержимое) — то ячейка становится действительно пустой и с ней начинают работать формулы и другие функции Excel как с реально пустой.
Здесь возникает главный вопрос: что с этой ячейкой не так, если там и до Delete ничего не было?
А не так с ней вот что: практически во всех программах есть такое понятие строковых(текстовых) данных, как строка нулевой длины(еще её часто называют нулевая строка. В Visual Basic for Application такая строка обозначается константой vbNullString , в ячейках её можно встретить как сочетание двух кавычек подряд — «» ). Визуально такой текст себя никак не проявляет, однако это все же текст. А любой текст это уже не пусто, но и не число.
Откуда может появится такой текст в ячейках? Здесь несколько вариантов:

  1. Он есть в ячейках изначально, потому что именно так настроена выгрузка и создание файлов в сторонней программе(вроде 1С). В некоторых случаях такие выгрузки настроены таким образом, что как таковых пустых ячеек нет — они просто заполняются строкой нулевой длины.
  2. в ячейке была записана формула, результатом которой стал текст нулевой длины. Самый простой пример такой формулы:
    =ЕСЛИ( A1 =1;10;»»)
    =IF(A1=1,10,»»)
    в итоге, если в ячейке A1 записано любое значение, отличное от 1 формула вернет строку нулевой длины. И если впоследствии формулу заменять значением(Как удалить в ячейке формулу, оставив значения?), то получим нашу псевдо пустую ячейку.
    Если формулы создаются Вами и в дальнейшем планируются производить с этими ячейками математические действия, то лучше вместо «» ставить 0. Тогда проблем не будет. Нули всегда можно заменить или скрыть: ФайлПараметрыДополнительноПоказывать нули в ячейках, которые содержат нулевые значения

А если такой файл делали не Вы — он достался «по наследству» или это выгрузка из другой программы, что делать тогда? Я предлагаю такой вот нехитрый код, который во всех выделенных ячейках заменит строки нулевой длины на нормальные пустые ячейки:

Sub ReplaceNullString() Dim rR As Range, rF As Range, rC As Range Dim avR, lr As Long, lc As Long Set rR = Intersect(ActiveSheet.UsedRange, Selection) On Error Resume Next Set rR = rR.SpecialCells(xlCellTypeConstants) On Error GoTo 0 If rR Is Nothing Then MsgBox «В выделенных ячейках нет значений!», vbInformation, «www.excel-vba.ru» Exit Sub End If Set rF = rR.Find(vbNullString, , xlFormulas, xlWhole) If Not rF Is Nothing Then avR = rR.Value For lr = 1 To UBound(avR, 1) For lc = 1 To UBound(avR, 2) If avR(lr, lc) = «» Then rR.Item(lr, lc).Value = Empty Next lc Next lr MsgBox «Строки нулевой длины заменены», vbInformation, «www.excel-vba.ru» Exit Sub End If MsgBox «Строк нулевой длины на листе нет или лист защищен», vbInformation, «www.excel-vba.ru» End Sub

Как это работает:
если раньше никогда не работали с макросами, то рекомендую ознакомиться со статьями:
Что такое макрос и где его искать?
Что такое модуль? Какие бывают модули?

  1. создаем в книге новый стандартный модуль: Alt+F11InsertModule()
  2. копируем в созданный модуль приведенный выше код
  3. выделяем нужный диапазон(если надо заменить на всем листе — то можно выделить все ячейки листа или целиком нужные столбцы — программа сама определить нужные данные)
  4. нажимаем Alt+F8 и в появившемся окне выбираем ReplaceNullString

Короткая видеоинструкция:

Статья помогла? Поделись ссылкой с друзьями!

Ссылка на основную публикацию
ВсеИнструменты 220 Вольт
Adblock
detector
×
×