Как в формуле вернуть пустую ячейку?

Старая форма входа

Страница 1 из 1 1
Модератор форума: китин, _Boroda_

Как в формуле вернуть пустую ячейку? (Формулы/Formulas)

AlexKniga

Дата: Воскресенье, 22.05.2016, 11:17 | Сообщение № 1

Группа: Пользователи

Ранг: Прохожий

Сообщений: 3

Репутация: 0 ±

Замечаний: 0% ±

Excel 2013

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

Код

=ЕСЛИ(ЕЧИСЛО(A1); A1*1000; "")

Код

=ЕСЛИ(ЕЧИСЛО(A1); A1*1000; НД())

В 1м случае такие функции как AVERAGE() СРЗНАЧ() нормально воспринимают "" как пустую ячейку, но построитель графиков трактует "" как 0 (ноль).
В 2м случае такие функции как AVERAGE() СРЗНАЧ() возвращает #Н/Д, а построитель графиков соединят прямой соседние данные. При настройках по умолчанию, построитель графиков для пустых ячеек делает разрыв.

Что вернуть в формуле, чтобы и построитель графиков и функции типа AVERAGE() СРЗНАЧ() воспринимали результат как пустую ячейку?
Для построителя графиков послабление: не обязательно делать разрыв, можно соединять прямой. Главное не падать в ноль.

VBA типа:
[vba]

Код

Sub FindAndClearEmptyInFormulas()
    Dim rCell As Range
    With Application: .ScreenUpdating = False: .EnableEvents = False: .Calculation = xlManual: End With
    For Each rCell In ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas)
        If rCell.Value = "" Then rCell.ClearContents
    Next rCell
    With Application: .ScreenUpdating = True: .EnableEvents = True: .Calculation = xlAutomatic: End With
End Sub

[/vba]
не предлагать.
[moder]Приложите файл-пример.[/moder]

К сообщению приложен файл: ReturnEmptyCell.xlsx (23.6 Kb)

Сообщение отредактировал AlexKniga - Воскресенье, 22.05.2016, 14:31

Ответить

Manyasha

Дата: Воскресенье, 22.05.2016, 12:04 | Сообщение № 2

Группа: Модераторы

Ранг: Старожил

Сообщений: 2198

Репутация: 901 ±

Замечаний: 0% ±

Excel 2010, 2016

AlexKniga, можно так:

Код

=ЕСЛИОШИБКА(ИНДЕКС($A$1:$A$10;НАИМЕНЬШИЙ(ЕСЛИ(ЕЧИСЛО($A$1:$A$10);СТРОКА($A$1:$A$10));СТРОКА(A1)))*1000;"")

а в графике именованный диапазон тест:

Код

=Лист1!$B$1:ИНДЕКС(Лист1!$B$1:$B$100;ПОИСКПОЗ(9^9;Лист1!$B$1:$B$100))

Если возникнут вопросы, показывайте файл.

К сообщению приложен файл: 0030153.xlsx (11.5 Kb)

ЯД: 410013299366744 WM: R193491431804

Ответить

SLAVICK

Дата: Воскресенье, 22.05.2016, 12:28 | Сообщение № 3

Группа: Модераторы

Ранг: Старожил

Сообщений: 2290

Репутация: 766 ±

Замечаний: 0% ±

2019

Можно заменить формулу среднего на:

Код

=СРЗНАЧ(ЕСЛИ(ЕОШИБКА(B4:B14);"";B4:B14))

формула массива.

К сообщению приложен файл: 7676231.xlsx (14.2 Kb)

Иногда все проще чем кажется с первого взгляда.

Ответить

AlexKniga

Дата: Воскресенье, 22.05.2016, 14:38 | Сообщение № 4

Группа: Пользователи

Ранг: Прохожий

Сообщений: 3

Репутация: 0 ±

Замечаний: 0% ±

Excel 2013

Цитата Manyasha, 22.05.2016 в 12:04, в сообщении № 2

=IFERROR(INDEX($A$1:$A$10,SMALL(IF(ISNUMBER($A$1:$A$10),ROW($A$1:$A$10)),ROW(A1)))*1000,"")

"Уплотнять" нельзя.

Цитата SLAVICK, 22.05.2016 в 12:28, в сообщении № 3

Можно заменить формулу среднего на:
=AVERAGE(IF(ISERROR(B4:B14),"",B4:B14))

Спасибо, если не будет лучшего...

Часто, удобно выделить несколько ячеек и в строке состояния сразу увидеть среднее/максимум/минимум и т.д.

Приложил пример.

Ответить

_Boroda_

Дата: Воскресенье, 22.05.2016, 16:16 | Сообщение № 5

Группа: Админы

Ранг: Местный житель

Сообщений: 16711

Репутация: 6502 ±

Замечаний: ±

2003; 2007; 2010; 2013 RUS

В ячейке D12 можно вместо СРЗНАЧ написать вот так (немассивная)

Код

=АГРЕГАТ(1;6;D$2:D$11)

К сообщению приложен файл: 3015546.xlsx (23.9 Kb)

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995