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

Вход

Регистрация

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

 

= Мир MS Excel/Формула iferror возвращает кавычки, а не пустое значение - Мир MS Excel

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

Excel 2013
Коллеги, добрый день.

Делаю ценовой мониторинг. Есть сводноая таблица с ценами по разным сетям.
Есть формула
Код
AVERAGE(IFERROR(GETPIVOTDATA(""&$E$5,$A$5,"Номенклатура",$O6),""),IFERROR(GETPIVOTDATA(""&$E$5,$A$5,"Номенклатура",$P6),""),IFERROR(GETPIVOTDATA(""&$E$5,$A$5,"Номенклатура",$Q6),""))

Суть: считает среднее для 3 значений из сводной таблицы. Если значения в сводной таблице нет, то формула iferror (еслиошибка) должна подставить пустое значение, этим мы избегаем ошибки #REF!
Однако формула считается неправильно. Конкретно в этом примере, нет второго значения. В итоге формула приобретает вид average(106,"",108) и возвращает ошибку #VALUE. Если вместо "" в исходной формуле iferror ставить число (например, 0), то формула работает. Но среднее значение становится неверным.

ВОПРОС: Почему iferror оставляет кавычки, а не пустое значение? Кто-нибудь с таким сталкивался?

К сожалению, не получается приложить файл, получается больше 100 кб при любой сводной таблице.





[moder]Оформите формулы тегами (кнопка fx)[/moder]


Сообщение отредактировал dewre - Понедельник, 16.11.2015, 12:47
 
Ответить
СообщениеКоллеги, добрый день.

Делаю ценовой мониторинг. Есть сводноая таблица с ценами по разным сетям.
Есть формула
Код
AVERAGE(IFERROR(GETPIVOTDATA(""&$E$5,$A$5,"Номенклатура",$O6),""),IFERROR(GETPIVOTDATA(""&$E$5,$A$5,"Номенклатура",$P6),""),IFERROR(GETPIVOTDATA(""&$E$5,$A$5,"Номенклатура",$Q6),""))

Суть: считает среднее для 3 значений из сводной таблицы. Если значения в сводной таблице нет, то формула iferror (еслиошибка) должна подставить пустое значение, этим мы избегаем ошибки #REF!
Однако формула считается неправильно. Конкретно в этом примере, нет второго значения. В итоге формула приобретает вид average(106,"",108) и возвращает ошибку #VALUE. Если вместо "" в исходной формуле iferror ставить число (например, 0), то формула работает. Но среднее значение становится неверным.

ВОПРОС: Почему iferror оставляет кавычки, а не пустое значение? Кто-нибудь с таким сталкивался?

К сожалению, не получается приложить файл, получается больше 100 кб при любой сводной таблице.





[moder]Оформите формулы тегами (кнопка fx)[/moder]
Автор - dewre
Дата добавления - 16.11.2015 в 10:36
Pelena Дата: Понедельник, 16.11.2015, 11:22 | Сообщение № 2
Группа: Админы
Ранг: Местный житель
Сообщений: 19197
Репутация: 4423 ±
Замечаний: ±

Excel 365 & Mac Excel
Цитата dewre, 16.11.2015 в 10:36, в сообщении № 1
Почему iferror оставляет кавычки, а не пустое значение?

Потому что в формуле так прописано, что в случае ошибки вернуть ""
Вместо картинок приложите файл с примером

"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
Сообщение
Цитата dewre, 16.11.2015 в 10:36, в сообщении № 1
Почему iferror оставляет кавычки, а не пустое значение?

Потому что в формуле так прописано, что в случае ошибки вернуть ""
Вместо картинок приложите файл с примером
Автор - Pelena
Дата добавления - 16.11.2015 в 11:22
dewre Дата: Понедельник, 16.11.2015, 12:11 | Сообщение № 3
Группа: Пользователи
Ранг: Прохожий
Сообщений: 3
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
вот файл.
Благодарю.
[moder]
Цитата dewre, 16.11.2015 в 10:36, в сообщении № 1
Модератор:
Оформите формулы тегами (кнопка fx)
[/moder]
К сообщению приложен файл: 0885902.xlsx (36.1 Kb)


Сообщение отредактировал Manyasha - Понедельник, 16.11.2015, 12:19
 
