Сводная таблица считает ячейки с формулами - как исключить?

Дата: Понедельник, 12.08.2019, 13:54 | Сообщение № 1

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 118

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

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

Excel 2016

Здравствуйте, ребят.

Подскажите, пожалуйста, возможно ли решить следующую проблему:

Есть таблица с исходными значениями, из которой мне нужно сделать сводную таблицу (Pivot table).

Проблема в том, что во всех ячейках тыблицы значения прописывается через формулы: при соблюдении определённого условия прописывается "о", в ином случае ничего не прописывается.

У меня не получается настроить сводную таблицу так, чтобы она не брала в учёт условно пустые ячейки.

Вот сама таблица с данными:

Вот свобдная таблица в том виде, какой она должна быть:

А вот свобдная таблица, которая получается в случае, если во все ячейки прописать формулы:

Возможно ли привести эту таблицу в такой вид, чтобы исключить все "красные" значения?

Прикладываю файл, в котором есть вся необходимая структура:

* таблица без формул + сводная таблица
* таблица с формулами + сводная таблица

Заранее большое спасибо за помощь!

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

Дата: Понедельник, 12.08.2019, 14:36 | Сообщение № 2

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

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

Сообщений: 16674

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

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

2003; 2007; 2010; 2013 RUS

Такой вариант
Формулу переписал. Кол в сводной поменял на сумму

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

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

Дата: Понедельник, 12.08.2019, 15:55 | Сообщение № 3

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 118

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

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

Excel 2016

Цитата _Boroda_, 12.08.2019 в 14:36, в сообщении № 2 ()

Такой вариант
Формулу переписал. Кол в сводной поменял на сумму

Этот вариант рабочий, но в действительно у меня формула немного сложнее:

Код

=IF($D15=H$7;"o";IF(COUNT(INDEX(1/(J15:P15>1);));"x";""))

По сути, из этого топика

Получается, что иногда у меня прописывается "о", иногда "х", а иногда ячейка остаётся пустой.
В сводной таблице мне необходимо считать количество ячеек как с "о", так и с "x", а счёт пустых ячеек мне не нужен.

Расскажите, пожалуйста, как работает ваша формула:

Код

=--(D2=B3)

Не совсем понятно, что такое =-- и как ячейка понимает, что в случае равенства в ячейку должен прописываться именно "о"?
ОК, про цвет фона ячейки я понял - там стоит УФ.

Большое спасибо!!

Сообщение отредактировал KIMVSR - Понедельник, 12.08.2019, 16:00

Дата: Понедельник, 12.08.2019, 16:06 | Сообщение № 4

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

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

Сообщений: 16674

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

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

2003; 2007; 2010; 2013 RUS

Про -- http://www.excelworld.ru/publ/hacks/tricks/17-1-0-9

Что Вы дальше делаете с этими о и х?
Приложите нормальный файл с рабочей формулой и нужным результатом (можно вручную)

Дата: Среда, 14.08.2019, 09:14 | Сообщение № 5

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 118

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

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

Excel 2016

Что Вы дальше делаете с этими о и х?
Приложите нормальный файл с рабочей формулой и нужным результатом (можно вручную)

Цитата _Boroda_, 12.08.2019 в 16:06, в сообщении № 4 ()

Прошу прощения, что не смог ответить сразу.

По сути, это база данных с работниками из разных отделов (JT**) и их умения в разных отделах.
Умения работника в своём отделе отмечаются в таблице как "о", а умения в чужом отделе - "х".
Таким образом мы имеем некий обзор ситуации умений и их уровня (шкала от 1 до 4) работников в различных отделах.
В дальнейшем по определённому алгоритму мы можем рассчитать бонусную плату сотдруникам (чем больше умений, тем выше коэффициент).

Сводная таблица, которую я хотел бы получить, выглядит таким образом:

Иными словами, мне нужно просто сосчитать все "о" и все "х" работников в разных отделах.
Красным цветом я подсветил умения в своём отделе ("о") и жёлтым - умения в чужом отделе ("х").

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

Дата: Среда, 14.08.2019, 10:05 | Сообщение № 6

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

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

Сообщений: 16674

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

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

2003; 2007; 2010; 2013 RUS

Так нужно?

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

Дата: Среда, 14.08.2019, 12:44 | Сообщение № 7

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 118

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

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

Excel 2016

Цитата _Boroda_, 14.08.2019 в 10:05, в сообщении № 6 ()

Так нужно?

Александр, это именно то, что нужно. Как вы этого добились?

Я вижу, что вы использовали Суммирование:

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

Дата: Среда, 14.08.2019, 13:33 | Сообщение № 8

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

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

Сообщений: 16674

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

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

2003; 2007; 2010; 2013 RUS

Формулы Вы в основном листе увидели вроде, если про минусы спрашиваете. Посмотрите там же и формат ячеек. И в листе со сводной тоже форматы посмотрите

Дата: Четверг, 15.08.2019, 15:32 | Сообщение № 9

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 118

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

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

Excel 2016

Цитата _Boroda_, 14.08.2019 в 13:33, в сообщении № 8 ()

Формулы Вы в основном листе увидели вроде, если про минусы спрашиваете. Посмотрите тамже и формат ячеек. И в листе со сводной тоже форматы посмотрите

Огромное спасибо за помощь, разобрался!
Не знал про 0;0 в формате ячеек и измение отрицательных значений на положительные...