Я просмотрел примеры задач, которые тут разбирались, похожего на то, что нужно мне, не обнаружил. Задача такая: выбрать из списка те id, остатки которых>0 хотя бы в трех городах и продажи которых=0. Мне не нужно решение этой задачи, просто подскажите хотя бы, какую формулу использовать, с синтаксисом я бы сам попробовал разобраться. Спасибо. Прикрепил на всякий случай файл в формате .xls тоже.
Я просмотрел примеры задач, которые тут разбирались, похожего на то, что нужно мне, не обнаружил. Задача такая: выбрать из списка те id, остатки которых>0 хотя бы в трех городах и продажи которых=0. Мне не нужно решение этой задачи, просто подскажите хотя бы, какую формулу использовать, с синтаксисом я бы сам попробовал разобраться. Спасибо. Прикрепил на всякий случай файл в формате .xls тоже.blizzard
blizzard, я бы добавил к Вашему автофильтру дополнительную колонку, в которой сочинил бы критерий (формулу) с использованием функций СЧЁТЕСЛИ и СУММЕСЛИ.
blizzard, я бы добавил к Вашему автофильтру дополнительную колонку, в которой сочинил бы критерий (формулу) с использованием функций СЧЁТЕСЛИ и СУММЕСЛИ.Gustav
MCH, это там только часть данных, для примера. На самом деле, их там тысячи строк. Я тут додумался, что фильтром можно убрать, все строки, поле "остаток которых =0. Уже их меньше будет. А дальше вот как? про города? В приложенном примере и так уже видно, что не будет таких строк, которые бы попадали в требования, но это неважно.
MCH, это там только часть данных, для примера. На самом деле, их там тысячи строк. Я тут додумался, что фильтром можно убрать, все строки, поле "остаток которых =0. Уже их меньше будет. А дальше вот как? про города? В приложенном примере и так уже видно, что не будет таких строк, которые бы попадали в требования, но это неважно.blizzard
MCH, я изначально неправильно условия задачи написал. Вот так правильно будет: выбрать из списка те id, остатки которых>0 хотя бы в трех городах и продажи которых=0
MCH, я изначально неправильно условия задачи написал. Вот так правильно будет: выбрать из списка те id, остатки которых>0 хотя бы в трех городах и продажи которых=0blizzard
_Boroda_, MCH, спасибо. _Boroda_, не совсем понял, что делает формула =(СУММПРОИЗВ((A$2:A$50=A2)*(E$2:E$50>0))>2)*НЕ(СУММЕСЛИ(A$2:A$50;A2;C$2:C$50)) в крайнем правом столбце MCH, как ты сделал сводную таблицу?
_Boroda_, MCH, спасибо. _Boroda_, не совсем понял, что делает формула =(СУММПРОИЗВ((A$2:A$50=A2)*(E$2:E$50>0))>2)*НЕ(СУММЕСЛИ(A$2:A$50;A2;C$2:C$50)) в крайнем правом столбце MCH, как ты сделал сводную таблицу?blizzard
Нет. Согласно Вашему условию, нужно выбрать ВСЕ id, у которых
Quote
выбрать из списка те id, остатки которых>0 хотя бы в трех городах и продажи которых=0
Получается, что если даже у этой строки остаток=0, но у других строк с этим id в 3-х городах есть остатки и у ВСЕХ строк по этому id нет продаж, то эту строку нужно выводить.
Вот если бы Вы написали примерно так: выбрать из списка те строки, остатки Id которых>0, остатки Id которых>0 хотя бы в трех городах и продажи которых=0 тогда да тогда формула такая =(СУММПРОИЗВ((A$2:A$50=A2)*(E$2:E$50>0))>2)*НЕ(СУММЕСЛИ(A$2:A$50;A2;C$2:C$50))*(E2>0)
Объяснялка =(СУММПРОИЗВ((A$2:A$50=A2)*(E$2:E$50>0))>2)*НЕ(СУММЕСЛИ(A$2:A$50;A2;C$2:C$50))*(E2>0) 1) A$2:A$50=A2 - в случае, если id из массива A$2:A$50 равен id в А2, то ИСТИНА, иначе - ЛОЖЬ (дает массив из ИСТИНА и ЛОЖЬ) 2) E$2:E$50>0 - остаток должен быть больше 0 (дает массив из ИСТИНА и ЛОЖЬ) 3) перемножив 1) и 2), получаем ИСТИНА в случае, когда и id совпадает с id в А2, и остаток больше 0 (получаем массив из 1 и 0 - почему не ИСТИНА и ЛОЖЬ? - потому, что при выполнении арифметических действий ИСТИНА преобразуется в 1, а ЛОЖЬ - в 0) 4) с помощью СУММПРОИЗВ складываем всю кучу единиц и нулей из п. 3, получаем количество таких строк, в которых id совпадает с id в А2, и остаток больше 0 5) пишем, что п. 4 должен быть больше 2, получаем ИСТИНА или ЛОЖЬ 6) СУММЕСЛИ(A$2:A$50;A2;C$2:C$50) дает нам сумму продаж для id, номер которого в А2 - получаем или 0, или какое-либо число 7) функция НЕ преобразует ИСТИНА в ЛОЖЬ и наоборот. Excel считает, что 0 - это ЛОЖЬ, а любое другое число - это ИСТИНА, следовательно, НЕ(п.6) даст нам ЛОЖЬ в случае, когда в п.6 мы получаем какое-либо число и ИСТИНА в случае, когда в п.6 мы имеем 0 8) E2>0 - дает ИСТИНА, если остаток в этой строке больше 0 или ЛОЖЬ в обратном случае 9) перемножаем п.п. 5; 7; 8, получаем единицу только в том случае, когда все три пункта дают нам ИСТИНА
Нет. Согласно Вашему условию, нужно выбрать ВСЕ id, у которых
Quote
выбрать из списка те id, остатки которых>0 хотя бы в трех городах и продажи которых=0
Получается, что если даже у этой строки остаток=0, но у других строк с этим id в 3-х городах есть остатки и у ВСЕХ строк по этому id нет продаж, то эту строку нужно выводить.
Вот если бы Вы написали примерно так: выбрать из списка те строки, остатки Id которых>0, остатки Id которых>0 хотя бы в трех городах и продажи которых=0 тогда да тогда формула такая =(СУММПРОИЗВ((A$2:A$50=A2)*(E$2:E$50>0))>2)*НЕ(СУММЕСЛИ(A$2:A$50;A2;C$2:C$50))*(E2>0)
Объяснялка =(СУММПРОИЗВ((A$2:A$50=A2)*(E$2:E$50>0))>2)*НЕ(СУММЕСЛИ(A$2:A$50;A2;C$2:C$50))*(E2>0) 1) A$2:A$50=A2 - в случае, если id из массива A$2:A$50 равен id в А2, то ИСТИНА, иначе - ЛОЖЬ (дает массив из ИСТИНА и ЛОЖЬ) 2) E$2:E$50>0 - остаток должен быть больше 0 (дает массив из ИСТИНА и ЛОЖЬ) 3) перемножив 1) и 2), получаем ИСТИНА в случае, когда и id совпадает с id в А2, и остаток больше 0 (получаем массив из 1 и 0 - почему не ИСТИНА и ЛОЖЬ? - потому, что при выполнении арифметических действий ИСТИНА преобразуется в 1, а ЛОЖЬ - в 0) 4) с помощью СУММПРОИЗВ складываем всю кучу единиц и нулей из п. 3, получаем количество таких строк, в которых id совпадает с id в А2, и остаток больше 0 5) пишем, что п. 4 должен быть больше 2, получаем ИСТИНА или ЛОЖЬ 6) СУММЕСЛИ(A$2:A$50;A2;C$2:C$50) дает нам сумму продаж для id, номер которого в А2 - получаем или 0, или какое-либо число 7) функция НЕ преобразует ИСТИНА в ЛОЖЬ и наоборот. Excel считает, что 0 - это ЛОЖЬ, а любое другое число - это ИСТИНА, следовательно, НЕ(п.6) даст нам ЛОЖЬ в случае, когда в п.6 мы получаем какое-либо число и ИСТИНА в случае, когда в п.6 мы имеем 0 8) E2>0 - дает ИСТИНА, если остаток в этой строке больше 0 или ЛОЖЬ в обратном случае 9) перемножаем п.п. 5; 7; 8, получаем единицу только в том случае, когда все три пункта дают нам ИСТИНА_Boroda_
_Boroda_, пытаюсь увидеть логику в синтаксисе функции из столбца "плохие":там вижу требование, что E$2:E$50>0 - это понятно, про остатки больше нуля. А где там упоминается сочетание трех любых городов? Я посмотрел для чего используется формула "СУММПРОИЗВ". Она для перемножения массивов. Не понял, как с помощью нее можно фильтровать строки по нужным требованиям.
_Boroda_, пытаюсь увидеть логику в синтаксисе функции из столбца "плохие":там вижу требование, что E$2:E$50>0 - это понятно, про остатки больше нуля. А где там упоминается сочетание трех любых городов? Я посмотрел для чего используется формула "СУММПРОИЗВ". Она для перемножения массивов. Не понял, как с помощью нее можно фильтровать строки по нужным требованиям.blizzard
Сообщение отредактировал blizzard - Пятница, 15.06.2012, 16:33
blizzard, логические выражения в арифметических операциях дают либо 1 (истина), либо 0 (ложь). Теперь взгляните по-другому на (A$2:A$50=A2) - это вектор (одномерный вертикальный массив), состоящий из нулей и единиц. (E$2:E$50>0) - это аналогичный вектор. Попарное перемножение в результрующем векторе даёт 1 только в случае 1*1. И сумма этих попарных умножений должна (согласно формуле) быть > 2, т.е. не менее трёх. Теперь посмотрите на остальные фрагменты формулы в том же духе, с такой же точки зрения.
blizzard, логические выражения в арифметических операциях дают либо 1 (истина), либо 0 (ложь). Теперь взгляните по-другому на (A$2:A$50=A2) - это вектор (одномерный вертикальный массив), состоящий из нулей и единиц. (E$2:E$50>0) - это аналогичный вектор. Попарное перемножение в результрующем векторе даёт 1 только в случае 1*1. И сумма этих попарных умножений должна (согласно формуле) быть > 2, т.е. не менее трёх. Теперь посмотрите на остальные фрагменты формулы в том же духе, с такой же точки зрения.Gustav
подскажите теперь, как в таблице убрать все неуникальные значения? Пробовал делать расширенный фильтр с галкой уникальные значения. Там выделяю мою таблицу в "исходный диапазон" потом столбец "item_id" в "диапазон условий" ну и место куда сделать мне эту новую табличку без повторяющихся id. Пишет "неверная ссылка" в правилах написано, что одна тема-один вопрос, но это как бы продолжение начальной моей проблемы, поэтому не стал новую тему создавать.
подскажите теперь, как в таблице убрать все неуникальные значения? Пробовал делать расширенный фильтр с галкой уникальные значения. Там выделяю мою таблицу в "исходный диапазон" потом столбец "item_id" в "диапазон условий" ну и место куда сделать мне эту новую табличку без повторяющихся id. Пишет "неверная ссылка" в правилах написано, что одна тема-один вопрос, но это как бы продолжение начальной моей проблемы, поэтому не стал новую тему создавать.blizzard
blizzard, надо указать диапазон условий с одним этим столбцом item_id и пустым значением (у меня I1:I2). И "другое место" тоже с одним этим столбцом. Заголовочную жёлтую ячейку "item_id" Вы должны в каждом диапазоне заранее прописать сами.
blizzard, надо указать диапазон условий с одним этим столбцом item_id и пустым значением (у меня I1:I2). И "другое место" тоже с одним этим столбцом. Заголовочную жёлтую ячейку "item_id" Вы должны в каждом диапазоне заранее прописать сами.Gustav
Gustav, так я уже сделал. Но мне надо не только список этих уникальных id, но и все остальные привязанные к ним поля, понимаешь? Т.е. цена, остаток, город. А так только столбец id остается.
Gustav, так я уже сделал. Но мне надо не только список этих уникальных id, но и все остальные привязанные к ним поля, понимаешь? Т.е. цена, остаток, город. А так только столбец id остается.blizzard
Но мне надо не только список этих уникальных id, но и все остальные привязанные к ним поля, понимаешь? Т.е. цена, остаток, город. А так только столбец id остается.
А смысл какой? Ну и получите обратно исходные свои 99 строк... Чисто технически надо в заголовках диапазона условий и выборки указать эти 4 поля. Но только что это даст?
Вот например для id = 669409 какой-бы из городов хотелось увидеть? А остаток какой из них? Или Вы как себе представляете уникальность по 4 полям?
Нарисуйте вручную примерную табличку того, что хотелось бы увидеть.
Quote (blizzard)
Но мне надо не только список этих уникальных id, но и все остальные привязанные к ним поля, понимаешь? Т.е. цена, остаток, город. А так только столбец id остается.
А смысл какой? Ну и получите обратно исходные свои 99 строк... Чисто технически надо в заголовках диапазона условий и выборки указать эти 4 поля. Но только что это даст?
Вот например для id = 669409 какой-бы из городов хотелось увидеть? А остаток какой из них? Или Вы как себе представляете уникальность по 4 полям?
Нарисуйте вручную примерную табличку того, что хотелось бы увидеть.Gustav
Вход
Регистрация
Напомнить пароль
Автосумма
Часто используемые
ВПР
ЕСЛИ
СУММЕСЛИ
Финансовые
Логические
Текстовые
Даты и времени 
Ссылки и массивы 
Математические
Другие функции
Проверка свойств и значений
Баз данных
Математические
Обработки массивов
Обработки строк
Преобразования типов данных 
Загрузки данных
Работы с файлами
Обработки системных параметров
Обработки цвета
Работы с датами и временем
Преобразования в системы счисления
Финансовые
Чисто технически надо в заголовках диапазона условий и выборки указать эти 4 поля. Но только что это даст? 
