Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/Вывод n значений, удовлетворяющих условию - Мир MS Excel

Регистрация · Логин: · Пароль: · · Забыли пароль?
Страница 1 из 11
Модератор форума: _Boroda_, Pelena, Manyasha, SLAVICK 
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Вывод n значений, удовлетворяющих условию (Формулы/Formulas)
Вывод n значений, удовлетворяющих условию
AlexKoul Дата: Вторник, 09.08.2016, 08:53 | Сообщение № 1
Группа: Пользователи
Ранг: Новичок
Сообщений: 12
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Добрый день, уважаемые форумчане!

Возникла такая проблема. В приложенном файле имеется Таблица 1. Колонок в реальности больше. Задача: в Таблицу 2 вывести первые 3 (n) самых дешевых товара. В строках товар может быть тот же самый (повторяться), только отличаться неким состоянием, как для карандаша заточенный/не заточенный. Цена при этом этого товара не меняется. Для каждой отобранной позиции необходимо ввести все соответствующие колонки. Пробовал разные варианты, но в моем случае не работает. Я понимаю почему не работает, так как в Таблице 2 во второй строке выводится не второй Карандаш, а выводится одна и та же строка, так как small ищет второе минимальное значение, а вот match выдает значение по первому значению, которое вернуло small.
Прошу помочь решить данную задачу. Как вывести корректно все соответствующие колонки? Если не затруднит, решение прошу сопроводить комментарием, чтобы понять логику решения. Заранее благодарю всех откликнувшихся!
К сообщению приложен файл: test1.xlsx(9Kb)
 
Ответить
СообщениеДобрый день, уважаемые форумчане!

Возникла такая проблема. В приложенном файле имеется Таблица 1. Колонок в реальности больше. Задача: в Таблицу 2 вывести первые 3 (n) самых дешевых товара. В строках товар может быть тот же самый (повторяться), только отличаться неким состоянием, как для карандаша заточенный/не заточенный. Цена при этом этого товара не меняется. Для каждой отобранной позиции необходимо ввести все соответствующие колонки. Пробовал разные варианты, но в моем случае не работает. Я понимаю почему не работает, так как в Таблице 2 во второй строке выводится не второй Карандаш, а выводится одна и та же строка, так как small ищет второе минимальное значение, а вот match выдает значение по первому значению, которое вернуло small.
Прошу помочь решить данную задачу. Как вывести корректно все соответствующие колонки? Если не затруднит, решение прошу сопроводить комментарием, чтобы понять логику решения. Заранее благодарю всех откликнувшихся!

Автор - AlexKoul
Дата добавления - 09.08.2016 в 08:53
pabchek Дата: Вторник, 09.08.2016, 09:21 | Сообщение № 2
Группа: Проверенные
Ранг: Ветеран
Сообщений: 691
Репутация: 152 ±
Замечаний: 0% ±

Excel 2007
Здравствуйте!
Так надо? Формула массива
Код
=ИНДЕКС(B$3:B$7;ОСТАТ(НАИМЕНЬШИЙ($D$3:$D$7+СТРОКА($A$1:$A$5)/10;$H3);1)*10)

Добавлено
Или если цены "нормальные", с двумя знаками после запятой
Код
=ИНДЕКС(B$3:B$7;ОСТАТ(НАИМЕНЬШИЙ($D$3:$D$7+СТРОКА($A$1:$A$5)/1000;$H3);0,01)*1000)
К сообщению приложен файл: 6273090.xlsx(9Kb)


"Учиться, учиться и еще раз учиться!"
WM: R399923528092


Сообщение отредактировал pabchek - Вторник, 09.08.2016, 09:45
 
Ответить
СообщениеЗдравствуйте!
Так надо? Формула массива
Код
=ИНДЕКС(B$3:B$7;ОСТАТ(НАИМЕНЬШИЙ($D$3:$D$7+СТРОКА($A$1:$A$5)/10;$H3);1)*10)

Добавлено
Или если цены "нормальные", с двумя знаками после запятой
Код
=ИНДЕКС(B$3:B$7;ОСТАТ(НАИМЕНЬШИЙ($D$3:$D$7+СТРОКА($A$1:$A$5)/1000;$H3);0,01)*1000)

Автор - pabchek
Дата добавления - 09.08.2016 в 09:21
Pelena Дата: Вторник, 09.08.2016, 09:22 | Сообщение № 3
Группа: Модераторы
Ранг: Местный житель
Сообщений: 11005
Репутация: 2457 ±
Замечаний: 0% ±

