Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Построение диаграммы по количеству значений, без учета нулей или пробелов
 
#1
23.01.2021 00:30:12
В примере на листе 4 строится диаграмма на основании выбора списка в ячейке А11. Как видно из столбцов В и С на разных участках количество случаев разное количество. Как мне сделать что бы в данном примере при выбора участок 1 в списке диаграмма строилась на 8 значений, а при выборе участок 2 по 3... причем в любое время все может поменяться как по количеству так и по какому именно критерию (критерии могут быть в строке 3 и 8 к примеру). Необходимо что бы диаграмма сама понимала по списку сколько по количеству критериев и строила только по ним, без учета нулей или пробелов. Желательно без макросов
 
 
 
#2
23.01.2021 12:08:52
Зачем вам такие сложности и построение диаграммы на диапазонах со смещением?
Сейчас можно делать все гораздо проще и универсальнее:

Можно загрузить данные в Power query - буквально в пару кликов мышкой перестроить данные в нормальный формат для сводной, загрузить их в модель данных и строить любые нормальные графики с нужными срезами исключая ненужные данные

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

Пример прикрепил.
Нужно бежать со всех ног, чтобы только оставаться на месте, а чтобы куда-то попасть, надо бежать как минимум вдвое быстрее!
 
 
 
#3
25.01.2021 01:47:37
Проблема в том, что на предприятии такого нет. А есть эксельи далико не новая версия)

Хорошо, мб есть смысл каким то образом поставить фильтры кнопками для сводной?
 
 
 
#4
25.01.2021 08:22:38
как то странно себя ведет файл или диаграмма.  Динамический массив формулой создается , но потом Excel крашится при добавлении его в диаграмму. Я еще 23го посомтрел. Не стал отписывать
для списка
=INDEX($A:$A;N(INDEX(AGGREGATE(15;6;ROW($A$2:$A$9)/(INDEX($B$2:$C$9;;MATCH($A$11;$B$1:$C$1;))<>0);ROW($A$1:INDEX($A:$A;COUNTIF(INDEX($B$2:$C$9;;MATCH($A$11;$B$1:$C$1;));"<>0"))));)))
для значений
=INDEX($B:$C;N(INDEX(AGGREGATE(15;6;ROW($A$2:$A$9)/(INDEX($B$2:$C$9;;MATCH($A$11;$B$1:$C$1;))<>0);ROW($A$1:INDEX($A:$A;COUNTIF(INDEX($B$2:$C$9;;MATCH($A$11;$B$1:$C$1;));"<>0"))));));MATCH($A$11;$B$1:$C$1;))
Изменено: БМВ - 25.01.2021 08:52:41
По вопросам из тем форума, личку не читаю.
 
 
 
#5
25.01.2021 10:45:43
БМВ, можете пример создать?
 
 
 
#6
25.01.2021 13:04:53
Через Aggregate не проходит, а через Small ок.
=INDEX($A:$A;N(INDEX(SMALL(IF(INDEX($B$2:$C$9;;MATCH($A$11;$B$1:$C$1;))<>0;ROW($A$2:$A$9));ROW($A$1:INDEX($A:$A;COUNTIF(INDEX($B$2:$C$9;;MATCH($A$11;$B$1:$C$1;));"<>0"))));)))
=INDEX($B:$C;N(INDEX(SMALL(IF(INDEX($B$2:$C$9;;MATCH($A$11;$B$1:$C$1;))<>0;ROW($A$2:$A$9));ROW($A$1:INDEX($A:$A;COUNTIF(INDEX($B$2:$C$9;;MATCH($A$11;$B$1:$C$1;));"<>0"))));));MATCH($A$11;$B$1:$C$1;))
По вопросам из тем форума, личку не читаю.
 
 
 
#7
06.02.2021 15:33:43
Помогите убрать ошибку. В примере диаграмма строиться в по данным В2:С9 по выбору А11. Причем в графике зашита формула при нулевых значениях отображение будет отсутствовать. Ошибка появляется если все значения столбца В или С будут 0. Необходимо чтоб показывало либо все нули либо надпись "ОШИБКА" и значение ну к примеру 1000.
 
 
 
#8
09.02.2021 08:35:41
проблему решил спасибо)
Вот мои формулы если кому интересно) Есть только нюанс в диапазоне поиска даты 1000 строк должны быть заполнены любыми датами иначе не считает.
 
