Как в excel сделать пустые ячейки а не 0?

• Чтобы выполнить действие только тогда, когда ячейка не пуста (содержит какие-то значения), вы можете использовать формулу, основанную на функции ЕСЛИ.
• В примере ниже столбец F содержит даты завершения закупок шоколада.
• Поскольку даты для Excel — это числа, то наша задача состоит в том, чтобы проверить в ячейке наличие числа.
• Формула в ячейке F3:
• =ЕСЛИ(СЧЁТЗ(D3:D9)=7,СУММ(C3:C9),»»)

Как работает эта формула?

Функция СЧЕТЗ (английский вариант — COUNTA) подсчитывает количество значений (текстовых, числовых и логических) в диапазоне ячеек Excel.

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

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

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

Давайте рассмотрим и другие варианты. В ячейке F6 записана большая формула —

=ЕСЛИ(ИЛИ(ЕПУСТО(D3),ЕПУСТО(D4),ЕПУСТО(D5),ЕПУСТО(D6),ЕПУСТО(D7),ЕПУСТО(D8),ЕПУСТО(D9)),»»,СУММ(C3:C9))

Функция ИЛИ (английский вариант — OR) позволяет объединить условия и указать, что нам достаточно того, чтобы хотя бы одна функция ЕПУСТО обнаружила пустую ячейку. В этом случае никаких вычислений не производим и функция ЕСЛИ возвращает пустую строку. В противном случае — производим вычисления.

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

Рассмотрим теперь более универсальные решения.

=ЕСЛИ(СЧИТАТЬПУСТОТЫ(D3:D9),»»,СУММ(C3:C9))

В качестве условия в функции ЕСЛИ мы используем СЧИТАТЬПУСТОТЫ (английский вариант — COUNTBLANK). Она возвращает количество пустых ячеек, но любое число больше 0 Excel интерпретирует как ИСТИНА.

И, наконец, еще одна формула Excel, которая позволит производить расчет только при наличии непустых ячеек.

=ЕСЛИ(ЕЧИСЛО(D3:D9),СУММ(C3:C9),»»)

Функция ЕЧИСЛО ( или ISNUMBER) возвращает ИСТИНА, если ссылается на число. Естественно, при ссылке на пустую ячейку возвратит ЛОЖЬ.

А теперь посмотрим, как это работает. Заполним таблицу недостающим значением.

Как видите, все наши формулы рассчитаны и возвратили одинаковые значения.

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

1. Итак, перед нами уже знакомая формула
2. =ЕСЛИ(СЧЁТЗ(D3:D9)=7,СУММ(C3:C9),»»)
3. Для функции СЧЕТЗ не имеет значения, число или текст используются в ячейке Excel.
4. =ЕСЛИ(СЧИТАТЬПУСТОТЫ(D3:D9),»»,СУММ(C3:C9))
5. То же можно сказать и о функции СЧИТАТЬПУСТОТЫ.

А вот третий вариант — к проверке условия при помощи функции ЕЧИСЛО добавляем проверку ЕТЕКСТ (ISTEXT в английском варианте). Объединяем их функцией ИЛИ.

=ЕСЛИ(ИЛИ(ЕТЕКСТ(D3:D9),ЕЧИСЛО(D3:D9)),СУММ(C3:C9),»»)

А теперь вставляем в ячейку D5 недостающее значение и проверяем, все ли работает.

Надеемся, этот материал был полезен. А вот еще несколько примеров работы с условиями и функцией ЕСЛИ в Excel.

