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

Вход

Регистрация

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

 

= Мир MS Excel/Выбрать диапазон из диапазона, расставив значения по порядку - Мир MS Excel

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

Excel 2013
Уважаемые эксперты, нужна Ваша подсказка.
На одной странице - список (компьютеров) с логинами и паролями. Одному значению (компьютеру) может соответствовать от 1 до 20 пользователей.
На другой странице есть список с ячейками - от 1 до 20, в которые необходимо вставить пароли и логины соответственно - от того компьютера, номер которого мы вставляем в ячейку А1.
То есть, как я понял, вначале нужно из общего диапазона значений найти диапазон с искомым именем, а потом в нём найти по одному - нужные значения...?
Решение нужно формулой, без макросов, и желательно, без массивов.
Заранее спасибо!
К сообщению приложен файл: 8178262.xlsx(13Kb)
 
Ответить
СообщениеУважаемые эксперты, нужна Ваша подсказка.
На одной странице - список (компьютеров) с логинами и паролями. Одному значению (компьютеру) может соответствовать от 1 до 20 пользователей.
На другой странице есть список с ячейками - от 1 до 20, в которые необходимо вставить пароли и логины соответственно - от того компьютера, номер которого мы вставляем в ячейку А1.
То есть, как я понял, вначале нужно из общего диапазона значений найти диапазон с искомым именем, а потом в нём найти по одному - нужные значения...?
Решение нужно формулой, без макросов, и желательно, без массивов.
Заранее спасибо!

Автор - Strateg_ru
Дата добавления - 07.08.2016 в 14:11
Pelena Дата: Воскресенье, 07.08.2016, 15:17 | Сообщение № 2
Группа: Модераторы
Ранг: Экселист
Сообщений: 9839
Репутация: 2252 ±
Замечаний: 0% ±

Excel 2010 & Mac Excel 2011
Формула массива вводится сочетанием клавиш Ctrl+Shift+Enter
Код
=ЕСЛИОШИБКА(ИНДЕКС(Данные!$C$2:$C$100;НАИМЕНЬШИЙ(ЕСЛИ($A$1=Данные!$A$2:$A$100;СТРОКА(Данные!$A$2:$A$100)-1);СТРОКА(A1)));"")
К сообщению приложен файл: 6968065.xlsx(14Kb)


"Черт возьми, Холмс! Но как??!!"
ЯД 41001765434816
 
Ответить
СообщениеФормула массива вводится сочетанием клавиш Ctrl+Shift+Enter
Код
=ЕСЛИОШИБКА(ИНДЕКС(Данные!$C$2:$C$100;НАИМЕНЬШИЙ(ЕСЛИ($A$1=Данные!$A$2:$A$100;СТРОКА(Данные!$A$2:$A$100)-1);СТРОКА(A1)));"")

Автор - Pelena
Дата добавления - 07.08.2016 в 15:17
_Boroda_ Дата: Воскресенье, 07.08.2016, 16:04 | Сообщение № 3
Группа: Модераторы
Ранг: Экселист
Сообщений: 9347
Репутация: 3922 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Если компьютеры в первом листе по порядку, то такой вариант обычной формулой
Код
=ЕСЛИ($A2>СЧЁТЕСЛИ(Данные!$A$2:$A$99;$A$1);"";ИНДЕКС(Данные!B2:B99;ПОИСКПОЗ($A$1;Данные!$A$2:$A$99;)))

Если не по порядку, то такая немассивная формула даст значения в обратном порядке (снизу вверх)
Код
=ЕСЛИОШИБКА(ПРОСМОТР(;-1/(Данные!$A$2:$A$12=$A$1)/ЕНД(ПОИСКПОЗ(Данные!$B$2:$B$99;$B$1:$B1;));Данные!B$2:B$99);"")


Поля Логин и Пароль в листа 2 поменял местами для того, чтобы формулу можно было протягивать не только вниз, но и вправо.
К сообщению приложен файл: 8178262_1.xlsx(12Kb) · 8178262_2.xlsx(12Kb)


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеЕсли компьютеры в первом листе по порядку, то такой вариант обычной формулой
Код
=ЕСЛИ($A2>СЧЁТЕСЛИ(Данные!$A$2:$A$99;$A$1);"";ИНДЕКС(Данные!B2:B99;ПОИСКПОЗ($A$1;Данные!$A$2:$A$99;)))

