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

Вход

Регистрация

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

 

= Мир MS Excel/Выбор данных из массива по двум значениям - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Выбор данных из массива по двум значениям (Формулы/Formulas)
Выбор данных из массива по двум значениям
alexialex Дата: Вторник, 18.12.2018, 20:54 | Сообщение № 1
Группа: Пользователи
Ранг: Прохожий
Сообщений: 7
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Подскажите, пожалуйста, как можно из массива выбрать несколько предметов по двум значениям
Пример Неуспевающие по предметам имеют "2" и "н\а" Необходимо написать эти предметы
Пример на Лист 2
С одним значением что-то получается(по крайней мере смог найти столбцы)
К сообщению приложен файл: 9770698.xlsx (40.1 Kb)
 
Ответить
СообщениеПодскажите, пожалуйста, как можно из массива выбрать несколько предметов по двум значениям
Пример Неуспевающие по предметам имеют "2" и "н\а" Необходимо написать эти предметы
Пример на Лист 2
С одним значением что-то получается(по крайней мере смог найти столбцы)

Автор - alexialex
Дата добавления - 18.12.2018 в 20:54
Nic70y Дата: Вторник, 18.12.2018, 22:03 | Сообщение № 2
Группа: Друзья
Ранг: Экселист
Сообщений: 8705
Репутация: 2260 ±
Замечаний: 0% ±

Excel 2010
вдруг правильно
Код
=ЕСЛИОШИБКА(ИНДЕКС(B1:B33;НАИМЕНЬШИЙ(ЕСЛИ((C5:W33=2)+(C5:W33="н\а");СТРОКА(C5:W33));СТРОКА()-75));"")
Код
=ЕСЛИ(D76<>"";ИНДЕКС(C$4:W$4;ПОИСКПОЗ("2";ПОДСТАВИТЬ(ИНДЕКС(C$5:W$33;ПОИСКПОЗ(D76;B$5:B$32;);ЕСЛИ(D76=D75;ПОИСКПОЗ(I75;C$4:W$4;)+1;)):ИНДЕКС(W$5:W$33;ПОИСКПОЗ(D76;B$5:B$32;));"н\а";2);)+ЕСЛИ(D76=D75;ПОИСКПОЗ(I75;C$4:W$4;);));"")
К сообщению приложен файл: 9770698-1-.xlsx (40.6 Kb)


ЮMoney 41001841029809
 
Ответить
Сообщениевдруг правильно
Код
=ЕСЛИОШИБКА(ИНДЕКС(B1:B33;НАИМЕНЬШИЙ(ЕСЛИ((C5:W33=2)+(C5:W33="н\а");СТРОКА(C5:W33));СТРОКА()-75));"")
Код
=ЕСЛИ(D76<>"";ИНДЕКС(C$4:W$4;ПОИСКПОЗ("2";ПОДСТАВИТЬ(ИНДЕКС(C$5:W$33;ПОИСКПОЗ(D76;B$5:B$32;);ЕСЛИ(D76=D75;ПОИСКПОЗ(I75;C$4:W$4;)+1;)):ИНДЕКС(W$5:W$33;ПОИСКПОЗ(D76;B$5:B$32;));"н\а";2);)+ЕСЛИ(D76=D75;ПОИСКПОЗ(I75;C$4:W$4;);));"")

Автор - Nic70y
Дата добавления - 18.12.2018 в 22:03
Светлый Дата: Вторник, 18.12.2018, 22:09 | Сообщение № 3
Группа: Друзья
Ранг: Старожил
Сообщений: 1823
Репутация: 507 ±
Замечаний: 0% ±

Excel 2013, 2016
И мой вариант (массивные):
Код
=ЕСЛИОШИБКА(ИНДЕКС(B:B;НАИМЕНЬШИЙ(ЕСЛИ(МУМНОЖ((C$5:W$31=2)+(C$5:W$31="н\а");ТРАНСП(СТОЛБЕЦ(C:W)^0))=1;СТРОКА($5:$31));СТРОКА(D1)));"")
Код
=ЕСЛИ(D76="";"";СМЕЩ(B$4;;ПОИСКПОЗ(1;(СМЕЩ(C$4:W$4;ПОИСКПОЗ(D76;B$5:B$31;);)=2)+(СМЕЩ(C$4:W$4;ПОИСКПОЗ(D76;B$5:B$31;);)="н\а");)))
К сообщению приложен файл: 9770698-1.xlsx (40.5 Kb)


