С Excel знаком не очень глубоко. Хорошо знаю лишь базовый функционал.
Появилась у меня задача: хочу оптимизировать учет (приходится тратить кучу времени), для чего необходимо импортировать данные с соседнего листа. Но, для быстрого поиска данных, которые не были импортированы, необходимо на импортируемом листе окрашивать ячейки. Тогда визуально будет легко и быстро определить, что было упущено.
Суть такова: имеется некий список (товаров) из строк с несколькими значениями (количество на складах) в разных столбцах. Все данные заранее заполнены. На соседнем листе в строки вписываются названия (товаров и складов), а количество должно автоматически прописаться из первого листа. Те цифры, которые были импортированы на второй лист, должны окраситься для визуального контроля.
В файле-примере все наглядней и понятней
Понимаю, что необходимо условное форматирование и какое-то сравнение, но пока даже приблизительных идей нету.
Всем здравствуйте.
С Excel знаком не очень глубоко. Хорошо знаю лишь базовый функционал.
Появилась у меня задача: хочу оптимизировать учет (приходится тратить кучу времени), для чего необходимо импортировать данные с соседнего листа. Но, для быстрого поиска данных, которые не были импортированы, необходимо на импортируемом листе окрашивать ячейки. Тогда визуально будет легко и быстро определить, что было упущено.
Суть такова: имеется некий список (товаров) из строк с несколькими значениями (количество на складах) в разных столбцах. Все данные заранее заполнены. На соседнем листе в строки вписываются названия (товаров и складов), а количество должно автоматически прописаться из первого листа. Те цифры, которые были импортированы на второй лист, должны окраситься для визуального контроля.
В файле-примере все наглядней и понятней
Понимаю, что необходимо условное форматирование и какое-то сравнение, но пока даже приблизительных идей нету.l_Operator_l
Так нужно? Сделал Вам на лист3 выпадающие списки для выбора склада и товара, автоподстановку цифр по выбранным данным и автораскраску на Лист2 выбранных на Лист3 позиций.
Довложен файл _12 - что-то сбойнуло и приложился промежуточный файл.
Так нужно? Сделал Вам на лист3 выпадающие списки для выбора склада и товара, автоподстановку цифр по выбранным данным и автораскраску на Лист2 выбранных на Лист3 позиций.
Довложен файл _12 - что-то сбойнуло и приложился промежуточный файл._Boroda_
То, что нужно! Вот только вопрос: как это сделано? Формулу я еще кое-как портирую в свой рабочий документ (надеюсь), а вот с выпадающим меню у меня совсем все плохо(((
И с окрашиванием, как я понимаю, не получится?
То, что нужно! Вот только вопрос: как это сделано? Формулу я еще кое-как портирую в свой рабочий документ (надеюсь), а вот с выпадающим меню у меня совсем все плохо(((
И с окрашиванием, как я понимаю, не получится?l_Operator_l
С выпадающим списком, кажется, разобрался. Интернет - великая сила, все таки. Но Вы, _Boroda_, нашли весьма оригинальный и простой способ, о возможности которого я даже не догадывался. Ну и с формулой подстановки тоже - у меня пока нет таких знаний, чтобы сотворить нечто подобное. Спасибо большое!
Но остался вопрос с тем, как легко и быстро визуально контролировать позиции, которые уже перенесены с общего списка (Лист2) в выборки (Лист3). По идее там необходимо использовать какое-то сравнение, формулы логики. А для меня это пока еще дремучий лес, тайна, покрытая мраком...
Добавлено:
Хочу похвастаться: у меня, вроде бы, вышло портировать формулу в рабочий документ. Там все гораздо сложнее, нежели в моем примере, и выборки есть не только вертикально, а и горизонтально, и с первого разу не получилось. И мне все равно не понятно, как она работает (читал подсказки, напрягал мозг, но так и не понял). Можете объяснить "на пальцах", так сказать?
С выпадающим списком, кажется, разобрался. Интернет - великая сила, все таки. Но Вы, _Boroda_, нашли весьма оригинальный и простой способ, о возможности которого я даже не догадывался. Ну и с формулой подстановки тоже - у меня пока нет таких знаний, чтобы сотворить нечто подобное. Спасибо большое!
Но остался вопрос с тем, как легко и быстро визуально контролировать позиции, которые уже перенесены с общего списка (Лист2) в выборки (Лист3). По идее там необходимо использовать какое-то сравнение, формулы логики. А для меня это пока еще дремучий лес, тайна, покрытая мраком...
Добавлено:
Хочу похвастаться: у меня, вроде бы, вышло портировать формулу в рабочий документ. Там все гораздо сложнее, нежели в моем примере, и выборки есть не только вертикально, а и горизонтально, и с первого разу не получилось. И мне все равно не понятно, как она работает (читал подсказки, напрягал мозг, но так и не понял). Можете объяснить "на пальцах", так сказать?l_Operator_l
Сообщение отредактировал l_Operator_l - Понедельник, 08.09.2014, 22:50
Непонятна вся формула в ячейках C и D Листа3. Можете расписать примерно так (в плане бреда): ложь, если сумма А1:А10 не равна значению ячейки А2? И почему такая сложная формула у диапазона (имени) для выпадающего меню? В своем рабочем документе я просто выбрал диапазон, и все. Чем это чревато? Вот пример вашего имени Данные:
Код
=СМЕЩ(Лист2!$B$4;;;СЧЁТЗ(Товар);СЧЁТЗ(Склад)*2)
А вот моего:
Код
='Лист2'!$C$6:$N$81
Непонятна вся формула в ячейках C и D Листа3. Можете расписать примерно так (в плане бреда): ложь, если сумма А1:А10 не равна значению ячейки А2? И почему такая сложная формула у диапазона (имени) для выпадающего меню? В своем рабочем документе я просто выбрал диапазон, и все. Чем это чревато? Вот пример вашего имени Данные:
Что касается файлов, то я, если честно, разницы между ними не заметил. На обоих нет закрашивания И я так и не понял, как сделать УФ. Если просто задать закрашивать не пустые ячейки, то мне от такого толку мало, я и так вижу, где пусто, а где нет. Мне нужно, чтобы я видел, не пропустил ли я в выборках какого-то значения из общей таблицы (выбрано должно быть все и по одному разу). В идеале было бы, чтобы контролировалась и двойная выборка (когда одна и та же позиция выбрана дважды в одной или нескольких выборках). Но то уже из разряда фантастики, и мне, в принципе, достаточно будет и упрощенного, так сказать, варианта.
Что касается файлов, то я, если честно, разницы между ними не заметил. На обоих нет закрашивания И я так и не понял, как сделать УФ. Если просто задать закрашивать не пустые ячейки, то мне от такого толку мало, я и так вижу, где пусто, а где нет. Мне нужно, чтобы я видел, не пропустил ли я в выборках какого-то значения из общей таблицы (выбрано должно быть все и по одному разу). В идеале было бы, чтобы контролировалась и двойная выборка (когда одна и та же позиция выбрана дважды в одной или нескольких выборках). Но то уже из разряда фантастики, и мне, в принципе, достаточно будет и упрощенного, так сказать, варианта.l_Operator_l
В своем рабочем документе я просто выбрал диапазон, и все. Чем это чревато?
Тут все дело в том, что моя формула за счет кусков со СЧЁТЗ автоматически подстраивается под новые данные - можно вводить еще столбцы и строки. У Вас диапазон стационарный. Если его (диапазон) менять не будем, то вполне подойдет и Ваша формула (или можно просто заранее взять с запасом). Но, сами понимаете, есть риск когда-нибудь нарваться. формула ЕСЛИОШИБКА(ИНДЕКС(Данные;ПОИСКПОЗ($B4;Товар;);ПОИСКПОЗ($A4;Склад;)+(C$3=$D$3));"") можно переписать так ЕСЛИОШИБКА(ИНДЕКС(Лист2!B4:G6;ПОИСКПОЗ($B4;Лист2!A4:A6;);ПОИСКПОЗ($A4;Лист2!B2:G2;)+(C$3=$D$3));"") 1. ПОИСКПОЗ($B4;Лист2!A4:A6;) - ищет номер позиции (каким по счету) в диапазоне Лист2!A4:A6 находится значение из В4 2. ПОИСКПОЗ($A4;Лист2!B2:G2;) - аналогично п.1 3. C$3=$D$3 при значении "Доступно" в строке 3 над ячейкой с формулой дает нам ИСТИНА (то есть, единицу) 4. ПОИСКПОЗ($A4;Склад;)+(C$3=$D$3) если п.3 = ИСТИНА, то нам от п.2 нужно сместиться на одну ячейку вправо, а если п.3=ЛОЖЬ, то не нужно 5. ИНДЕКС(Лист2!B4:G6;п.1;п.4) в массиве Лист2!B4:G6 выбираем строку из п.1 и столбец из п.4 6. ЕСЛИОШИБКА(п.5;"") если п.5 дает нам ошибку, то показываем пустое значение ""
А закрашивание смотрите в файле в этом сообщении. И обычное, и повторных значений. Судя по всему, при сохранении в xls УФ слетало.
В своем рабочем документе я просто выбрал диапазон, и все. Чем это чревато?
Тут все дело в том, что моя формула за счет кусков со СЧЁТЗ автоматически подстраивается под новые данные - можно вводить еще столбцы и строки. У Вас диапазон стационарный. Если его (диапазон) менять не будем, то вполне подойдет и Ваша формула (или можно просто заранее взять с запасом). Но, сами понимаете, есть риск когда-нибудь нарваться. формула ЕСЛИОШИБКА(ИНДЕКС(Данные;ПОИСКПОЗ($B4;Товар;);ПОИСКПОЗ($A4;Склад;)+(C$3=$D$3));"") можно переписать так ЕСЛИОШИБКА(ИНДЕКС(Лист2!B4:G6;ПОИСКПОЗ($B4;Лист2!A4:A6;);ПОИСКПОЗ($A4;Лист2!B2:G2;)+(C$3=$D$3));"") 1. ПОИСКПОЗ($B4;Лист2!A4:A6;) - ищет номер позиции (каким по счету) в диапазоне Лист2!A4:A6 находится значение из В4 2. ПОИСКПОЗ($A4;Лист2!B2:G2;) - аналогично п.1 3. C$3=$D$3 при значении "Доступно" в строке 3 над ячейкой с формулой дает нам ИСТИНА (то есть, единицу) 4. ПОИСКПОЗ($A4;Склад;)+(C$3=$D$3) если п.3 = ИСТИНА, то нам от п.2 нужно сместиться на одну ячейку вправо, а если п.3=ЛОЖЬ, то не нужно 5. ИНДЕКС(Лист2!B4:G6;п.1;п.4) в массиве Лист2!B4:G6 выбираем строку из п.1 и столбец из п.4 6. ЕСЛИОШИБКА(п.5;"") если п.5 дает нам ошибку, то показываем пустое значение ""
А закрашивание смотрите в файле в этом сообщении. И обычное, и повторных значений. Судя по всему, при сохранении в xls УФ слетало._Boroda_
А что касается примера, то в нем УФ так и не работает(((
Дело в том, что Александр в формуле УФ использовал ссылки на другой лист, а Excel 2007 этого не понимает, но если использовать именованные диапазоны, то всё заработает
А что касается примера, то в нем УФ так и не работает(((
Дело в том, что Александр в формуле УФ использовал ссылки на другой лист, а Excel 2007 этого не понимает, но если использовать именованные диапазоны, то всё заработаетPelena
Pelena, Ваш пример у меня тоже не окрашивает ячейки((( Неужели Excel 2007 такой отставший от жизни? Если уж совсем никак, то таки найду дистрибутив и обновлюсь до версии 2013, но это не так легко и быстро, как хотелось бы + не на одном компьютере делать надо...
Pelena, Ваш пример у меня тоже не окрашивает ячейки((( Неужели Excel 2007 такой отставший от жизни? Если уж совсем никак, то таки найду дистрибутив и обновлюсь до версии 2013, но это не так легко и быстро, как хотелось бы + не на одном компьютере делать надо...l_Operator_l
Pelena, хм, вот специально скачал и открыл этот же файл на другом компьютере (с офисом из того же инсталлятора) и все красится. Чудеса. Спасибо еще раз. Буду вникать в механику действий
Pelena, хм, вот специально скачал и открыл этот же файл на другом компьютере (с офисом из того же инсталлятора) и все красится. Чудеса. Спасибо еще раз. Буду вникать в механику действийl_Operator_l
Pelena, а может быть проблема в том, что на компьютерах хоть и абсолютно одинаковый офис, но разные операционные системы или(и) настройки Excel? На рабочем компьютере (WinXP) снова открыл файл, а окрашивание не работает, хотя дома (Win7) работало... Хотя, думаю, причина таки не в ОС. В общем, куда копать, на что смотреть?
Pelena, а может быть проблема в том, что на компьютерах хоть и абсолютно одинаковый офис, но разные операционные системы или(и) настройки Excel? На рабочем компьютере (WinXP) снова открыл файл, а окрашивание не работает, хотя дома (Win7) работало... Хотя, думаю, причина таки не в ОС. В общем, куда копать, на что смотреть? l_Operator_l
Даже не знаю... Проверьте имена СтолбецА и СтолбецВ (Ctrl+F3), какие там диапазоны? Попробуйте заново создать правило: выделить диапазон $B$4:$G$6 -- Условное форматирование -- Создать правило -- Использовать формулу -- формулу скопируйте -- задайте формат -- ОК
Даже не знаю... Проверьте имена СтолбецА и СтолбецВ (Ctrl+F3), какие там диапазоны? Попробуйте заново создать правило: выделить диапазон $B$4:$G$6 -- Условное форматирование -- Создать правило -- Использовать формулу -- формулу скопируйте -- задайте формат -- ОКPelena
"Черт возьми, Холмс! Но как??!!" Ю-money 41001765434816
Не знаю почему, но на рабочем компьютере так и не заработало УФ((( Пускай хоть на домашнем будет. Но возникла новая проблема: когда пытаюсь применить формулу, отредактированную под мои параметры, Excel намертво виснет и помогает только снятие задачи. Делаю следующее: выделяю область C6:N17 - УФ - по формуле - задаю формат - вставляю формулу - ОК - висяк. Формула УФ следующая:
Что я делаю не так? Прикладываю упрощенную копию рабочего документа. Используются только листы 2 и 4.
Не знаю почему, но на рабочем компьютере так и не заработало УФ((( Пускай хоть на домашнем будет. Но возникла новая проблема: когда пытаюсь применить формулу, отредактированную под мои параметры, Excel намертво виснет и помогает только снятие задачи. Делаю следующее: выделяю область C6:N17 - УФ - по формуле - задаю формат - вставляю формулу - ОК - висяк. Формула УФ следующая:
Конечно. Зачем Вы в именованные диапазоны засунули весь столбец целиком? Держите. Поменял Вам только 2 диапазона Гр_1 и П_1. Остальное самостоятельно. Динамические диапазоны делать не стал, просто тупо до 999 строки. И формулу для УФ дополнил, чтобы пустые ячейки не красились
Конечно. Зачем Вы в именованные диапазоны засунули весь столбец целиком? Держите. Поменял Вам только 2 диапазона Гр_1 и П_1. Остальное самостоятельно. Динамические диапазоны делать не стал, просто тупо до 999 строки. И формулу для УФ дополнил, чтобы пустые ячейки не красились_Boroda_
Вход
Регистрация
Напомнить пароль
Автосумма
Часто используемые
ВПР
ЕСЛИ
СУММЕСЛИ
Финансовые
Логические
Текстовые
Даты и времени 
Ссылки и массивы 
Математические
Другие функции
Проверка свойств и значений
Баз данных
Математические
Обработки массивов
Обработки строк
Преобразования типов данных 
Загрузки данных
Работы с файлами
Обработки системных параметров
Обработки цвета
Работы с датами и временем
Преобразования в системы счисления
Финансовые