Функция ЕСЛИ: примеры с несколькими условиями — Для того, чтобы описать условие в функции ЕСЛИ, Excel позволяет использовать более сложные конструкции. В том числе можно использовать и несколько условий. Рассмотрим на примере. Для объединения нескольких условий в… Проверка ввода данных в Excel — Подтверждаем правильность ввода галочкой. Задача: При ручном вводе данных в ячейки таблицы проверять правильность ввода в соответствии с имеющимся списком допустимых значений. В случае правильного ввода в отдельном столбце ставить… Функция ЕСЛИ: проверяем условия с текстом — Рассмотрим использование функции ЕСЛИ в Excel в том случае, если в ячейке находится текст. СодержаниеПроверяем условие для полного совпадения текста.ЕСЛИ + СОВПАДИспользование функции ЕСЛИ с частичным совпадением текста.ЕСЛИ + ПОИСКЕСЛИ… Визуализация данных при помощи функции ЕСЛИ — Функцию ЕСЛИ можно использовать для вставки в таблицу символов, которые наглядно показывают происходящие с данными изменения. К примеру, мы хотим показать, происходит рост или снижение продаж. В столбце N поставим… Как функция ЕСЛИ работает с датами? — На первый взгляд может показаться, что функцию ЕСЛИ для работы с датами можно использовать так же, как для числовых и текстовых значений, которые мы только что обсудили. К сожалению, это…

Источник: https://mister-office.ru/formuly-excel/calculate-if-not-blank.html

Как заполнить пустые ячейки в Excel?

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

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

Способ 1: Заполнение пустых ячеек в Excel с помощью заданного значения

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

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

 И этот метод настолько прост , что вы можете использовать без каких — либо специальных знаний.

• Следующие шаги иллюстрируют, как заполнить пустые ячейки в Excel с помощью указанного значения.
• Шаг 1: Откройте нужный лист Excel, на котором необходимо заполнить пустые ячейки.
• Шаг 2: Выберите конкретный столбец, содержащий пустые ячейки.

Шаг 3: Теперь найдите кнопку Найти и выделить в меню Главная и выберите Выделить группу ячеек. Появится окно с различными вариантами. Выберите Пустые ячейки.

Шаг 4: После данного выбора все пустые ячейки в указанном диапазоне будут выделены. Теперь, не нажимая ничего, введите необходимо значение, которое нужно поместить во все пустые ячейки и нажмите Ctrl + Enter. Пустые ячейки заполнятся нужным содержимым.

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

Способ 2: Заполнение пустых ячеек в Excel значением, расположенным в ячейке выше

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

Шаг 1: Откройте нужный лист Excel, который содержит пустые ячейки.

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

Шаг 3: Теперь найдите кнопку Найти и выделить в меню Главная и выберите Выделить группу ячеек. Появится окно с различными вариантами. Выберите Пустые ячейки.

Шаг 4: В строке формул наберите знак «=» и укажите ячейку, содержимым которой необходимо заполнить пустые ячейки. Далее нажмите Ctrl + Enter. Вы увидите, что все пустые ячейки заполнились значением ячейки выше. Смотрите скриншоты ниже.

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

Способ 3: Заполнение пустых ячеек Excel со значением выше с помощью макроса VBA

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

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

Шаг 2: Теперь откройте редактор VBA, нажав Alt + F11.

Шаг 3: Щелкните правой кнопкой мыши на любом проекте и перейдите к Insert > Module. После этого вставьте следующий код VBA.

Sub FillBlankCells()

Static rngUR As Range: Set rngUR = ActiveWorkbook.ActiveSheet.UsedRange

Dim rngBlank As Range: Set rngBlank = rngUR.Find(«»)

While Not rngBlank Is Nothing

rngBlank.Value = rngBlank.Offset(-1, 0).Value

1. Set rngBlank = rngUR.Find(«», rngBlank)
2. Wend
3. End Sub