Программировать проще, чем писать стихи.
 
Ответить
СообщениеИ мой вариант (массивные):
Код
=ЕСЛИОШИБКА(ИНДЕКС(B:B;НАИМЕНЬШИЙ(ЕСЛИ(МУМНОЖ((C$5:W$31=2)+(C$5:W$31="н\а");ТРАНСП(СТОЛБЕЦ(C:W)^0))=1;СТРОКА($5:$31));СТРОКА(D1)));"")
Код
=ЕСЛИ(D76="";"";СМЕЩ(B$4;;ПОИСКПОЗ(1;(СМЕЩ(C$4:W$4;ПОИСКПОЗ(D76;B$5:B$31;);)=2)+(СМЕЩ(C$4:W$4;ПОИСКПОЗ(D76;B$5:B$31;);)="н\а");)))

Автор - Светлый
Дата добавления - 18.12.2018 в 22:09
alexialex Дата: Вторник, 18.12.2018, 22:44 | Сообщение № 4
Группа: Пользователи
Ранг: Прохожий
Сообщений: 7
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Цитата Светлый, 18.12.2018 в 22:09, в сообщении № 3 ()
мой вариант (массивные):

Спасибо большое за более короткую формулу, но она не решает задачу для нескольких предметов(про которую и вопрос)
для S76,S77...
К сообщению приложен файл: 4591509.xlsx (40.7 Kb)
 
Ответить
Сообщение
Цитата Светлый, 18.12.2018 в 22:09, в сообщении № 3 ()
мой вариант (массивные):

Спасибо большое за более короткую формулу, но она не решает задачу для нескольких предметов(про которую и вопрос)
для S76,S77...

Автор - alexialex
Дата добавления - 18.12.2018 в 22:44
alexialex Дата: Вторник, 18.12.2018, 23:20 | Сообщение № 5
Группа: Пользователи
Ранг: Прохожий
Сообщений: 7
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
вдруг правильно

Спасибо! Ваша формула работает, но не там,где нужно
Я попытался ее переделать в нужном месте, но видимо,где-то запутался.
Выдает только один предмет, а должен несколько предметов в строчке
К сообщению приложен файл: 3091438.xlsx (42.8 Kb)


Сообщение отредактировал alexialex - Вторник, 18.12.2018, 23:22
 
Ответить
Сообщение
вдруг правильно

Спасибо! Ваша формула работает, но не там,где нужно
Я попытался ее переделать в нужном месте, но видимо,где-то запутался.
Выдает только один предмет, а должен несколько предметов в строчке

Автор - alexialex
Дата добавления - 18.12.2018 в 23:20
_Boroda_ Дата: Вторник, 18.12.2018, 23:27 | Сообщение № 6
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16666
Репутация: 6478 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Не совсем понял, так нужно?
Для "с одной"
ФИО, массивная
Код
=ЕСЛИОШИБКА(ИНДЕКС(B:B;НАИМЕНЬШИЙ(ЕСЛИ(C$76=X$5:X$33;СТРОКА($5:$33));СТРОКА(D1)));"")

Предмет, немассивная
Код
=ЕСЛИ(D76="";"";ИНДЕКС($4:$4;СУММПРОИЗВ(СТОЛБЕЦ(C:W)*(ИНДЕКС(C:W;ПОИСКПОЗ(D76;B$1:B$33;);)=C$77:C$78))))

Для нескольких
ФИО, массивная
Код
=ЕСЛИОШИБКА(ИНДЕКС(B:B;НАИМЕНЬШИЙ(ЕСЛИ(M$76=X$5:X$33;СТРОКА($5:$33));СТРОКА(D1)));"")

Кол-во, немассивная
Код
=ЕСЛИ(N76="";"";СУММПРОИЗВ(СЧЁТЕСЛИ(ИНДЕКС(C:W;ПОИСКПОЗ(N76;B$1:B$33;););M$77:M$78)))

Предметы, массивная
Код
=ЕСЛИОШИБКА(ИНДЕКС($4:$4;НАИМЕНЬШИЙ(ЕСЛИ(ИНДЕКС($C:$W;ПОИСКПОЗ($N76;$B$1:$B$33;);)=$M$77:$M$78;СТОЛБЕЦ($C:$W));СТОЛБЕЦ(A76)));"")


