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

Вход

Регистрация

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

 

= Мир MS Excel/Отбор по критериям из таблицы в таблицу, без массива - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: _Boroda_, китин  
Мир MS Excel » Вопросы и решения » Excel и другие приложения » Google Docs » Отбор по критериям из таблицы в таблицу, без массива (Формулы/Formulas)
Отбор по критериям из таблицы в таблицу, без массива
nordri Дата: Воскресенье, 12.06.2022, 12:22 | Сообщение № 1
Группа: Пользователи
Ранг: Новичок
Сообщений: 25
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Приветствую уважаемые Форумчане!

У меня имеется лист, сроки в который добавляет скрипт... На самом деле это лист на Гугл таблицах, данные в который попадают из формы.

ЛИСТ ЗАЯВКА
имя    баллы класс предмет
   
петя    3    5б    ааа
вася    5    5а    ббб
дима    5    5в    ввв
коля    4    5б    ггг
саша    5    5а    ддд

На другой лист Результат данные с листа Заявка попадают с использованием формулы =СМЕЩ('Заявка'!A1;0;0;1000;15).
В Гугл.Таблицах это работает немного иначе, нежели в Экселе, но очень хорошо (достаточно ввести эту формулу в одну ячейку, и Гуглъ заполнит значениями новый лист сам, без протягивания).

Но мне требуется добавлять на новый лист (Результат) данные с отбором. В общем то я могу и на новом листе еще таблицу с отбором создать...
В данном случае критерий отбора указан на листе РЕЗУЛЬТАТ в ячейке (желтая в примере).


ЛИСТ РЕЗУЛЬТАТ

Отбор с листа заявка    балл    5   
    отметка    Отлично   
     
номер    имя    баллы    класс    отметка    предмет
     
1    вася    5    5а    Отлично    ббб
2    дима    5    5в    Отлично    ввв
3    саша    5    5а    Отлично    ддд
    

И во втором отборе мне нужно найти последнее вхождение уже отобранных результатов (второй отбор) на листе Результат, который опять же указывается в ячейке на этом листе (коричневая в примере).
и в добавок видимо Массив с использованием клавиш CTRL+SHIFT+ENTER, не вариант… Придется растягивать, защищая $ нижнюю границу массива D7:D$11...
Не понимаю, на сколько это хорошо.

Второй обор

    Отбор    5а    класс    
    
номер отбор    
    баллы    имя    отметка    предмет
    
1    5    вася    Отлично    ббб
2    5    саша    Отлично    ддд
    
К сообщению приложен файл: __-_--.xlsx(22.3 Kb)


Сообщение отредактировал nordri - Воскресенье, 12.06.2022, 12:23
 
Ответить
СообщениеПриветствую уважаемые Форумчане!

У меня имеется лист, сроки в который добавляет скрипт... На самом деле это лист на Гугл таблицах, данные в который попадают из формы.

ЛИСТ ЗАЯВКА
имя    баллы класс предмет
   
петя    3    5б    ааа
вася    5    5а    ббб
дима    5    5в    ввв
коля    4    5б    ггг
саша    5    5а    ддд

На другой лист Результат данные с листа Заявка попадают с использованием формулы =СМЕЩ('Заявка'!A1;0;0;1000;15).
В Гугл.Таблицах это работает немного иначе, нежели в Экселе, но очень хорошо (достаточно ввести эту формулу в одну ячейку, и Гуглъ заполнит значениями новый лист сам, без протягивания).

Но мне требуется добавлять на новый лист (Результат) данные с отбором. В общем то я могу и на новом листе еще таблицу с отбором создать...
В данном случае критерий отбора указан на листе РЕЗУЛЬТАТ в ячейке (желтая в примере).


ЛИСТ РЕЗУЛЬТАТ

Отбор с листа заявка    балл    5   
    отметка    Отлично   
     
номер    имя    баллы    класс    отметка    предмет
     
1    вася    5    5а    Отлично    ббб
2    дима    5    5в    Отлично    ввв
3    саша    5    5а    Отлично    ддд
    

И во втором отборе мне нужно найти последнее вхождение уже отобранных результатов (второй отбор) на листе Результат, который опять же указывается в ячейке на этом листе (коричневая в примере).
и в добавок видимо Массив с использованием клавиш CTRL+SHIFT+ENTER, не вариант… Придется растягивать, защищая $ нижнюю границу массива D7:D$11...
Не понимаю, на сколько это хорошо.

Второй обор

    Отбор    5а    класс    
    
номер отбор    
    баллы    имя    отметка    предмет
    