Ответить
Сообщениевот файл.
Благодарю.
[moder]
Цитата dewre, 16.11.2015 в 10:36, в сообщении № 1
Модератор:
Оформите формулы тегами (кнопка fx)
[/moder]
Автор - dewre
Дата добавления - 16.11.2015 в 12:11
Roman777 Дата: Понедельник, 16.11.2015, 12:37 | Сообщение № 4
Группа: Проверенные
Ранг: Ветеран
Сообщений: 980
Репутация: 127 ±
Замечаний: 0% ±

Excel 2007, Excel 2013
dewre, у Вас получается формула
Код
=СРЗНАЧ(A;"";B)
тут будет ошибка, вместо "" мб лучше поставить 0?

Много чего не знаю!!!!
 
Ответить
Сообщениеdewre, у Вас получается формула
Код
=СРЗНАЧ(A;"";B)
тут будет ошибка, вместо "" мб лучше поставить 0?
Автор - Roman777
Дата добавления - 16.11.2015 в 12:37
Pelena Дата: Понедельник, 16.11.2015, 12:41 | Сообщение № 5
Группа: Админы
Ранг: Местный житель
Сообщений: 19197
Репутация: 4423 ±
Замечаний: ±

Excel 365 & Mac Excel
Попробуйте так, формула массива вводится нажатием сочетания клавиш Ctrl+Shift+Enter
Код
=СРЗНАЧ(ЕСЛИОШИБКА(ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ(""&$E$4;$A$5;"Номенклатура";O5:Q5);ЛОЖЬ))
К сообщению приложен файл: 3093146.xlsx (37.5 Kb)