Ну и раскрасочку еще Условным форматированием сделал, чтобы не искать

*Не те формулы скопировал. Переписал и файл перевложил
К сообщению приложен файл: 4591509_2.xlsx (46.0 Kb)


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


Сообщение отредактировал _Boroda_ - Вторник, 18.12.2018, 23:39
 
Ответить
СообщениеНе совсем понял, так нужно?
Для "с одной"
ФИО, массивная
Код
=ЕСЛИОШИБКА(ИНДЕКС(B:B;НАИМЕНЬШИЙ(ЕСЛИ(C$76=X$5:X$33;СТРОКА($5:$33));СТРОКА(D1)));"")

Предмет, немассивная
Код
=ЕСЛИ(D76="";"";ИНДЕКС($4:$4;СУММПРОИЗВ(СТОЛБЕЦ(C:W)*(ИНДЕКС(C:W;ПОИСКПОЗ(D76;B$1:B$33;);)=C$77:C$78))))

Для нескольких
ФИО, массивная
Код
=ЕСЛИОШИБКА(ИНДЕКС(B:B;НАИМЕНЬШИЙ(ЕСЛИ(M$76=X$5:X$33;СТРОКА($5:$33));СТРОКА(D1)));"")

Кол-во, немассивная
Код
=ЕСЛИ(N76="";"";СУММПРОИЗВ(СЧЁТЕСЛИ(ИНДЕКС(C:W;ПОИСКПОЗ(N76;B$1:B$33;););M$77:M$78)))

Предметы, массивная
Код
=ЕСЛИОШИБКА(ИНДЕКС($4:$4;НАИМЕНЬШИЙ(ЕСЛИ(ИНДЕКС($C:$W;ПОИСКПОЗ($N76;$B$1:$B$33;);)=$M$77:$M$78;СТОЛБЕЦ($C:$W));СТОЛБЕЦ(A76)));"")


Ну и раскрасочку еще Условным форматированием сделал, чтобы не искать

*Не те формулы скопировал. Переписал и файл перевложил

Автор - _Boroda_
Дата добавления - 18.12.2018 в 23:27
alexialex Дата: Вторник, 18.12.2018, 23:34 | Сообщение № 7
Группа: Пользователи
Ранг: Прохожий
Сообщений: 7
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Предметы, массивная

Да! Огромное спасибо от классных руководителей, которым приходится заполнять данную ведомость.
Отдельно спасибо за более короткую формулу для одного предмета
ПЫ.СЫ Как поднять репутацию? При нажатии на + крутится в окошке кружок и ничего больше не происходит)))
Sorry, увидел всплывающее окошко - только через 24 часа смогу добавить плюсик...


Сообщение отредактировал alexialex - Вторник, 18.12.2018, 23:36
 
Ответить
Сообщение
Предметы, массивная

Да! Огромное спасибо от классных руководителей, которым приходится заполнять данную ведомость.
Отдельно спасибо за более короткую формулу для одного предмета
ПЫ.СЫ Как поднять репутацию? При нажатии на + крутится в окошке кружок и ничего больше не происходит)))
Sorry, увидел всплывающее окошко - только через 24 часа смогу добавить плюсик...

Автор - alexialex
Дата добавления - 18.12.2018 в 23:34
Светлый Дата: Вторник, 18.12.2018, 23:39 | Сообщение № 8
Группа: Друзья
Ранг: Старожил
Сообщений: 1823
Репутация: 507 ±
Замечаний: 0% ±

Excel 2013, 2016
Зелёным независимые от столбца X неоптимизированные формулы. Неуспевающие:
Код
=ЕСЛИОШИБКА(ИНДЕКС(B:B;НАИМЕНЬШИЙ(ЕСЛИ(МУМНОЖ((C$5:W$31=2)+(C$5:W$31="н\а");ТРАНСП(СТОЛБЕЦ(C:W)^0))>1;СТРОКА($5:$31));СТРОКА(D1)));"")
Количество предметов:
Код
=ЕСЛИ(N76="";"";СУММ((СМЕЩ($C$4:$W$4;ПОИСКПОЗ($N76;$B$5:$B$31;);)=2)+(СМЕЩ($C$4:$W$4;ПОИСКПОЗ($N76;$B$5:$B$31;);)="н\а")))
Сами предметы:
Код
=ЕСЛИОШИБКА(ЛЕВБ(ИНДЕКС($4:$4;НАИМЕНЬШИЙ(ЕСЛИ((СМЕЩ($C$4:$W$4;ПОИСКПОЗ($N76;$B$5:$B$31;);)=2)+(СМЕЩ($C$4:$W$4;ПОИСКПОЗ($N76;$B$5:$B$31;);)="н\а");СТОЛБЕЦ($C:$W));СТОЛБЕЦ(A76)));3);"")
К сообщению приложен файл: 9770698-2.xlsx (43.3 Kb)


