Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/Как в формуле вернуть пустую ячейку? - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Как в формуле вернуть пустую ячейку? (Формулы/Formulas)
Как в формуле вернуть пустую ячейку?
AlexKniga Дата: Воскресенье, 22.05.2016, 11:17 | Сообщение № 1
Группа: Пользователи
Ранг: Прохожий
Сообщений: 3
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
Идет обработка данных, иногда данных нет, и тогда надо вернуть эквивалент пустой ячейки.
1)
Код
=ЕСЛИ(ЕЧИСЛО(A1); A1*1000; "")

2)
Код
=ЕСЛИ(ЕЧИСЛО(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
 
Ответить
СообщениеИдет обработка данных, иногда данных нет, и тогда надо вернуть эквивалент пустой ячейки.
1)
Код
=ЕСЛИ(ЕЧИСЛО(A1); A1*1000; "")

2)
Код
=ЕСЛИ(ЕЧИСЛО(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]

Автор - AlexKniga
Дата добавления - 22.05.2016 в 11:17
Manyasha Дата: Воскресенье, 22.05.2016, 12:04 | Сообщение № 2
Группа: Модераторы
Ранг: Старожил
Сообщений: 2198
Репутация: 898 ±
Замечаний: 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
 
Ответить
Сообщение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))

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

Автор - Manyasha
Дата добавления - 22.05.2016 в 12:04
SLAVICK Дата: Воскресенье, 22.05.2016, 12:28 | Сообщение № 3
Группа: Модераторы
Ранг: Старожил
Сообщений: 2290
Репутация: 766 ±
Замечаний: 0% ±

2019
Можно заменить формулу среднего на:
Код
=СРЗНАЧ(ЕСЛИ(ЕОШИБКА(B4:B14);"";B4:B14))

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


Иногда все проще чем кажется с первого взгляда.
 
Ответить
СообщениеМожно заменить формулу среднего на:
Код
=СРЗНАЧ(ЕСЛИ(ЕОШИБКА(B4:B14);"";B4:B14))

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

Автор - SLAVICK
Дата добавления - 22.05.2016 в 12:28
AlexKniga Дата: Воскресенье, 22.05.2016, 14:38 | Сообщение № 4
Группа: Пользователи
Ранг: Прохожий
Сообщений: 3
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013

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

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

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

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

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

Приложил пример.
 
Ответить
Сообщение

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

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

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

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

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

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

Автор - AlexKniga
Дата добавления - 22.05.2016 в 14:38
_Boroda_ Дата: Воскресенье, 22.05.2016, 16:16 | Сообщение № 5
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16666
Репутация: 6478 ±
Замечаний: 0% ±

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
 
Ответить
СообщениеВ ячейке D12 можно вместо СРЗНАЧ написать вот так (немассивная)
Код
=АГРЕГАТ(1;6;D$2:D$11)

Автор - _Boroda_
Дата добавления - 22.05.2016 в 16:16
AlexKniga Дата: Вторник, 24.05.2016, 19:12 | Сообщение № 6
Группа: Пользователи
Ранг: Прохожий
Сообщений: 3
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
Остановился на
Код
=ЕСЛИ(ЕЧИСЛО(A1); A1*1000; НД())

+
перелопачивание обработки на массивную формулу
Код
=СРЗНАЧ(ЕСЛИ(ЕОШИБКА(B4:B14);"";B4:B14))


[offtop]Определение среднего это малая часть обработки[/offtop]
 
Ответить
СообщениеОстановился на
Код
=ЕСЛИ(ЕЧИСЛО(A1); A1*1000; НД())

+
перелопачивание обработки на массивную формулу
Код
=СРЗНАЧ(ЕСЛИ(ЕОШИБКА(B4:B14);"";B4:B14))


[offtop]Определение среднего это малая часть обработки[/offtop]

Автор - AlexKniga
Дата добавления - 24.05.2016 в 19:12
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Как в формуле вернуть пустую ячейку? (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

Яндекс.Метрика Яндекс цитирования
© 2010-2024 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!