Если не по порядку, то такая немассивная формула даст значения в обратном порядке (снизу вверх)
Код
=ЕСЛИОШИБКА(ПРОСМОТР(;-1/(Данные!$A$2:$A$12=$A$1)/ЕНД(ПОИСКПОЗ(Данные!$B$2:$B$99;$B$1:$B1;));Данные!B$2:B$99);"")


Поля Логин и Пароль в листа 2 поменял местами для того, чтобы формулу можно было протягивать не только вниз, но и вправо.

Автор - _Boroda_
Дата добавления - 07.08.2016 в 16:04
jakim Дата: Воскресенье, 07.08.2016, 17:28 | Сообщение № 4
Группа: Друзья
Ранг: Ветеран
Сообщений: 677
Репутация: 171 ±
Замечаний: 0% ±

Для Вас подойдёт и такой вариант.

Код
=IFERROR(INDEX(Данные!B$2:B$100;AGGREGATE(15;6;ROW($1:$100)/(Данные!$A$2:$A$100=Формула!$A$1);ROWS($2:2)));"")
К сообщению приложен файл: 6706598.xlsx(14Kb)


Сообщение отредактировал jakim - Воскресенье, 07.08.2016, 17:30
 
Ответить
Сообщение
Для Вас подойдёт и такой вариант.

Код
=IFERROR(INDEX(Данные!B$2:B$100;AGGREGATE(15;6;ROW($1:$100)/(Данные!$A$2:$A$100=Формула!$A$1);ROWS($2:2)));"")

Автор - jakim
Дата добавления - 07.08.2016 в 17:28
Strateg_ru Дата: Воскресенье, 07.08.2016, 21:50 | Сообщение № 5
Группа: Пользователи
Ранг: Новичок
Сообщений: 42
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
Pelena, спасибо! Всё работает.
 
Ответить
СообщениеPelena, спасибо! Всё работает.

Автор - Strateg_ru
Дата добавления - 07.08.2016 в 21:50
Strateg_ru Дата: Воскресенье, 07.08.2016, 21:52 | Сообщение № 6
Группа: Пользователи
Ранг: Новичок
Сообщений: 42
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
_Boroda_, со второй формулой - при копировании (размножении) данных на первом листе, второй почему-то не забирает новые значения... ни с сортировкой, ни без сортировки.
С первой формулой всё отлично работает!
 
Ответить
Сообщение_Boroda_, со второй формулой - при копировании (размножении) данных на первом листе, второй почему-то не забирает новые значения... ни с сортировкой, ни без сортировки.
С первой формулой всё отлично работает!

Автор - Strateg_ru
Дата добавления - 07.08.2016 в 21:52
Strateg_ru Дата: Воскресенье, 07.08.2016, 21:55 | Сообщение № 7
Группа: Пользователи
Ранг: Новичок
Сообщений: 42
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
jakim, при размножении данных получаются странные результаты... посмотрите, может я что-то делаю не так?
К сообщению приложен файл: 34.xlsx(14Kb)
 
Ответить
Сообщениеjakim, при размножении данных получаются странные результаты... посмотрите, может я что-то делаю не так?

Автор - Strateg_ru
Дата добавления - 07.08.2016 в 21:55
Pelena Дата: Воскресенье, 07.08.2016, 22:07 | Сообщение № 8
Группа: Модераторы
Ранг: Экселист
Сообщений: 9839
Репутация: 2252 ±
Замечаний: 0% ±

Excel 2010 & Mac Excel 2011
при копировании

То есть логин может повторяться с разными паролями? Или что означает "копирование"?


"Черт возьми, Холмс! Но как??!!"
ЯД 41001765434816
 
Ответить
Сообщение
при копировании

То есть логин может повторяться с разными паролями? Или что означает "копирование"?