Шаг 4: Теперь сохраните модуль с помощью клавиши Ctrl + S, появившееся диалоговое окно с предупреждением просто проигнорируйте, нажав на кнопку Нет. Теперь вы можете задать сочетание клавиш для выполнения макроса. (меню Разработчик Макросы выберите нужный макрос Параметры Сочетание клавиш (укажите букву).

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

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

Источник: http://pro-spo.ru/internet/5196-kak-zapolnit-pustye-yachejki-v-excel

Excel говорит мне, что мои пустые ячейки не пустые

поэтому в excel я пытаюсь избавиться от пустых ячеек между моими ячейками, в которых есть информация, используя F5 для поиска пустых ячеек, затем Ctrl + — для их удаления и сдвига ячеек вверх. Но когда я пытаюсь это сделать, он говорит мне, что «клеток не найдено».

Итак, мой вопрос в том, как я могу это сделать, но с этими пустыми ячейками, которые Excel считает не пустыми? Я попытался пройти и просто нажать delete над пустыми ячейками, но у меня много данных и понял, что это займет слишком много времени. мне нужно найти способ выбрать эти «пустые» ячейки в пределах выбора данных.

заранее спасибо за вашу помощь! 🙂

откровение: некоторые пустые ячейки на самом деле не пустой! Как я покажу, ячейки могут иметь пробелы, новые строки и true empty:

чтобы найти эти ячейки быстро вы можете сделать несколько вещей.

1. на =CODE(A1) формула вернет значение#! если клетка действительно пуста, в противном случае вернется. Это число -номер ASCII в =CHAR(32).
2. если вы выделите ячейку и щелкните в строке формул и используйте курсор, чтобы выбрать все.

удаление этих:

если у вас есть только пробел в клетках они могут быть легко удалены с помощью:

1. пресс ctrl + h чтобы открыть find и replace.
2. введите одно место в найти, оставить заменить на пустой и убедитесь, что у вас есть матч всей ячейки содержание галочкой в опциях.
3. пресс заменить все.

если у вас newlines это сложнее и требует VBA:

1. щелкните правой кнопкой мыши на вкладке листа > Просмотр кода.

2. затем введите следующий код. Помните Chr(10) новая строка заменяет это только по мере необходимости, например » » & Char(10) это пробел и новая строка:

   Sub find_newlines()
   With Me.Cells
   Set c = .Find(Chr(10), LookIn:=xlValues, LookAt:=xlWhole)
   If Not c Is Nothing Then
   firstAddress = c.Address
   Do
   c.Value = «»
   Set c = .FindNext(c)
   If c Is Nothing Then Exit Do
   Loop While c.Address firstAddress
   End If
   End With
   End Sub

3. Теперь запустите ваш код нажатие Ф5.

после файл поставляется: выберите диапазон интересов для повышения производительности, затем выполните следующие действия:

Sub find_newlines()
With Selection
Set c = .Find(«», LookIn:=xlValues, LookAt:=xlWhole)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Value = «»
Set c = .FindNext(c)
If c Is Nothing Then Exit Do
Loop While c.Address firstAddress
End If
End With
End Sub

простой способ выбрать и очистить эти пустые ячейки, чтобы сделать их пустыми:

1. пресс ctrl + a или предварительно выберите свой диапазон
2. пресс ctrl + f
3. оставить найти пустой и выберите матч всего содержимого ячейки.
4. нажмите найти все
5. пресс ctrl + a выбрать все пустые ячейки нашли
6. закрыть поиск диалоговое окно
7. пресс backspace или удалить

Это сработало для меня:

1. CTR-H для поиска и замены
2. оставьте «найти что» пустой
3. изменить «заменить на» на уникальный текст, то, что вы
   положительный не будет найден в другой ячейке (я использовал 'xx')
4. клик
   «Заменить Все»
5. скопируйте уникальный текст на Шаге 3, чтобы найти то, что'
6. удалить уникальный текст в «заменить на»
7. нажмите «заменить все»

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

мне удалось очистить их

1. выбор столбцов / строк, которые я хотел очистить, а затем делать
2. «найти» [без текста] и» заменить » [слово выбора].
3. затем я сделал «найти» [слово выбора] и» заменить » на [нет текста].

он избавился от всех скрытых / фантомных персонажей в тех ячейки. Может, это сработает?

все, это довольно просто. Я пытался сделать то же самое, и это то, что сработало для меня в VBA

Range(«A1:R50»).Select 'The range you want to remove blanks
With Selection
Selection.NumberFormat = «General»
.Value = .Value
End With

С уважением,
Ананд Ланка

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

'Select non blank cells
Selection.SpecialCells(xlCellTypeConstants, 23).Select
' REplace tehse blank look like cells to something uniqu
Selection.Replace What:=»», Replacement:=»TOBEDELETED», LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
'now replace this uique text to nothing and voila all will disappear
Selection.Replace What:=»TOBEDELETED», Replacement:=»», LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

нашел другой способ. Установите автофильтр для все столбцы (важно или вы будете смещать данные), выбрав строку заголовка > вкладка «данные» > сортировка и фильтр — «фильтр».

Вернитесь к раскрывающемуся меню > 'Select all'. Престо:)

