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

Вход

Регистрация

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

 

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

Регистрация · Логин: · Пароль: · · Забыли пароль?
  • Страница 1 из 1
  • 1
Модератор форума: _Boroda_, Manyasha, SLAVICK, китин  
Мир 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
Группа: Друзья
Ранг: Участник клуба
Сообщений: 5190
Репутация: 1125 ±
Замечаний: 0% ±

Excel 2013
вдруг правильно
Код
=ЕСЛИОШИБКА(ИНДЕКС(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)


ЯД(poison) 41001841029809
+7 978 049 98 74 (мтс)
 
Ответить
Сообщениевдруг правильно
Код
=ЕСЛИОШИБКА(ИНДЕКС(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
Группа: Проверенные
Ранг: Ветеран
Сообщений: 955
Репутация: 233 ±
Замечаний: 0% ±

Excel 2010
И мой вариант (массивные):
Код
=ЕСЛИОШИБКА(ИНДЕКС(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
Группа: Модераторы
Ранг: Местный житель
Сообщений: 14517
Репутация: 5791 ±
Замечаний: 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
Группа: Проверенные
Ранг: Ветеран
Сообщений: 955
Репутация: 233 ±
Замечаний: 0% ±

Excel 2010
Зелёным независимые от столбца 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
Группа: Модераторы
Ранг: Местный житель
Сообщений: 14517
Репутация: 5791 ±
Замечаний: 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
Группа: Проверенные
Ранг: Ветеран
Сообщений: 955
Репутация: 233 ±
Замечаний: 0% ±

Excel 2010
Тему можно закрывать.
Немного причесал свои формулы. Чтобы старания не пропали даром, всё-таки выложу.
*Подправил формулы, файл перезалил.
**И для столбца 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
Группа: Проверенные
Ранг: Ветеран
Сообщений: 955
Репутация: 233 ±
Замечаний: 0% ±

Excel 2010
Хотел в МШ выложить, да раздумал. Массивная для столбца 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-2019 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!