Excel 2010 & Mac Excel 2011
Здравствуйте.
Посмотрите такой вариант
Код
=ИНДЕКС($E$3:$E$7;НАИМЕНЬШИЙ(ЕСЛИ(НАИМЕНЬШИЙ($D$3:$D$7;$H3)=$D$3:$D$7;СТРОКА($D$3:$D$7)-2);СЧЁТЕСЛИ($I$3:$I3;$I3)))

и в столбце J формулу немного сократила
К сообщению приложен файл: 9343307.xlsx(9Kb)


"Черт возьми, Холмс! Но как??!!"
ЯД 41001765434816
 
Ответить
СообщениеЗдравствуйте.
Посмотрите такой вариант
Код
=ИНДЕКС($E$3:$E$7;НАИМЕНЬШИЙ(ЕСЛИ(НАИМЕНЬШИЙ($D$3:$D$7;$H3)=$D$3:$D$7;СТРОКА($D$3:$D$7)-2);СЧЁТЕСЛИ($I$3:$I3;$I3)))

и в столбце J формулу немного сократила

Автор - Pelena
Дата добавления - 09.08.2016 в 09:22
AlexKoul Дата: Вторник, 09.08.2016, 10:05 | Сообщение № 4
Группа: Пользователи
Ранг: Новичок
Сообщений: 12
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Спасибо, работают оба варианта!

Pelena, подскажите, пожалуйста, а что делает последняя конструкция в Вашем выражении СЧЁТЕСЛИ($I$3:$I3;$I3)? Не совсем понял что она делает и как ее сделать, чтобы можно было растянуть на всю таблицу?
 
Ответить
СообщениеСпасибо, работают оба варианта!

Pelena, подскажите, пожалуйста, а что делает последняя конструкция в Вашем выражении СЧЁТЕСЛИ($I$3:$I3;$I3)? Не совсем понял что она делает и как ее сделать, чтобы можно было растянуть на всю таблицу?

Автор - AlexKoul
Дата добавления - 09.08.2016 в 10:05
Pelena Дата: Вторник, 09.08.2016, 10:13 | Сообщение № 5
Группа: Модераторы
Ранг: Местный житель
Сообщений: 11005
Репутация: 2457 ±
Замечаний: 0% ±

Excel 2010 & Mac Excel 2011
Подсчитывает, сколько раз текущее наименование уже встречалось раньше.
Здесь только первая ячейка диапазона закреплена. А протягивается формула, как обычно


"Черт возьми, Холмс! Но как??!!"
ЯД 41001765434816
 
Ответить
СообщениеПодсчитывает, сколько раз текущее наименование уже встречалось раньше.
Здесь только первая ячейка диапазона закреплена. А протягивается формула, как обычно

Автор - Pelena
Дата добавления - 09.08.2016 в 10:13
AlexKoul Дата: Вторник, 09.08.2016, 11:28 | Сообщение № 6
Группа: Пользователи
Ранг: Новичок
Сообщений: 12
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Да, не обратил сразу внимание, что закреплено только первое значение.

По поводу формулы все равно не понял, в каком случае срабатывает этот счетчик? Разве условие НАИМЕНЬШИЙ($D$3:$D$7;$H3)=$D$3:$D$7 не всегда будет выдавать положительное значение?
 
Ответить
СообщениеДа, не обратил сразу внимание, что закреплено только первое значение.

По поводу формулы все равно не понял, в каком случае срабатывает этот счетчик? Разве условие НАИМЕНЬШИЙ($D$3:$D$7;$H3)=$D$3:$D$7 не всегда будет выдавать положительное значение?

Автор - AlexKoul
Дата добавления - 09.08.2016 в 11:28
Pelena Дата: Вторник, 09.08.2016, 11:41 | Сообщение № 7
Группа: Модераторы
Ранг: Местный житель
Сообщений: 11005
Репутация: 2457 ±
Замечаний: 0% ±

Excel 2010 & Mac Excel 2011
Если у Вас в таблице два карандаша, то фрагмент формулы
Код
ЕСЛИ(НАИМЕНЬШИЙ($D$3:$D$7;$H3)=$D$3:$D$7;СТРОКА($D$3:$D$7)-2)

вернёт массив
{1:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:5}
Это можно увидеть, выделив этот фрагмент в строке формул и нажав клавишу F9 (подробнее можно почитать в статье)
Для первого каранандаша мы должны взять значение позиции 1, а для второго 5. Эту возможность и даёт функция
Код
СЧЁТЕСЛИ($I$3:$I3;$I3)