Не уверен, что это уже было сказано, но у меня была аналогичная проблема с ячейками, ничего не показывающими в них, но не пустыми при запуске формулы IsBlank ().

Я выбрал весь столбец, выбрал Find & Replace, нашел ячейки ни с чем и заменил на 0, затем снова запустил find и replace, найдя ячейки с 0 и заменив их на «».

Это решило мою проблему и позволило мне искать пустые ячейки (F5, специальные, пробелы) и удалять строки, которые были пусты….БУМ.

может не работать для каждого приложения, но это решило мою проблему.

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

Это работает с цифрами.

Если ваш диапазон O8:O20, то в соседнем пустом диапазоне (например, T8: T20) введите =O8/1 и заполните. Это даст вам результат #VALUE для «пустых» ячеек, и ваш исходный номер останется прежним.

затем с выбранным диапазоном T8: 20 (CTL -*, если это еще не так) нажмите F5 и выберите специальный. В специальном диалоге выберите ошибки и нажмите кнопку ОК. Это отменит выбор фактических номеров, оставив только выбранные ячейки #VALUE. Удалите их, и у вас будут фактические пустые ячейки. Скопируйте T8:T20 и вставьте обратно через O8: O20.

по существу, поскольку пустые ячейки не работают, вам нужно преобразовать «пустые» ячейки во что-то, что может зацепиться за специальное. Любое действие, которое будет преобразовано в #VALUE, будет работать, и другие типы «ошибок» также должны поддерживаться.

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

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

Он находится в разделе Данные > текст в Столбцы > разделители > удалить все символы синтаксического анализа (также можно выбрать текст, если хотите) > готово. Это должно заставить Excel повторно распознавать ваши данные с нуля или из текста и распознавать пробелы как действительно пустые.
Вы можете автоматизировать это в подпрограмме:

Sub F2Enter_new()
Dim rInput As Range
If Selection.Cells.Count > 1 Then Set rInput = Selection
Set rInput = Application.InputBox(Title:=»Select», prompt:=»input range», _
Default:=rInput.Address, Type:=8)
' Application.EnableEvents = False: Application.ScreenUpdating = False
For Each c In rInput.Columns
c.TextToColumns Destination:=Range(c.Cells(1).Address), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, _
FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True
Next c
Application.EnableEvents = True: Application.ScreenUpdating = True
End Sub

вы также можете включить эту прокомментированную строку, чтобы эта подпрограмма выполнялась «в фоновом режиме». Для этой подпрограммы улучшается производительность только немного (для других это действительно может помочь).
Имя-F2Enter, потому что исходный ручной метод для исправления этой проблемы «пробелов» — заставить Excel распознать формулу, нажав F2 и Enter.

вот как я исправил эту проблему без какого-либо кодирования.

1. выберите весь столбец, из которого я хотел удалить «пустые» ячейки.
2. щелкните вкладку Условное форматирование вверху.
3. Выберите «Новое Правило».
4. нажать «форматировать только ячейки, которые содержат».
5. изменить «между» На «равно».
6. нажмите на поле рядом с полем» равно».
7. нажмите одну из проблемных» пустых » ячеек.
8. клик кнопка формат.
9. выберите случайный цвет для заполнения коробки.
10. нажмите «OK».
11. Это должно изменить все проблемные» пустые » ячейки на цвет, который вы выбрали. Теперь щелкните правой кнопкой мыши одну из цветных ячеек и перейдите в раздел «сортировать» и «поместить выбранный цвет ячейки сверху».
12. это поставит все проблемные ячейки в верхней части столбца, и теперь все ваши другие ячейки останутся в исходном порядке, в котором вы их поместите. Теперь вы можете выбрать все проблемные ячейки в одной группе и нажмите кнопку Удалить ячейку сверху, чтобы избавиться от них.