"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
СообщениеПопробуйте так, формула массива вводится нажатием сочетания клавиш Ctrl+Shift+Enter
Код
=СРЗНАЧ(ЕСЛИОШИБКА(ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ(""&$E$4;$A$5;"Номенклатура";O5:Q5);ЛОЖЬ))
Автор - Pelena
Дата добавления - 16.11.2015 в 12:41
dewre Дата: Понедельник, 16.11.2015, 12:43 | Сообщение № 6
Группа: Пользователи
Ранг: Прохожий
Сообщений: 3
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
Если поставить "0", то считать будет, но среднее значение получится неверным.
Модератор предложил написать формулы через FX. Попробуем.
[moder]Я предложила Вам исправить Ваш первый пост, заключив написанную там формулу в теги с помощью кнопки fx[/moder]
А, жаль=(

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


Сообщение отредактировал dewre - Понедельник, 16.11.2015, 12:48
 
Ответить
СообщениеЕсли поставить "0", то считать будет, но среднее значение получится неверным.
Модератор предложил написать формулы через FX. Попробуем.
[moder]Я предложила Вам исправить Ваш первый пост, заключив написанную там формулу в теги с помощью кнопки fx[/moder]
А, жаль=(

Также примечание: Интересен тот факт, что исходная формула работает неправильно, однако если формулу разбить на составные части и каждую часть поместить в отдельную ячейку, то всё будет работать.
Автор - dewre
Дата добавления - 16.11.2015 в 12:43
Roman777 Дата: Понедельник, 16.11.2015, 13:14 | Сообщение № 7
Группа: Проверенные
Ранг: Ветеран
Сообщений: 980
Репутация: 127 ±
Замечаний: 0% ±

Excel 2007, Excel 2013
dewre, длиновато, но в Вашем случае можно так попробовать:
Код
=(ЕСЛИОШИБКА(ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ(""&$E$4;$A$5;"Номенклатура";$O6);0)+ЕСЛИОШИБКА(ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ(""&$E$4;$A$5;"Номенклатура";$P6);0)+ЕСЛИОШИБКА(ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ(""&$E$4;$A$5;"Номенклатура";$Q6);0))/СЧЁТ(ЕСЛИОШИБКА(ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ(""&$E$4;$A$5;"Номенклатура";$O6);"");ЕСЛИОШИБКА(ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ(""&$E$4;$A$5;"Номенклатура";$P6);"");ЕСЛИОШИБКА(ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ(""&$E$4;$A$5;"Номенклатура";$Q6);""))

Много чего не знаю!!!!

Сообщение отредактировал Roman777 - Понедельник, 16.11.2015, 13:45
 
Ответить
Сообщениеdewre, длиновато, но в Вашем случае можно так попробовать:
Код
=(ЕСЛИОШИБКА(ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ(""&$E$4;$A$5;"Номенклатура";$O6);0)+ЕСЛИОШИБКА(ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ(""&$E$4;$A$5;"Номенклатура";$P6);0)+ЕСЛИОШИБКА(ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ(""&$E$4;$A$5;"Номенклатура";$Q6);0))/СЧЁТ(ЕСЛИОШИБКА(ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ(""&$E$4;$A$5;"Номенклатура";$O6);"");ЕСЛИОШИБКА(ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ(""&$E$4;$A$5;"Номенклатура";$P6);"");ЕСЛИОШИБКА(ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ(""&$E$4;$A$5;"Номенклатура";$Q6);""))
Автор - Roman777
Дата добавления - 16.11.2015 в 13:14
Gustav Дата: Понедельник, 16.11.2015, 16:07 | Сообщение № 8
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2757
Репутация: 1139 ±
Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1
Цитата dewre, 16.11.2015 в 12:43, в сообщении № 6
Интересен тот факт, что исходная формула работает неправильно, однако если формулу разбить на составные части и каждую часть поместить в отдельную ячейку, то всё будет работать.

Думаю, что это от того, что в этом случае функция СРЗНАЧ воспринимает диапазон ячеек как массив.

Сравните: формула
Код
=СРЗНАЧ(106;"";108)
дает ошибку #ЗНАЧ!, а формула со скобками массива
Код
=СРЗНАЧ({106;"";108})
даёт правильный ответ 107. Неприятность состоит в том, что мы, увы, не можем подставить в массив констант (внутрь фигурных скобок) выражения или ссылки на ячейки.

Если не боитесь макросов, то простейшая пользовательская функция-оболочка вокруг Average решает проблему
Код
=СРЗНАЧпольз(106;"";108)
и дает правильный ответ 107. Работает и дает 107 даже в следующем "осложненном" случае :)
Код
=СРЗНАЧпольз(100+6;"";108;"привет";ИСТИНА)

Код же функции прост до безобразия:
[vba]
Код
Function СРЗНАЧпольз(ParamArray значения())
    
    СРЗНАЧпольз = WorksheetFunction.Average(значения)

End Function
[/vba]

МОИ: Ник, Tip box: 41001663842605
 
Ответить
Сообщение
Цитата dewre, 16.11.2015 в 12:43, в сообщении № 6
Интересен тот факт, что исходная формула работает неправильно, однако если формулу разбить на составные части и каждую часть поместить в отдельную ячейку, то всё будет работать.

Думаю, что это от того, что в этом случае функция СРЗНАЧ воспринимает диапазон ячеек как массив.

Сравните: формула
Код
=СРЗНАЧ(106;"";108)
дает ошибку #ЗНАЧ!, а формула со скобками массива
Код
=СРЗНАЧ({106;"";108})
даёт правильный ответ 107. Неприятность состоит в том, что мы, увы, не можем подставить в массив констант (внутрь фигурных скобок) выражения или ссылки на ячейки.

Если не боитесь макросов, то простейшая пользовательская функция-оболочка вокруг Average решает проблему
Код
=СРЗНАЧпольз(106;"";108)
и дает правильный ответ 107. Работает и дает 107 даже в следующем "осложненном" случае :)
Код
=СРЗНАЧпольз(100+6;"";108;"привет";ИСТИНА)

Код же функции прост до безобразия:
[vba]
Код
Function СРЗНАЧпольз(ParamArray значения())
    
    СРЗНАЧпольз = WorksheetFunction.Average(значения)

End Function
[/vba]
Автор - Gustav
Дата добавления - 16.11.2015 в 16:07
Светлый Дата: Понедельник, 16.11.2015, 20:32 | Сообщение № 9
Группа: Друзья
Ранг: Старожил
Сообщений: 1830
Репутация: 510 ±
Замечаний: 0% ±

Excel 2013, 2016
Вместо "" подставьте ссылку на заведомо пустую ячейку.
Код
=СРЗНАЧ(106;$XX$1;108)


У меня сработала массивная формула:
Код
=СРЗНАЧ(ЕСЛИОШИБКА(ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ(""&$E$4;$A$5;"Номенклатура";$O6:$Q6);""))
К сообщению приложен файл: 0885902_1.xlsx (37.4 Kb)

Программировать проще, чем писать стихи.

Сообщение отредактировал Светлый - Понедельник, 16.11.2015, 20:59
 