Автор - Pelena
Дата добавления - 07.08.2016 в 22:07
Strateg_ru Дата: Воскресенье, 07.08.2016, 22:40 | Сообщение № 9
Группа: Пользователи
Ранг: Новичок
Сообщений: 42
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
Pelena, по факту - не может. Скопировал предыдущие данные, чтобы не креативеть ещё десяток строк. Формулы чувствительны к повторам?
 
Ответить
СообщениеPelena, по факту - не может. Скопировал предыдущие данные, чтобы не креативеть ещё десяток строк. Формулы чувствительны к повторам?

Автор - Strateg_ru
Дата добавления - 07.08.2016 в 22:40
jakim Дата: Воскресенье, 07.08.2016, 23:01 | Сообщение № 10
Группа: Друзья
Ранг: Ветеран
Сообщений: 677
Репутация: 171 ±
Замечаний: 0% ±

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

Автор - jakim
Дата добавления - 07.08.2016 в 23:01
krosav4ig Дата: Понедельник, 08.08.2016, 05:37 | Сообщение № 11
Группа: Друзья
Ранг: Старожил
Сообщений: 1336
Репутация: 533 ±
Замечаний: 0% ±

Excel 2007, 2013
Strateg_ru, а сводная не подойдет?
в файле сделал 2 варианта
1 - с помощью сводной
2 - подключение к внешним данным + макрос для обновления строки подключения
К сообщению приложен файл: 8178262.xlsm(27Kb)


(_)Õvõ(_)
 
Ответить
СообщениеStrateg_ru, а сводная не подойдет?
в файле сделал 2 варианта
1 - с помощью сводной
2 - подключение к внешним данным + макрос для обновления строки подключения

Автор - krosav4ig
Дата добавления - 08.08.2016 в 05:37
Strateg_ru Дата: Понедельник, 08.08.2016, 07:37 | Сообщение № 12
Группа: Пользователи
Ранг: Новичок
Сообщений: 42
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
jakim, да, теперь всё работает идеально - выбирает и неорганизованные данные и повторы. Спасибо!
 
Ответить
Сообщениеjakim, да, теперь всё работает идеально - выбирает и неорганизованные данные и повторы. Спасибо!

Автор - Strateg_ru
Дата добавления - 08.08.2016 в 07:37
Strateg_ru Дата: Понедельник, 08.08.2016, 07:43 | Сообщение № 13
Группа: Пользователи
Ранг: Новичок
Сообщений: 42
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
krosav4ig, сожалею - это сложнее, чем я смог понять :(
Мне этот результат надо сейчас будет вставлять в ещё более сложную конструкцию, поэтому и хотелось без макросов, лучше и без массивов. У меня самого квалификация в экселе пока на уровне обычных многовложенных формул, поэтому, как вклеить макрос в свою конструкцию - боюсь, что с ходу не разберусь.
Но, всё равно спасибо - ваше решение работает (хоть и не понимаю, как))
 
Ответить
Сообщениеkrosav4ig, сожалею - это сложнее, чем я смог понять :(
Мне этот результат надо сейчас будет вставлять в ещё более сложную конструкцию, поэтому и хотелось без макросов, лучше и без массивов. У меня самого квалификация в экселе пока на уровне обычных многовложенных формул, поэтому, как вклеить макрос в свою конструкцию - боюсь, что с ходу не разберусь.
Но, всё равно спасибо - ваше решение работает (хоть и не понимаю, как))

Автор - Strateg_ru
Дата добавления - 08.08.2016 в 07:43
Strateg_ru Дата: Понедельник, 08.08.2016, 07:44 | Сообщение № 14
Группа: Пользователи
Ранг: Новичок
Сообщений: 42
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
Друзья! Всем огромнейшее спасибо! Тему можно закрывать.
 
Ответить
СообщениеДрузья! Всем огромнейшее спасибо! Тему можно закрывать.

Автор - Strateg_ru
Дата добавления - 08.08.2016 в 07:44
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Выбрать диапазон из диапазона, расставив значения по порядку (Формулы/Formulas)
Страница 1 из 11
Поиск:

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