• самым простым решением для меня было:
• 1) Выберите диапазон и скопируйте его (ctrl+c)
• 2)Создайте новый текстовый файл (в любом месте, он будет удален в ближайшее время), откройте текстовый файл, а затем вставьте в excel информацию (ctrl+v)
• 3) Теперь, когда информация в Excel находится в текстовом файле, выполните select all в текстовом файле (ctrl+a), а затем скопируйте (ctrl+c)
• 4) перейдите к началу исходного диапазона на шаге 1 и вставьте его старая информация из копии на Шаге 3.

готово! Больше никаких фальшивых заготовок! (теперь вы можете удалить временный текстовый файл)

Goto — > Special — >blanks не любит объединенные ячейки. Попробовать unmerging клеток выше диапазона, в котором вы хотите выбрать то, болванками попробовать снова.

У меня была аналогичная проблема с получением формулы COUNTA для подсчета непустых ячеек, она считала все из них (даже пустые как непустые), я попытался =CODE (), но у них не было пробелов или новых строк.

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

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

'This checks all the cells in a table so will need to be changed if you're using a range
Sub CreateBlanks()

Dim clientTable As ListObject
Dim selectedCell As Range

Set clientTable = Worksheets(«Client Table»).ListObjects(«ClientTable»)

For Each selectedCell In clientTable.DataBodyRange.Cells
If selectedCell = «» Then
selectedCell = «»
End If
Next selectedCell
End Sub

Источник: https://askdev.ru/q/excel-govorit-mne-chto-moi-pustye-yacheyki-ne-pustye-186301/

За 60 секунд: Как в Excel показывать нули как пустые ячейки

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

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

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

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

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

1. Откройте файл Excel с нулевыми ячейками

Мы начинаем с файла Excel с многочисленными ячейками 0, которые мы хотим превратить в пустые.

Файл Excel с нулевыми (0) ячейками.

2. Как отключить отображение нулей в Excel

Теперь перейдите на вкладку Файл в Excel. Найдите кнопку Параметры с левой стороны и, давайте, нажмите на неё.

Теперь измените параметр Дополнительно с левой стороны. Прокрутим вниз. И посреди этих параметров, идём в поле, которое говорит: Показывать нули в ячейках, которые содержат нулевые значения. Этот параметр управляет настройками для всей книги. Нам нужно снять этот флажок и, теперь, давайте нажмем OK.

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

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

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

Ячейки Excel теперь пусты, если в них ноль.

Закругляемся!

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

Более полезные учебники по Excel на Envato Tuts+

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

Вот несколько уроков по Excel, на которые можно перейти в настоящее время:

• • Microsoft Excel
  • За 60 секунд: Как защищать ячейки, листы и книги в Excel
  • Эндрю Чилдресс
• 1. Microsoft Excel
  2. За 60 секунд: Как вставлять, удалять и прятать новые листы в Excel
  3. Эндрю Чилдресс
• • Microsoft Excel
  • За 60 секунд: Как закреплять области и строки в Excel
  • Эндрю Чилдресс

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

Источник: https://business.tutsplus.com/ru/tutorials/excel-zeros-as-blank-cells—cms-29487

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

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

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

Я использовал функцию (=) со ссылкой на ячейку, чтобы выполнить это.

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

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

38

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

=A2 & «»

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

ответил wbeard52 28 апреля 2015, 07:56:10

17

Вот три ответа:

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

  

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

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

ответил Scott 7 декабря 2012, 01:30:16

3

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

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

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

=IFERROR((A1 & «») * 1,»»)

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

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

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

ответил rayzinnz 28 апреля 2015, 07:19:06

2

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

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

http://www.ozgrid.com/Excel/named-formulas.htm

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

= IFERROR( INDEX(INDIRECT(«EsZkouska»);
SMALL(IF((INDEX(INDIRECT(«EsZkouska»);;1‌​;1)=»ČSN721180″)*(INDEX(INDIRECT(«EsZkouska»);;9;1)=»RC_P_B»);
ROW(INDIRECT»EsZkouska»))-MIN(ROW(INDIRECT(«EsZkouska»)))+1;»»);1);17;1);»»)

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

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

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

{=IF(RC_P_B»»; RC_P_B;»—«)}

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

ответил Vojtěch Dohnal 25 июля 2014, 16:35:30

2

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

ответил Firas 9 ноября 2014, 09:28:11

2

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