Программировать проще, чем писать стихи.
 
Ответить
СообщениеЗелёным независимые от столбца X неоптимизированные формулы. Неуспевающие:
Код
=ЕСЛИОШИБКА(ИНДЕКС(B:B;НАИМЕНЬШИЙ(ЕСЛИ(МУМНОЖ((C$5:W$31=2)+(C$5:W$31="н\а");ТРАНСП(СТОЛБЕЦ(C:W)^0))>1;СТРОКА($5:$31));СТРОКА(D1)));"")
Количество предметов:
Код
=ЕСЛИ(N76="";"";СУММ((СМЕЩ($C$4:$W$4;ПОИСКПОЗ($N76;$B$5:$B$31;);)=2)+(СМЕЩ($C$4:$W$4;ПОИСКПОЗ($N76;$B$5:$B$31;);)="н\а")))
Сами предметы:
Код
=ЕСЛИОШИБКА(ЛЕВБ(ИНДЕКС($4:$4;НАИМЕНЬШИЙ(ЕСЛИ((СМЕЩ($C$4:$W$4;ПОИСКПОЗ($N76;$B$5:$B$31;);)=2)+(СМЕЩ($C$4:$W$4;ПОИСКПОЗ($N76;$B$5:$B$31;);)="н\а");СТОЛБЕЦ($C:$W));СТОЛБЕЦ(A76)));3);"")

Автор - Светлый
Дата добавления - 18.12.2018 в 23:39
_Boroda_ Дата: Вторник, 18.12.2018, 23:45 | Сообщение № 9
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16666
Репутация: 6478 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Переписал там немного
Как поднять репутацию?
Вы уже подняли, спасибо

*Я так понял, что на столбце Х в файле вообще почти всё завязано, поэтому в формулах для ФИО его и использовал.


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

*Я так понял, что на столбце Х в файле вообще почти всё завязано, поэтому в формулах для ФИО его и использовал.

Автор - _Boroda_
Дата добавления - 18.12.2018 в 23:45
alexialex Дата: Среда, 19.12.2018, 00:02 | Сообщение № 10
Группа: Пользователи
Ранг: Прохожий
Сообщений: 7
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Цитата Светлый, 18.12.2018 в 23:39, в сообщении № 8 ()
Зелёным независимые от столбца X

Конечно, ничего в этих формулах не понял). Единственное они будут работать без столбца Х.
Спасибо, будем разбираться....
 
Ответить
Сообщение
Цитата Светлый, 18.12.2018 в 23:39, в сообщении № 8 ()
Зелёным независимые от столбца X

Конечно, ничего в этих формулах не понял). Единственное они будут работать без столбца Х.
Спасибо, будем разбираться....

Автор - alexialex
Дата добавления - 19.12.2018 в 00:02
alexialex Дата: Среда, 19.12.2018, 00:59 | Сообщение № 11
Группа: Пользователи
Ранг: Прохожий
Сообщений: 7
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Всем большое спасибо!
Тему можно закрывать.
Конечный файл выкладываю.
Можно раздать классным руководителям.(Особенно, если защитить лист и не давать редактировать то, что не надо!)
К сообщению приложен файл: 7538046.xlsx (27.9 Kb)
 
Ответить
СообщениеВсем большое спасибо!
Тему можно закрывать.
Конечный файл выкладываю.
Можно раздать классным руководителям.(Особенно, если защитить лист и не давать редактировать то, что не надо!)

Автор - alexialex
Дата добавления - 19.12.2018 в 00:59
Светлый Дата: Среда, 19.12.2018, 07:22 | Сообщение № 12
Группа: Друзья
Ранг: Старожил
Сообщений: 1823
Репутация: 507 ±
Замечаний: 0% ±

