усл.формат-е свод.таблиц по предельным значениям ячеек

Дата: Вторник, 20.03.2018, 14:25 | Сообщение № 1

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

Ранг: Новичок

Сообщений: 37

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

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

Excel 2013

Дорогие Excel-Волшебники,

Буду благодарна за помощь в решении следующей задачи, если оно, в принципе, возможно:

Дано:

1. Сводная таблица с показателями наличия 5 разных товаров в 10 разных помещениях.

2. Для каждого помещения определена норма минимального количества по каждому товару.

Задача:

Подсвечевать значения тех ячеек сводной таблицы, в которых наличие товара в определенном помещении падает ниже нормы.

Нормы индивидуальны для каждого помещения и товара, т.е. для всех отдельных ячеек сводной таблицы. Товары и помещения могут прибавляться и исчезать, т.е. вариант простого копирования в статичную таблицу не подходит. Если бы можно было в формуле ссылаться на определение значения ячейки сводной таблицы без привязки к месту ее появления (например, "Если значение/количество товара 1 помещения 1 <10" то - желтая подсветка) - проблема бы решилась...

Спасибо и всего доброго,
Елена.

Nic70y

Дата: Вторник, 20.03.2018, 14:28 | Сообщение № 2

Группа: Друзья

Ранг: Экселист

Сообщений: 8759

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

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

Excel 2010

файл приложите,
будет видно

ЮMoney 41001841029809

Дата: Вторник, 20.03.2018, 16:58 | Сообщение № 3

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

Ранг: Новичок

Сообщений: 37

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

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

Excel 2013

вот пожалуйста...

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

Дата: Вторник, 20.03.2018, 17:41 | Сообщение № 4

Группа: Друзья

Ранг: Участник клуба

Сообщений: 2566

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

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

Excel 2010

Добрый день.
т.к. в УФ нельзя использовать массивы констант (по крайней мере в 2010), пришлось создать два именованных списка (посмотреть можно в Диспетчере имен)
формула в УФ

Код

=B5<ПРОСМОТР(--(B$4&$A5);товар_помещение;порог)

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

Яндекс: 410016850021169

Дата: Вторник, 20.03.2018, 18:03 | Сообщение № 5

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

Ранг: Новичок

Сообщений: 37

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

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

Excel 2013

sboy,

а что так можно было? крутизна! спасибо!

я правильно понимаю - константы из "порогов" применяются последовательно к ряду сочетаний из "товар_помещение"?

Дата: Среда, 21.03.2018, 09:11 | Сообщение № 6

Группа: Друзья

Ранг: Участник клуба

Сообщений: 2566

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

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

Excel 2010

Цитата aivella, 20.03.2018 в 18:03, в сообщении № 5 ()

я правильно понимаю

вроде правильно)

Яндекс: 410016850021169

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

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

Ранг: Новичок

Сообщений: 37

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

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

Excel 2013

Спасибо!

еще такой вопрос по созданию массива. В предложенной Вами формуле
товар_помещение определяется как ={11;12;13;21;22;23;31;32;33}

а мне бы надо текстом - т.е. это я для упрощения товар 1 помещение 1 - в итоге 11 указала. На самом деле там что-то типа филиалЗенит красная краска.

попробовала использовать товар_помещение={"филиалЗенит""красная краска";"филиалЗенит""зеленая краска";и т.д.} не работает.

Может не тот синтаксис?

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

Группа: Друзья

Ранг: Участник клуба

Сообщений: 2566

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

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

Excel 2010

Цитата aivella, 21.03.2018 в 12:59, в сообщении № 7 ()

Может не тот синтаксис?

попробуйте без лишних кавычек товар_помещение={"филиалЗениткрасная краска";"филиалЗенитзеленая краска";...
если не поможет, то в файле покажите
добавлено: работать будет неправильно, в ПРОСМОТРЕ надо строго по возрастанию/алфавиту
сначала на листе отсортируйте по алфавиту

Яндекс: 410016850021169

Сообщение отредактировал sboy - Среда, 21.03.2018, 13:34

Дата: Среда, 21.03.2018, 13:42 | Сообщение № 9

Группа: Друзья

Ранг: Участник клуба

Сообщений: 2566

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

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

Excel 2010

Пособие как сделать список для массива констант
1. Выводим список на лист и сортируем по алфавиту
2. В ячейку B1 пишем формулу = диапазон списка (в примере А1:А4)

3. Встаем в строку формул и нажимаем F9

получаем массив констант, из строки формул копируем и вставляем уже в Диспетчер имен

К сообщению приложен файл: 2034066.jpg (37.1 Kb) · 5494742.jpg (24.1 Kb)

Яндекс: 410016850021169

Сообщение отредактировал sboy - Среда, 21.03.2018, 13:46

Дата: Среда, 21.03.2018, 15:25 | Сообщение № 10

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

Ранг: Новичок

Сообщений: 37

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

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

Excel 2013

sboy,

Супер! Спасибо огромное!

Дата: Пятница, 23.03.2018, 11:54 | Сообщение № 11

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

Ранг: Новичок

Сообщений: 37

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

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

Excel 2013

sboy,

очень надеюсь на Ваше понимание и поддержку еще раз. Моя таблица отказывается работать

Все рекомендованные Вами ходы я использовала, но она живет своей жизнью и не желает менять цвета.

буду очень благодарна, если посмотрите, что там не так...

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