ответил Aleksey F. 8 июня 2015, 13:22:15

1

=IF(ISTEXT(Sheet1!A2), Sheet1!A2, «»)

ответил Brad Patton 7 декабря 2012, 01:30:05

1

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

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

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

• Я экспериментировал с некоторыми другими функциями и нашел то, что я считаю самым элегантным решением на сегодняшний день. Продолжая приведенный выше пример:
• = CELL («contents», A2)
• возвращает числовое значение, которое может быть отформатировано как число, и оно возвращает пустое значение, когда ссылочная ячейка пуста. По какой-то причине это решение, похоже, не появляется ни в одном из предложенных онлайн-предложений, но оно

ответил Schaetzer 11 июня 2015, 06:35:03

1

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

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

ответил C.B.MISRA 22 июня 2017, 15:20:12

Public Function FuncDisplayStringNoValue(MyCell As Variant) As String

Dim Result As Variant

If IsEmpty(MyCell) Then

FuncDisplayStringNoValue = «No Value»

Else
Result = CDec(MyCell)

Result = Round(Result, 2)

FuncDisplayStringNoValue = «» & Result

End If

End Function
ответил Ramon Salazar 8 января 2016, 20:33:14

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

ответил Butonic 22 апреля 2018, 19:42:25

-1

Что работало для меня в Office 2013:

=IF(A2=»»,» «,A2)

Где в первом наборе цитат нет места, а на втором множестве есть пробел.

Надеюсь, что это поможет

ответил Andy 17 марта 2015, 23:24:48

-1

У меня была аналогичная проблема, и я думаю, что нашел метод для вас. Он исправил мои проблемы.

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

=A1

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

=T(A1)

Что касается форматирования ячейки, ссылка (columnn C) должна быть общей.

ответил Will Prater 15 июля 2015, 18:20:22

-1

Решение: =IF('Wk1 Data-replace w dt bgn & end'!C2>0, 'Wk1 Data-replace w dt bgn & end'!C2, «»)

1. Объяснение:
   Чтобы решить эту проблему, я использовал два набора команд If.
2. Часть первая команды:
   Я сказал ячейке отобразить содержимое контрольной ячейки, если эталонная ячейка содержит данные (Excel интерпретирует это как ячейку со значением больше 0)
3. Часть вторая команды:
   Я сказал, как вести себя, если не было данных; «» означает оставить пустым

Примечание:
Wk1 Data-replace w dt bgn & end '! C2 — моя опорная ячейка.

ответил Crystal Harris 30 ноября 2015, 08:58:52

-1

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

ответил John Alonso 14 августа 2016, 00:40:08

-1

У меня есть решение для меня:

IF(cell reference=»»,»»,cell reference)

Пояснение:

«» равен пустой или пустой ячейке.
Теперь я поворачиваю только if. Если ячейка пуста, сделайте ее пустой, в противном случае используйте ссылку на ячейку.

ответил Sebastian Tidare 29 января 2018, 15:01:59

-2

Перейдите к форматированию ячеек.

Затем выберите пользовательский формат,
и введите следующее:
0;-0;;@

ответил Avik 25 сентября 2015, 22:33:35

Источник: https://sprosi.pro/questions/80819/otobrajat-pustoe-znachenie-pri-ssyilke-na-pustuyu-yacheyku-v-excel-2010

Как я могу оставить пустую строку вместо 0 в ячейке в.xlsx?

Можно ли оставить ячейку пустой в.xlsx?

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

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

Когда данные считываются позже внешним процессом, над которым мы не имеем контроля, это вызывает проблемы.

Чтобы воспроизвести это, откройте новую таблицу Excel (2007-2016), поместите формулу = «» в B2, выберите весь лист, затем скопируйте и вставьте значения. Как и ожидалось, он будет выглядеть пустым, а xlLastCell будет B2 вместо A1. Даже если вы используете activesheet.usedrange он не сбросит xlLastCell на A1, потому что B2 действительно имеет значение.

При сохранении и анализе данных в текстовом редакторе (измените расширение с.xlsx на.zip, извлеките и распакуйте xlworksheetssheet1.xml для незнакомых), вы увидите, что в узле sheetData есть только один value, это узел под B2, и он показывает 0 (что не эквивалентно пустой строке, которая была вставлена в эту ячейку).

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