время
=ИНДЕКС('Сводные   таблицы'!$B:$D;Ч(ЕСЛИ(СЧЁТЕСЛИ(ИНДЕКС('Сводные   таблицы'!$B$2:$D$16;;ПОИСКПОЗ('Сводные таблицы'!$F$1;'Сводные   таблицы'!$B$1:$D$1;));"<>0");ИНДЕКС(АГРЕГАТ(15;6;СТРОКА('Сводные   таблицы'!$A$2:$A$16)/(ИНДЕКС('Сводные таблицы'!$B$2:$D$16;;ПОИСКПОЗ('Сводные   таблицы'!$F$1;'Сводные таблицы'!$B$1:$D$1;))<>0);СТРОКА('Сводные   таблицы'!$A$1:ИНДЕКС('Сводные таблицы'!$A$1:$A$16;СЧЁТЕСЛИ(ИНДЕКС('Сводные   таблицы'!$B$2:$D$16;;ПОИСКПОЗ('Сводные таблицы'!$F$1;'Сводные   таблицы'!$B$1:$D$1;));"<>0")))););0));ПОИСКПОЗ('Сводные   таблицы'!$F$1;'Сводные таблицы'!$B$1:$D$1;))
Случаи
=ИНДЕКС('Сводные   таблицы'!$B:$D;Ч(ЕСЛИ(СЧЁТЕСЛИ(ИНДЕКС('Сводные   таблицы'!$B$2:$D$16;;ПОИСКПОЗ('Сводные таблицы'!$F$17;'Сводные   таблицы'!$B$1:$D$1;));"<>0");ИНДЕКС(АГРЕГАТ(15;6;СТРОКА('Сводные   таблицы'!$A$2:$A$16)/(ИНДЕКС('Сводные таблицы'!$B$2:$D$16;;ПОИСКПОЗ('Сводные   таблицы'!$F$17;'Сводные таблицы'!$B$1:$D$1;))<>0);СТРОКА('Сводные   таблицы'!$A$1:ИНДЕКС('Сводные таблицы'!$A$1:$A$16;СЧЁТЕСЛИ(ИНДЕКС('Сводные   таблицы'!$B$2:$D$16;;ПОИСКПОЗ('Сводные таблицы'!$F$17;'Сводные таблицы'!$B$1:$D$1;));"<>0")))););0));ПОИСКПОЗ('Сводные   таблицы'!$F$17;'Сводные таблицы'!$B$1:$D$1;))
Потери
=ИНДЕКС('Сводные   таблицы'!$B:$D;Ч(ЕСЛИ(СЧЁТЕСЛИ(ИНДЕКС('Сводные   таблицы'!$B$2:$D$16;;ПОИСКПОЗ('Сводные таблицы'!$F$19;'Сводные   таблицы'!$B$1:$D$1;));"<>0");ИНДЕКС(АГРЕГАТ(15;6;СТРОКА('Сводные   таблицы'!$A$2:$A$16)/(ИНДЕКС('Сводные таблицы'!$B$2:$D$16;;ПОИСКПОЗ('Сводные   таблицы'!$F$19;'Сводные таблицы'!$B$1:$D$1;))<>0);СТРОКА('Сводные   таблицы'!$A$1:ИНДЕКС('Сводные таблицы'!$A$1:$A$16;СЧЁТЕСЛИ(ИНДЕКС('Сводные   таблицы'!$B$2:$D$16;;ПОИСКПОЗ('Сводные таблицы'!$F$19;'Сводные таблицы'!$B$1:$D$1;));"<>0")))););0));ПОИСКПОЗ('Сводные   таблицы'!$F$19;'Сводные таблицы'!$B$1:$D$1;))
Прооц
=ИНДЕКС('Сводные   таблицы'!$B:$E;Ч(ЕСЛИ(СЧЁТЕСЛИ(ИНДЕКС('Сводные   таблицы'!$B$2:$E$16;;ПОИСКПОЗ('Сводные таблицы'!$F$22;'Сводные   таблицы'!$B$1:$E$1;));"<>0");ИНДЕКС(АГРЕГАТ(15;6;СТРОКА('Сводные   таблицы'!$A$2:$A$16)/(ИНДЕКС('Сводные таблицы'!$B$2:$E$16;;ПОИСКПОЗ('Сводные   таблицы'!$F$22;'Сводные таблицы'!$B$1:$E$1;))<>0);СТРОКА('Сводные   таблицы'!$A$1:ИНДЕКС('Сводные таблицы'!$A$1:$A$16;СЧЁТЕСЛИ(ИНДЕКС('Сводные   таблицы'!$B$2:$E$16;;ПОИСКПОЗ('Сводные таблицы'!$F$22;'Сводные таблицы'!$B$1:$E$1;));"<>0")))););0));ПОИСКПОЗ('Сводные   таблицы'!$F$22;'Сводные таблицы'!$B$1:$E$1;))
имя
=ИНДЕКС('Сводные   таблицы'!$A:$A;Ч(ЕСЛИ(СЧЁТЕСЛИ(ИНДЕКС('Сводные   таблицы'!$B$2:$D$16;;ПОИСКПОЗ('Сводные таблицы'!$F$1;'Сводные   таблицы'!$B$1:$D$1;));"<>0");ИНДЕКС(АГРЕГАТ(15;6;СТРОКА('Сводные   таблицы'!$A$2:$A$16)/(ИНДЕКС('Сводные таблицы'!$B$2:$D16;;ПОИСКПОЗ('Сводные   таблицы'!$F$1;'Сводные таблицы'!$B$1:$D$1;))<>0);СТРОКА('Сводные   таблицы'!$A$1:ИНДЕКС('Сводные таблицы'!$A:$A;СЧЁТЕСЛИ(ИНДЕКС('Сводные   таблицы'!$B$2:$D$16;;ПОИСКПОЗ('Сводные таблицы'!$F$1;'Сводные   таблицы'!$B$1:$D$1;));"<>0")))););0)))
 
 
 
#9
09.02.2021 09:06:59
1.
Цитата
aastg2016 написал:
Вот мои формулы если кому интересно
есть у меня сомнения, что Ваши, скорее всего Лены
2 . без примера -этим формулам грощ цена
3. Смайлы совсем обесценивают формулы.
По вопросам из тем форума, личку не читаю.
 
 
 
#10
09.02.2021 11:48:00
да Лена помогла как и Вы. Я неприсваиваю залуги, просто написал что получилось у меня... Если кто будет читать тему. Или это тоже запрещено? или что непойму не так?)

Цитата
БМВ написал: 2 . без примера -этим формулам грощ цена
пример не помещается по размеру, отделить немогу, так как тогда это будет пустой файл с некоректными ссылками)
 
 
 
#11
09.02.2021 16:04:02
aastg2016, Вам каждый пункт правил объяснять?
Пример - это ПРИМЕР, а не Ваша рабочая книга. Можно же не отделять, а создать небольшую демонстрацию.
 
 
 
Страницы: 1
Наверх