1    5    вася    Отлично    ббб
2    5    саша    Отлично    ддд
    

Автор - nordri
Дата добавления - 12.06.2022 в 12:22
Gustav Дата: Воскресенье, 12.06.2022, 14:50 | Сообщение № 2
Группа: Друзья
Ранг: Старожил
Сообщений: 2079
Репутация: 828 ±
Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1
Раз вопрос явно по Гугл Таблицам, надо было сразу в соответствующий раздел Форума поместить: http://www.excelworld.ru/forum/23 (можете попросить модераторов перенести тему туда).
И табличку в этом случае лучше "прикладывать" в виде ссылки на вашу тестовую Гугл Таблицу (расшареную копию рабочей таблицы без конфиденциалки).

В качестве примерного ответа предлагаю такие Гугл-формулы (с дальнейшим "причесыванием" по месту типа добавления колонки "Отметка" или перестановки других колонок):
для ячейки A7 листа "Результат":
[vba]
Код
={SEQUENCE(COUNTIFS('Заявка'!B3:B;$C$2);1)\FILTER('Заявка'!A3:D;'Заявка'!B3:B=$C$2)}
[/vba]
для ячейки H7 листа "Результат":
[vba]
Код
=ArrayFormula(VLOOKUP(QUERY(FILTER(A7:E;D7:D=$J$2);"select max(Col1) group by Col2 order by max(Col1) label max(Col1) ''";);FILTER(A7:E;D7:D=$J$2);{1\2\3\4\5};0))
[/vba]


Мой tip box - яд 41001663842605
 
Ответить
СообщениеРаз вопрос явно по Гугл Таблицам, надо было сразу в соответствующий раздел Форума поместить: http://www.excelworld.ru/forum/23 (можете попросить модераторов перенести тему туда).
И табличку в этом случае лучше "прикладывать" в виде ссылки на вашу тестовую Гугл Таблицу (расшареную копию рабочей таблицы без конфиденциалки).

В качестве примерного ответа предлагаю такие Гугл-формулы (с дальнейшим "причесыванием" по месту типа добавления колонки "Отметка" или перестановки других колонок):
для ячейки A7 листа "Результат":
[vba]
Код
={SEQUENCE(COUNTIFS('Заявка'!B3:B;$C$2);1)\FILTER('Заявка'!A3:D;'Заявка'!B3:B=$C$2)}
[/vba]
для ячейки H7 листа "Результат":
[vba]
Код
=ArrayFormula(VLOOKUP(QUERY(FILTER(A7:E;D7:D=$J$2);"select max(Col1) group by Col2 order by max(Col1) label max(Col1) ''";);FILTER(A7:E;D7:D=$J$2);{1\2\3\4\5};0))
[/vba]

Автор - Gustav
Дата добавления - 12.06.2022 в 14:50
nordri Дата: Воскресенье, 12.06.2022, 19:58 | Сообщение № 3
Группа: Пользователи
Ранг: Новичок
Сообщений: 25
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Раз вопрос явно по Гугл Таблицам, надо было сразу в соответствующий раздел Форума поместить

Дело в том, что в Гугл Таблицы я загружаю книгу с ПК... Как правило для моих целей этого пока было достаточно.
И Exel (2016) пока этого у меня не понимает...
для ячейки A7 листа "Результат":
={SEQUENCE(COUNTIFS('Заявка'!B3:B;$C$2);1)\FILTER('Заявка'!A3:D;'Заявка'!B3:B=$C$2)}


Как следствие, я ничего не могу с этим сделать, в добавок тема теперь в неверном (по моему мнению) разделе :'(
Хотя кажется, в Гугл Таблицах работает!

Возможно следует уточнить, что основная работа ведется на ПК, а Гугул Таблицы, только собирают данные...

Вот какой у меня выходит результат, соответственно я могу с ним работать, только в другой (четвертой) таблице

ЛИСТ РЕЗУЛЬТАТ (Отбор с листа Заявка)
1 вася 5 5а ббб
2 дима 5 5в ввв
3 саша 5 5а ддд

А: Второй отбор

1 вася 5 5а Отлично
3 саша 5 5а Отлично

Строки 1 3
Для этого я и писал
Код
=ЕСЛИ(J7="";"";МАКС($H$6:$H6)+1)


Сообщение отредактировал nordri - Воскресенье, 12.06.2022, 20:24
 
Ответить
Сообщение
Раз вопрос явно по Гугл Таблицам, надо было сразу в соответствующий раздел Форума поместить