Найденная замена не работает потому что в этой нашей таблице примеров, когда вы выбираете «Найти все» для пустых строк, он находит только A1, который, как вы помните, мы не набрали (0 не найден вообще).

Есть ли способ фактически оставить пул значений (или хотя бы пустую строку) в результирующем xml этим способом?

Вопросы могут быть, почему бы не использовать CSV файл? Почему бы не сделать все это самостоятельно? Включить макросы для этого пользователя? Почему бы не потратить 20-30 часов и написать.

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

Источник: http://qaru.site/questions/14148216/how-can-i-leave-an-empty-string-instead-of-0-in-a-cell-in-an-xlsx

Пустая ячейка, не регистрирующаяся как ноль в excel

Кажется, это ошибка в Excel 2013.

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

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

• = IF (ISBLANK (a1), 1,2) == > возвращает 2. (т. е. ячейка не является пустой).

• = IF (A1 = 0,0,1) == > Возвращает значение 1. (т. Е. Ячейка не содержит нуля).

• = A1 * 2 == > Возвращает #VALUE! (т. е. ячейка не содержит числа).

• = IF ((A1) > 1,1,0) == > Возвращает 1 (т. Е. Ячейка содержит число больше 1!).

• = IF (A + > 999999999999999E + 307,1,0) == > Возвращает 1. (т. Е. Ячейка содержит очень большое число).

•=»X» & amp; A1 & amp; «X» == > Возвращает «XX». (т. е. ячейка A1 действительно пуста.).

• = CELL («формат», A1) == > Возвращает «G». (то есть формат номера ячейки является общим).

• = CELL («префикс», A1) == > Возвращает «^». (т. е. ячейка центра отформатирована для текста и цифр).

• = CELL («type», A1) == > Возвращает «I». (т. е. ячейка содержит текст!).

• = VALUE (A1) == > Возвращает #VALUE! (т. е. вызов не содержит текстовую строку, которая выглядит как число).

• = LEN (A1) == > Возвращает 0. (т. Е. Длина любой строки равна нулю).

• = T (A1) == > Возвращает ничего (т. Е. Возвращает текстовую строку нулевой длины).

• = UNICODE (A1) == > Возвращает #VALUE! (т. е. ячейка не содержит строку).

Для меня это происходит, когда я импортировал данные из другого приложения. (Telekurs Infinacials в моем случае). Функция = If (A1 = 0,0,1) сообщает, что ячейка a1 не равна нулю. Функция = A1 + 1 создает #VALUE! ошибка. Несколько других видов математики в пустой ячейке дают # ВАЛЮТ! результат.

Логическое соединение состоит в том, что ячейка не содержит числа. Однако это противоречит формуле = if (A1 > 99,1,1), что подразумевает, что ячейка содержит большое число.

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

(Вы можете проверить это, например, вставив формулу типа=»X» & amp; A1 & amp; «» X «, где A1 — это ячейка, которую вы подозреваете, не пуста. Если формула возвращает« XX », ячейка не содержит никакого номера, ни текста. В противном случае будет пробел, число или текст, содержащий два «X».).

Ячейка, похоже, тоже не пуста, потому что если вы используете формулу = IF (ISBLANK (a1), 1,2), она возвращает 2.

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

Единственное решение — щелкнуть по ячейке, затем щелкнуть по строке ввода и нажать клавишу ввода. Проблема исчезает, и она становится пустой ячейкой, которая также возвращает значение нуля.

• Это решение прекрасно, если у вас есть только одна ячейка, с которой нужно иметь дело, но если у вас есть таблица с тысячами таких ячеек, это проблема. Поэтому в электронной таблице, содержащей числа и в которой вы хотите, чтобы «видимая» пустая ячейка была рассчитана как ноль, вам нужно использовать функцию iferror следующим образом:
• Старая формула: = A1 * 2
• Новая формула: = IFERROR ((A1 * 1), 0) * 2

    

Источник: https://ubuntugeeks.com/questions/407718/a-blank-cell-not-registering-as-zero-in-excel