[p.s.]В статье по ссылке подробно разобран похожий пример[/p.s.]


"Черт возьми, Холмс! Но как??!!"
ЯД 41001765434816
 
Ответить
СообщениеЕсли у Вас в таблице два карандаша, то фрагмент формулы
Код
ЕСЛИ(НАИМЕНЬШИЙ($D$3:$D$7;$H3)=$D$3:$D$7;СТРОКА($D$3:$D$7)-2)

вернёт массив
{1:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:5}
Это можно увидеть, выделив этот фрагмент в строке формул и нажав клавишу F9 (подробнее можно почитать в статье)
Для первого каранандаша мы должны взять значение позиции 1, а для второго 5. Эту возможность и даёт функция
Код
СЧЁТЕСЛИ($I$3:$I3;$I3)

[p.s.]В статье по ссылке подробно разобран похожий пример[/p.s.]

Автор - Pelena
Дата добавления - 09.08.2016 в 11:41
AlexKoul Дата: Вторник, 09.08.2016, 14:57 | Сообщение № 8
Группа: Пользователи
Ранг: Новичок
Сообщений: 12
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Спасибо за ответ!
Это-то понятно, каждую часть формулы я уже прогнал с помощью F9 просто недостаток этого решения в том, что формулы ссылаются на столбец I, который сформирован неправильно, а вот сформировать на примере Вашего решения столбец I корректно, у меня мозгов видимо маловато. Если в предложенном Вами решении цена ручки станет тоже 1, то мой список в столбце I вернет три раза одну и ту же строку "Карандаш", хотя массу вернет именно ручки. Вот и получается несоответствие между колонками итоговой таблицы.
 
Ответить
СообщениеСпасибо за ответ!
Это-то понятно, каждую часть формулы я уже прогнал с помощью F9 просто недостаток этого решения в том, что формулы ссылаются на столбец I, который сформирован неправильно, а вот сформировать на примере Вашего решения столбец I корректно, у меня мозгов видимо маловато. Если в предложенном Вами решении цена ручки станет тоже 1, то мой список в столбце I вернет три раза одну и ту же строку "Карандаш", хотя массу вернет именно ручки. Вот и получается несоответствие между колонками итоговой таблицы.

Автор - AlexKoul
Дата добавления - 09.08.2016 в 14:57
Pelena Дата: Вторник, 09.08.2016, 15:11 | Сообщение № 9
Группа: Модераторы
Ранг: Местный житель
Сообщений: 11005
Репутация: 2457 ±
Замечаний: 0% ±

Excel 2010 & Mac Excel 2011
Для столбца I можно так
Код
=ИНДЕКС($B$3:$B$7;НАИМЕНЬШИЙ(ЕСЛИ(НАИМЕНЬШИЙ($D$3:$D$7;$H3)=$D$3:$D$7;СТРОКА($D$3:$D$7)-2);СЧЁТЕСЛИ($J$3:J3;J3)))
К сообщению приложен файл: 6125299.xlsx(9Kb)


"Черт возьми, Холмс! Но как??!!"
ЯД 41001765434816
 
Ответить
СообщениеДля столбца I можно так
Код
=ИНДЕКС($B$3:$B$7;НАИМЕНЬШИЙ(ЕСЛИ(НАИМЕНЬШИЙ($D$3:$D$7;$H3)=$D$3:$D$7;СТРОКА($D$3:$D$7)-2);СЧЁТЕСЛИ($J$3:J3;J3)))

Автор - Pelena
Дата добавления - 09.08.2016 в 15:11
TimSha Дата: Вторник, 09.08.2016, 17:37 | Сообщение № 10
Группа: Проверенные
Ранг: Обитатель
Сообщений: 508
Репутация: 81 ±
Замечаний: 0% ±

Excel 2013 Pro +
Как вариант - расширенный фильтр... ;)
К сообщению приложен файл: ZXC_test1.xlsx(10Kb)


"Ctrl+S" - достойное завершение ваших гениальных мыслей!.. ;)
 
Ответить
СообщениеКак вариант - расширенный фильтр... ;)

Автор - TimSha
Дата добавления - 09.08.2016 в 17:37
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Вывод n значений, удовлетворяющих условию (Формулы/Formulas)
Страница 1 из 11
Поиск:

Яндекс цитирования
© 2010-2017 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!