Дело в том, что в Гугл Таблицы я загружаю книгу с ПК... Как правило для моих целей этого пока было достаточно.
И Exel (2016) пока этого у меня не понимает...
для ячейки A7 листа "Результат":
={SEQUENCE(COUNTIFS('Заявка'!B3:B;$C$2);1)\FILTER('Заявка'!A3:D;'Заявка'!B3:B=$C$2)}


Как следствие, я ничего не могу с этим сделать, в добавок тема теперь в неверном (по моему мнению) разделе :'(
Хотя кажется, в Гугл Таблицах работает!

Возможно следует уточнить, что основная работа ведется на ПК, а Гугул Таблицы, только собирают данные...

Вот какой у меня выходит результат, соответственно я могу с ним работать, только в другой (четвертой) таблице

ЛИСТ РЕЗУЛЬТАТ (Отбор с листа Заявка)
1 вася 5 5а ббб
2 дима 5 5в ввв
3 саша 5 5а ддд

А: Второй отбор

1 вася 5 5а Отлично
3 саша 5 5а Отлично

Строки 1 3
Для этого я и писал
Код
=ЕСЛИ(J7="";"";МАКС($H$6:$H6)+1)

Автор - nordri
Дата добавления - 12.06.2022 в 19:58
Gustav Дата: Понедельник, 13.06.2022, 00:40 | Сообщение № 4
Группа: Друзья
Ранг: Старожил
Сообщений: 2079
Репутация: 828 ±
Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1
А: Второй отбор

1 вася 5 5а Отлично
3 саша 5 5а Отлично

Строки 1 3
Для этого я и писал

=ЕСЛИ(J7="";"";МАКС($H$6:$H6)+1)

Правильно ли я понимаю, что этот абзац - ваше сокрушение о том, что строки пронумерованы как 1 и 3, в то время как вам хотелось бы видеть здесь 1 и 2 ? Если да, то обновим формулы до полного соответствия задумкам:
для ячейки A7 листа "Результат" (добавлена колонка со словом "Отлично"):
[vba]
Код
={SEQUENCE(COUNTIFS('Заявка'!B3:B;$C$2)) \
FILTER(
{'Заявка'!A3:C \ IF('Заявка'!B3:B=$C$2;$C$3;$D$3) \'Заявка'!D3:D};
'Заявка'!B3:B=$C$2)}
[/vba]
для ячейки H7 листа "Результат" (добавлена последовательная нумерация, без "дырок"):
[vba]
Код
={SEQUENCE(COUNTA(UNIQUE(FILTER(B7:B;D7:D=$J$2)))) \
ArrayFormula(VLOOKUP(
QUERY(FILTER(A7:F; D7:D=$J$2);
"select max(Col1) group by Col2 order by max(Col1) label max(Col1) ''");
A7:F; {3\2\5\6}; 0))}
[/vba]
Теперь хорошо?


Мой tip box - яд 41001663842605
 
Ответить
Сообщение
А: Второй отбор

1 вася 5 5а Отлично
3 саша 5 5а Отлично

Строки 1 3
Для этого я и писал

=ЕСЛИ(J7="";"";МАКС($H$6:$H6)+1)

Правильно ли я понимаю, что этот абзац - ваше сокрушение о том, что строки пронумерованы как 1 и 3, в то время как вам хотелось бы видеть здесь 1 и 2 ? Если да, то обновим формулы до полного соответствия задумкам:
для ячейки A7 листа "Результат" (добавлена колонка со словом "Отлично"):
[vba]
Код
={SEQUENCE(COUNTIFS('Заявка'!B3:B;$C$2)) \
FILTER(
{'Заявка'!A3:C \ IF('Заявка'!B3:B=$C$2;$C$3;$D$3) \'Заявка'!D3:D};
'Заявка'!B3:B=$C$2)}
[/vba]
для ячейки H7 листа "Результат" (добавлена последовательная нумерация, без "дырок"):
[vba]
Код
={SEQUENCE(COUNTA(UNIQUE(FILTER(B7:B;D7:D=$J$2)))) \
ArrayFormula(VLOOKUP(
QUERY(FILTER(A7:F; D7:D=$J$2);
"select max(Col1) group by Col2 order by max(Col1) label max(Col1) ''");
A7:F; {3\2\5\6}; 0))}
[/vba]
Теперь хорошо?

Автор - Gustav
Дата добавления - 13.06.2022 в 00:40
Мир MS Excel » Вопросы и решения » Excel и другие приложения » Google Docs » Отбор по критериям из таблицы в таблицу, без массива (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

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