Strateg_ru
Дата: Воскресенье, 04.09.2016, 17:13 |
Сообщение № 1
Группа: Пользователи
Ранг: Участник
Сообщений: 65
Репутация:
0
±
Замечаний:
0% ±
Excel 2013
Здравствуйте, уважаемые эксперты форума! Требуется Ваша помощь в следующем вопросе... Дано: В диапазоне Группы!B2:J17 относительно "произвольно" расставлены названия групп. Названия уникальны. В пустых ячейках - формулы, выдающие по условию - "". В Пользователи!B:B - напротив имени пользователя стоит одна из групп вышеприведённого диапазона (вставляется вручную). Нужно: На странице Количество, в столбце А - выводить список групп из диапазона Группы!B2:J17 строго слева направо и сверху вниз. Если в данном диапазоне добавляется ещё одна группа, она добавляется за предыдущей, а всё следующие "двигаются" на одну вниз. В столбце В - должно стоять количество пользователей (группы из соседней - левой ячейки), напротив которых, на странице Пользователей, стоит эта группа. Количественные требования к формуле - групп в диапазоне - до 1000. Пользователей в группе - до 100. Образцы помечены цветом.
Здравствуйте, уважаемые эксперты форума! Требуется Ваша помощь в следующем вопросе... Дано: В диапазоне Группы!B2:J17 относительно "произвольно" расставлены названия групп. Названия уникальны. В пустых ячейках - формулы, выдающие по условию - "". В Пользователи!B:B - напротив имени пользователя стоит одна из групп вышеприведённого диапазона (вставляется вручную). Нужно: На странице Количество, в столбце А - выводить список групп из диапазона Группы!B2:J17 строго слева направо и сверху вниз. Если в данном диапазоне добавляется ещё одна группа, она добавляется за предыдущей, а всё следующие "двигаются" на одну вниз. В столбце В - должно стоять количество пользователей (группы из соседней - левой ячейки), напротив которых, на странице Пользователей, стоит эта группа. Количественные требования к формуле - групп в диапазоне - до 1000. Пользователей в группе - до 100. Образцы помечены цветом. Strateg_ru
К сообщению приложен файл:
_1.xlsx
(11.4 Kb)
Ответить
Сообщение Здравствуйте, уважаемые эксперты форума! Требуется Ваша помощь в следующем вопросе... Дано: В диапазоне Группы!B2:J17 относительно "произвольно" расставлены названия групп. Названия уникальны. В пустых ячейках - формулы, выдающие по условию - "". В Пользователи!B:B - напротив имени пользователя стоит одна из групп вышеприведённого диапазона (вставляется вручную). Нужно: На странице Количество, в столбце А - выводить список групп из диапазона Группы!B2:J17 строго слева направо и сверху вниз. Если в данном диапазоне добавляется ещё одна группа, она добавляется за предыдущей, а всё следующие "двигаются" на одну вниз. В столбце В - должно стоять количество пользователей (группы из соседней - левой ячейки), напротив которых, на странице Пользователей, стоит эта группа. Количественные требования к формуле - групп в диапазоне - до 1000. Пользователей в группе - до 100. Образцы помечены цветом. Автор - Strateg_ru Дата добавления - 04.09.2016 в 17:13
buchlotnik
Дата: Воскресенье, 04.09.2016, 17:52 |
Сообщение № 2
Группа: Заблокированные
Ранг: Участник клуба
Сообщений: 3442
Репутация:
929
±
Замечаний:
20% ±
2010, 2013, 2016 RUS / ENG
жуть какую-то соорудил Код
=ЕСЛИОШИБКА(ИНДЕКС(Группы!$A$1:$J$17;ЦЕЛОЕ(НАИМЕНЬШИЙ(ЕСЛИ(ДЛСТР(Группы!$B$2:$J$17)>0;СТРОКА(Группы!$B$2:$J$17)*100+СТОЛБЕЦ(Группы!$B$2:$J$17);"");СТРОКА(A1))%);ОСТАТ(НАИМЕНЬШИЙ(ЕСЛИ(ДЛСТР(Группы!$B$2:$J$17)>0;СТРОКА(Группы!$B$2:$J$17)*100+СТОЛБЕЦ(Группы!$B$2:$J$17);"");СТРОКА(A1));100));"")
массивка. Нули форматом убрал UPD Чутка сократил Код
=ЕСЛИОШИБКА(ИНДЕКС(Группы!$A$1:$J$17;ЦЕЛОЕ(НАИМЕНЬШИЙ(ЕСЛИ(Группы!$B$2:$J$17<>"";СТРОКА(Группы!$B$2:$J$17)*100+СТОЛБЕЦ(Группы!$B$2:$J$17);"");СТРОКА(A1))%);ОСТАТ(НАИМЕНЬШИЙ(ЕСЛИ(Группы!$B$2:$J$17<>"";СТРОКА(Группы!$B$2:$J$17)*100+СТОЛБЕЦ(Группы!$B$2:$J$17);"");СТРОКА(A1));100));"")
жуть какую-то соорудил Код
=ЕСЛИОШИБКА(ИНДЕКС(Группы!$A$1:$J$17;ЦЕЛОЕ(НАИМЕНЬШИЙ(ЕСЛИ(ДЛСТР(Группы!$B$2:$J$17)>0;СТРОКА(Группы!$B$2:$J$17)*100+СТОЛБЕЦ(Группы!$B$2:$J$17);"");СТРОКА(A1))%);ОСТАТ(НАИМЕНЬШИЙ(ЕСЛИ(ДЛСТР(Группы!$B$2:$J$17)>0;СТРОКА(Группы!$B$2:$J$17)*100+СТОЛБЕЦ(Группы!$B$2:$J$17);"");СТРОКА(A1));100));"")
массивка. Нули форматом убрал UPD Чутка сократил Код
=ЕСЛИОШИБКА(ИНДЕКС(Группы!$A$1:$J$17;ЦЕЛОЕ(НАИМЕНЬШИЙ(ЕСЛИ(Группы!$B$2:$J$17<>"";СТРОКА(Группы!$B$2:$J$17)*100+СТОЛБЕЦ(Группы!$B$2:$J$17);"");СТРОКА(A1))%);ОСТАТ(НАИМЕНЬШИЙ(ЕСЛИ(Группы!$B$2:$J$17<>"";СТРОКА(Группы!$B$2:$J$17)*100+СТОЛБЕЦ(Группы!$B$2:$J$17);"");СТРОКА(A1));100));"")
buchlotnik
Сообщение отредактировал buchlotnik - Воскресенье, 04.09.2016, 18:08
Ответить
Сообщение жуть какую-то соорудил Код
=ЕСЛИОШИБКА(ИНДЕКС(Группы!$A$1:$J$17;ЦЕЛОЕ(НАИМЕНЬШИЙ(ЕСЛИ(ДЛСТР(Группы!$B$2:$J$17)>0;СТРОКА(Группы!$B$2:$J$17)*100+СТОЛБЕЦ(Группы!$B$2:$J$17);"");СТРОКА(A1))%);ОСТАТ(НАИМЕНЬШИЙ(ЕСЛИ(ДЛСТР(Группы!$B$2:$J$17)>0;СТРОКА(Группы!$B$2:$J$17)*100+СТОЛБЕЦ(Группы!$B$2:$J$17);"");СТРОКА(A1));100));"")
массивка. Нули форматом убрал UPD Чутка сократил Код
=ЕСЛИОШИБКА(ИНДЕКС(Группы!$A$1:$J$17;ЦЕЛОЕ(НАИМЕНЬШИЙ(ЕСЛИ(Группы!$B$2:$J$17<>"";СТРОКА(Группы!$B$2:$J$17)*100+СТОЛБЕЦ(Группы!$B$2:$J$17);"");СТРОКА(A1))%);ОСТАТ(НАИМЕНЬШИЙ(ЕСЛИ(Группы!$B$2:$J$17<>"";СТРОКА(Группы!$B$2:$J$17)*100+СТОЛБЕЦ(Группы!$B$2:$J$17);"");СТРОКА(A1));100));"")
Автор - buchlotnik Дата добавления - 04.09.2016 в 17:52
Nic70y
Дата: Воскресенье, 04.09.2016, 17:56 |
Сообщение № 3
Группа: Друзья
Ранг: Экселист
Сообщений: 8759
Репутация:
2273
±
Замечаний:
0% ±
Excel 2010
примерно тоже, наверное, не проверял :(Код
=ИНДЕКС(Группы!A$1:J$17;НАИМЕНЬШИЙ(ЕСЛИ(Группы!B$2:J$17<>"";СТРОКА(A$2:G$17));СТРОКА(A1));ОКРУГЛ(ОСТАТ(НАИМЕНЬШИЙ(ЕСЛИ(Группы!B$2:J$17<>"";СТРОКА(A$2:G$17)+СТОЛБЕЦ(Группы!B$2:J$17)/1000);СТРОКА(A1));1)*1000;))
Код
=СЧЁТЕСЛИ(Пользователи!B:B;A2)
примерно тоже, наверное, не проверял :(Код
=ИНДЕКС(Группы!A$1:J$17;НАИМЕНЬШИЙ(ЕСЛИ(Группы!B$2:J$17<>"";СТРОКА(A$2:G$17));СТРОКА(A1));ОКРУГЛ(ОСТАТ(НАИМЕНЬШИЙ(ЕСЛИ(Группы!B$2:J$17<>"";СТРОКА(A$2:G$17)+СТОЛБЕЦ(Группы!B$2:J$17)/1000);СТРОКА(A1));1)*1000;))
Код
=СЧЁТЕСЛИ(Пользователи!B:B;A2)
Nic70y
ЮMoney 41001841029809
Ответить
Сообщение примерно тоже, наверное, не проверял :(Код
=ИНДЕКС(Группы!A$1:J$17;НАИМЕНЬШИЙ(ЕСЛИ(Группы!B$2:J$17<>"";СТРОКА(A$2:G$17));СТРОКА(A1));ОКРУГЛ(ОСТАТ(НАИМЕНЬШИЙ(ЕСЛИ(Группы!B$2:J$17<>"";СТРОКА(A$2:G$17)+СТОЛБЕЦ(Группы!B$2:J$17)/1000);СТРОКА(A1));1)*1000;))
Код
=СЧЁТЕСЛИ(Пользователи!B:B;A2)
Автор - Nic70y Дата добавления - 04.09.2016 в 17:56
buchlotnik
Дата: Воскресенье, 04.09.2016, 18:09 |
Сообщение № 4
Группа: Заблокированные
Ранг: Участник клуба
Сообщений: 3442
Репутация:
929
±
Замечаний:
20% ±
2010, 2013, 2016 RUS / ENG
Цитата
примерно тоже, наверное
то же, то же. Только чутка покороче и пошустрее, чем у меня
Цитата
примерно тоже, наверное
то же, то же. Только чутка покороче и пошустрее, чем у меня buchlotnik
Ответить
Сообщение Цитата
примерно тоже, наверное
то же, то же. Только чутка покороче и пошустрее, чем у меня Автор - buchlotnik Дата добавления - 04.09.2016 в 18:09
Strateg_ru
Дата: Воскресенье, 04.09.2016, 22:12 |
Сообщение № 5
Группа: Пользователи
Ранг: Участник
Сообщений: 65
Репутация:
0
±
Замечаний:
0% ±
Excel 2013
Друзья, спасибо огромное! У Nic70y немного запутался с диапазоном J и G, но формула полностью решает свою задачу! Всем плюсую. Тему можно закрывать.
Друзья, спасибо огромное! У Nic70y немного запутался с диапазоном J и G, но формула полностью решает свою задачу! Всем плюсую. Тему можно закрывать. Strateg_ru
Ответить
Сообщение Друзья, спасибо огромное! У Nic70y немного запутался с диапазоном J и G, но формула полностью решает свою задачу! Всем плюсую. Тему можно закрывать. Автор - Strateg_ru Дата добавления - 04.09.2016 в 22:12
krosav4ig
Дата: Понедельник, 05.09.2016, 00:40 |
Сообщение № 6
Группа: Друзья
Ранг: Старожил
Сообщений: 2347
Репутация:
989
±
Замечаний:
0% ±
Excel 2007,2010,2013
еще вариант, без формул, с использованием надстройки Power Query
[vba]
Код
let группы=Table.SelectColumns(Table.AddIndexColumn(Table.SelectRows(Table.UnpivotOtherColumns(Excel.CurrentWorkbook(){[Name="группы"]}[Content], {" "}, " ", "Группа"), each ([Группа] <> "")), "№п/п", 1, 1),{"№п/п","Группа"}), пользователи = Excel.CurrentWorkbook(){[Name="пользователи"]}[Content], пользователи_по_группам = Table.ExpandTableColumn(Table.NestedJoin(группы,{"Группа"},пользователи,{"Группа"}," ",JoinKind.LeftOuter), " ", {"Пользователь"}) in пользователи_по_группам
[/vba]
еще вариант, без формул, с использованием надстройки Power Query
[vba]
Код
let группы=Table.SelectColumns(Table.AddIndexColumn(Table.SelectRows(Table.UnpivotOtherColumns(Excel.CurrentWorkbook(){[Name="группы"]}[Content], {" "}, " ", "Группа"), each ([Группа] <> "")), "№п/п", 1, 1),{"№п/п","Группа"}), пользователи = Excel.CurrentWorkbook(){[Name="пользователи"]}[Content], пользователи_по_группам = Table.ExpandTableColumn(Table.NestedJoin(группы,{"Группа"},пользователи,{"Группа"}," ",JoinKind.LeftOuter), " ", {"Пользователь"}) in пользователи_по_группам
[/vba]
krosav4ig
email:krosav4ig26@gmail.com WMR R207627035142 WMZ Z821145374535 ЯД 410012026478460
Сообщение отредактировал krosav4ig - Понедельник, 05.09.2016, 00:43
Ответить
Сообщение еще вариант, без формул, с использованием надстройки Power Query
[vba]
Код
let группы=Table.SelectColumns(Table.AddIndexColumn(Table.SelectRows(Table.UnpivotOtherColumns(Excel.CurrentWorkbook(){[Name="группы"]}[Content], {" "}, " ", "Группа"), each ([Группа] <> "")), "№п/п", 1, 1),{"№п/п","Группа"}), пользователи = Excel.CurrentWorkbook(){[Name="пользователи"]}[Content], пользователи_по_группам = Table.ExpandTableColumn(Table.NestedJoin(группы,{"Группа"},пользователи,{"Группа"}," ",JoinKind.LeftOuter), " ", {"Пользователь"}) in пользователи_по_группам
[/vba]
Автор - krosav4ig Дата добавления - 05.09.2016 в 00:40
Strateg_ru
Дата: Понедельник, 05.09.2016, 10:04 |
Сообщение № 7
Группа: Пользователи
Ранг: Участник
Сообщений: 65
Репутация:
0
±
Замечаний:
0% ±
Excel 2013
krosav4ig , хм, Power Query - новая для меня штука... спасибо, поизучаю.
krosav4ig , хм, Power Query - новая для меня штука... спасибо, поизучаю.Strateg_ru
Ответить
Сообщение krosav4ig , хм, Power Query - новая для меня штука... спасибо, поизучаю.Автор - Strateg_ru Дата добавления - 05.09.2016 в 10:04