Excel 2013, 2016
Тему можно закрывать.
Немного причесал свои формулы. Чтобы старания не пропали даром, всё-таки выложу.
*Подправил формулы, файл перезалил.
**И для столбца X формулу смастерил:
Код
=ЕСЛИ(B5>0;ИНДЕКС({"ну":"у":"хор":"отл"}&ЕСЛИ(МУМНОЖ(СЧЁТЕСЛИ(C5:W5;{2;"н\а":3;1:4;1:5;"а"});{1:1})=1;1;"");5-LOG(1+СУММ((МУМНОЖ(СЧЁТЕСЛИ(C5:W5;{2;"н\а":3;1:4;1:5;"а"});{1:1})>0)*{8:4:2:1});2));"")
***Ещё ужал (массивная):
Код
=ИНДЕКС({"":"отл":"хор":"у":"ну"}&ЕСЛИ(МУМНОЖ(СЧЁТЕСЛИ(C5:W5;{9;9:5;"а":4;9:3;9:2;"н\а"});{1:1})=1;1;"");LOG(СУММ(22^ЕСЛИ(C5:W5>0;7-ПОДСТАВИТЬ(ПОДСТАВИТЬ(C5:W5;"н\а";2);"а";5)));22))
К сообщению приложен файл: 9770698-3.xlsx (43.2 Kb)


Программировать проще, чем писать стихи.

Сообщение отредактировал Светлый - Среда, 19.12.2018, 21:57
 
Ответить
Сообщение
Тему можно закрывать.
Немного причесал свои формулы. Чтобы старания не пропали даром, всё-таки выложу.
*Подправил формулы, файл перезалил.
**И для столбца X формулу смастерил:
Код
=ЕСЛИ(B5>0;ИНДЕКС({"ну":"у":"хор":"отл"}&ЕСЛИ(МУМНОЖ(СЧЁТЕСЛИ(C5:W5;{2;"н\а":3;1:4;1:5;"а"});{1:1})=1;1;"");5-LOG(1+СУММ((МУМНОЖ(СЧЁТЕСЛИ(C5:W5;{2;"н\а":3;1:4;1:5;"а"});{1:1})>0)*{8:4:2:1});2));"")
***Ещё ужал (массивная):
Код
=ИНДЕКС({"":"отл":"хор":"у":"ну"}&ЕСЛИ(МУМНОЖ(СЧЁТЕСЛИ(C5:W5;{9;9:5;"а":4;9:3;9:2;"н\а"});{1:1})=1;1;"");LOG(СУММ(22^ЕСЛИ(C5:W5>0;7-ПОДСТАВИТЬ(ПОДСТАВИТЬ(C5:W5;"н\а";2);"а";5)));22))

Автор - Светлый
Дата добавления - 19.12.2018 в 07:22
Светлый Дата: Четверг, 20.12.2018, 23:44 | Сообщение № 13
Группа: Друзья
Ранг: Старожил
Сообщений: 1823
Репутация: 507 ±
Замечаний: 0% ±

Excel 2013, 2016
Хотел в МШ выложить, да раздумал. Массивная для столбца X:
Код
=ИНДЕКС({"":"отл":"хор":"у":"ну"}&ЕСЛИ(МУМНОЖ(СЧЁТЕСЛИ(C5:W5;{9;9:5;"а":4;9:3;9:2;"н\а"});{1:1})=1;1;"");LOG(СУММ(22^ОСТАТ(7-ОСТАТ(КОДСИМВ(C5:W5&6)-48;19);8));22))


Программировать проще, чем писать стихи.
 
Ответить
СообщениеХотел в МШ выложить, да раздумал. Массивная для столбца X:
Код
=ИНДЕКС({"":"отл":"хор":"у":"ну"}&ЕСЛИ(МУМНОЖ(СЧЁТЕСЛИ(C5:W5;{9;9:5;"а":4;9:3;9:2;"н\а"});{1:1})=1;1;"");LOG(СУММ(22^ОСТАТ(7-ОСТАТ(КОДСИМВ(C5:W5&6)-48;19);8));22))

Автор - Светлый
Дата добавления - 20.12.2018 в 23:44
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Выбор данных из массива по двум значениям (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

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