Ответить
СообщениеВместо "" подставьте ссылку на заведомо пустую ячейку.
Код
=СРЗНАЧ(106;$XX$1;108)


У меня сработала массивная формула:
Код
=СРЗНАЧ(ЕСЛИОШИБКА(ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ(""&$E$4;$A$5;"Номенклатура";$O6:$Q6);""))
Автор - Светлый
Дата добавления - 16.11.2015 в 20:32
Gustav Дата: Вторник, 17.11.2015, 11:41 | Сообщение № 10
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2757
Репутация: 1139 ±
Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1
Цитата Светлый, 16.11.2015 в 20:32, в сообщении № 9
Вместо "" подставьте ссылку на заведомо пустую ячейку.
=СРЗНАЧ(106;$XX$1;108)

Интересное замечание. НО! Нам-то нужна не просто ссылка на "сферическую ячейку в вакууме", а корректная обработка конструкции ЕСЛИОШИБКА. И вот в составе конструкции ЕСЛИОШИБКА(...;$XX$1) наша пустая ячейка превратится в 0, который уже будет влиять на среднее:
Код
=СРЗНАЧ(106;ЕСЛИОШИБКА(НД();$XX$1);108)
вместо ожидаемых 107 вернет "неправильное" значение 71,33, что равносильно
Код
=СРЗНАЧ(106;0;108)

МОИ: Ник, Tip box: 41001663842605
 
Ответить
Сообщение
Цитата Светлый, 16.11.2015 в 20:32, в сообщении № 9
Вместо "" подставьте ссылку на заведомо пустую ячейку.
=СРЗНАЧ(106;$XX$1;108)

Интересное замечание. НО! Нам-то нужна не просто ссылка на "сферическую ячейку в вакууме", а корректная обработка конструкции ЕСЛИОШИБКА. И вот в составе конструкции ЕСЛИОШИБКА(...;$XX$1) наша пустая ячейка превратится в 0, который уже будет влиять на среднее:
Код
=СРЗНАЧ(106;ЕСЛИОШИБКА(НД();$XX$1);108)
вместо ожидаемых 107 вернет "неправильное" значение 71,33, что равносильно
Код
=СРЗНАЧ(106;0;108)
Автор - Gustav
Дата добавления - 17.11.2015 в 11:41
SLAVICK Дата: Вторник, 17.11.2015, 12:00 | Сообщение № 11
Группа: Модераторы
Ранг: Старожил
Сообщений: 2290
Репутация: 766 ±
Замечаний: 0% ±

2019
Если не нужно учитывать 0, тогда формула массива на основе формулы Pelena :D :
Код
=СУММПРОИЗВ((ЕСЛИОШИБКА(ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ(""&$E$4;$A$5;"Номенклатура";O6:Q6);0)))/
СУММПРОИЗВ(--(ЕСЛИОШИБКА(ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ(""&$E$4;$A$5;"Номенклатура";O6:Q6);0)>0))

или тоже, но с Сумм:
Код
=СУММ((ЕСЛИОШИБКА(ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ(""&$E$4;$A$5;"Номенклатура";O5:Q5);0)))/
СУММ(--(ЕСЛИОШИБКА(ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ(""&$E$4;$A$5;"Номенклатура";O5:Q5);0)<>0))
К сообщению приложен файл: 5950773.xlsx (35.7 Kb)

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

Сообщение отредактировал SLAVICK - Вторник, 17.11.2015, 12:05
 
Ответить
СообщениеЕсли не нужно учитывать 0, тогда формула массива на основе формулы Pelena :D :
Код
=СУММПРОИЗВ((ЕСЛИОШИБКА(ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ(""&$E$4;$A$5;"Номенклатура";O6:Q6);0)))/
СУММПРОИЗВ(--(ЕСЛИОШИБКА(ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ(""&$E$4;$A$5;"Номенклатура";O6:Q6);0)>0))

или тоже, но с Сумм:
Код
=СУММ((ЕСЛИОШИБКА(ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ(""&$E$4;$A$5;"Номенклатура";O5:Q5);0)))/
СУММ(--(ЕСЛИОШИБКА(ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ(""&$E$4;$A$5;"Номенклатура";O5:Q5);0)<>0))
Автор - SLAVICK
Дата добавления - 17.11.2015 в 12:00
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Формула iferror возвращает кавычки, а не пустое значение (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

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