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

Вход

Регистрация

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

 

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

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

Excel 2013
Уважаемые эксперты, нужна Ваша подсказка.
На одной странице - список (компьютеров) с логинами и паролями. Одному значению (компьютеру) может соответствовать от 1 до 20 пользователей.
На другой странице есть список с ячейками - от 1 до 20, в которые необходимо вставить пароли и логины соответственно - от того компьютера, номер которого мы вставляем в ячейку А1.
То есть, как я понял, вначале нужно из общего диапазона значений найти диапазон с искомым именем, а потом в нём найти по одному - нужные значения...?
Решение нужно формулой, без макросов, и желательно, без массивов.
Заранее спасибо!
К сообщению приложен файл: 8178262.xlsx (13.2 Kb)
 
Ответить
СообщениеУважаемые эксперты, нужна Ваша подсказка.
На одной странице - список (компьютеров) с логинами и паролями. Одному значению (компьютеру) может соответствовать от 1 до 20 пользователей.
На другой странице есть список с ячейками - от 1 до 20, в которые необходимо вставить пароли и логины соответственно - от того компьютера, номер которого мы вставляем в ячейку А1.
То есть, как я понял, вначале нужно из общего диапазона значений найти диапазон с искомым именем, а потом в нём найти по одному - нужные значения...?
Решение нужно формулой, без макросов, и желательно, без массивов.
Заранее спасибо!
Автор - Strateg_ru
Дата добавления - 07.08.2016 в 14:11
Pelena Дата: Воскресенье, 07.08.2016, 15:17 | Сообщение № 2
Группа: Админы
Ранг: Местный житель
Сообщений: 19182
Репутация: 4420 ±
Замечаний: ±

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

"Черт возьми, Холмс! Но как??!!"
Ю-money 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
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16675
Репутация: 6481 ±
Замечаний: 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 (12.3 Kb) · 8178262_2.xlsx (12.2 Kb)

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
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
Группа: Друзья
Ранг: Старожил
Сообщений: 1199
Репутация: 313 ±
Замечаний: 0% ±

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

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


Сообщение отредактировал 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
Группа: Пользователи
Ранг: Участник
Сообщений: 65
Репутация: 0 ±
Замечаний: 0% ±

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

Excel 2013
_Boroda_, со второй формулой - при копировании (размножении) данных на первом листе, второй почему-то не забирает новые значения... ни с сортировкой, ни без сортировки.
С первой формулой всё отлично работает!
 
Ответить
Сообщение_Boroda_, со второй формулой - при копировании (размножении) данных на первом листе, второй почему-то не забирает новые значения... ни с сортировкой, ни без сортировки.
С первой формулой всё отлично работает!
Автор - Strateg_ru
Дата добавления - 07.08.2016 в 21:52
Strateg_ru Дата: Воскресенье, 07.08.2016, 21:55 | Сообщение № 7
Группа: Пользователи
Ранг: Участник
Сообщений: 65
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
jakim, при размножении данных получаются странные результаты... посмотрите, может я что-то делаю не так?
К сообщению приложен файл: 34.xlsx (14.4 Kb)
 
Ответить
Сообщениеjakim, при размножении данных получаются странные результаты... посмотрите, может я что-то делаю не так?
Автор - Strateg_ru
Дата добавления - 07.08.2016 в 21:55
Pelena Дата: Воскресенье, 07.08.2016, 22:07 | Сообщение № 8
Группа: Админы
Ранг: Местный житель
Сообщений: 19182
Репутация: 4420 ±
Замечаний: ±

Excel 365 & Mac Excel
Цитата Strateg_ru, 07.08.2016 в 21:52, в сообщении № 6
при копировании

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

"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
Сообщение
Цитата Strateg_ru, 07.08.2016 в 21:52, в сообщении № 6
при копировании

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

Excel 2013
Pelena, по факту - не может. Скопировал предыдущие данные, чтобы не креативеть ещё десяток строк. Формулы чувствительны к повторам?
 
Ответить
СообщениеPelena, по факту - не может. Скопировал предыдущие данные, чтобы не креативеть ещё десяток строк. Формулы чувствительны к повторам?
Автор - Strateg_ru
Дата добавления - 07.08.2016 в 22:40
jakim Дата: Воскресенье, 07.08.2016, 23:01 | Сообщение № 10
Группа: Друзья
Ранг: Старожил
Сообщений: 1199
Репутация: 313 ±
Замечаний: 0% ±

Excel 2010
Я думаю, что в натуре у Вас не должно быть дубликатов пользователей паролей.
Ошибку в несоответсвии диапазонов исправил, за что извините.
К сообщению приложен файл: 0122927.xlsx (14.3 Kb)
 
Ответить
Сообщение
Я думаю, что в натуре у Вас не должно быть дубликатов пользователей паролей.
Ошибку в несоответсвии диапазонов исправил, за что извините.
Автор - jakim
Дата добавления - 07.08.2016 в 23:01
krosav4ig Дата: Понедельник, 08.08.2016, 05:37 | Сообщение № 11
Группа: Друзья
Ранг: Старожил
Сообщений: 2347
Репутация: 989 ±
Замечаний: 0% ±

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

email:krosav4ig26@gmail.com WMR R207627035142 WMZ Z821145374535 ЯД 410012026478460
 
Ответить
СообщениеStrateg_ru, а сводная не подойдет?
в файле сделал 2 варианта
1 - с помощью сводной
2 - подключение к внешним данным + макрос для обновления строки подключения
Автор - krosav4ig
Дата добавления - 08.08.2016 в 05:37
Strateg_ru Дата: Понедельник, 08.08.2016, 07:37 | Сообщение № 12
Группа: Пользователи
Ранг: Участник
Сообщений: 65
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
jakim, да, теперь всё работает идеально - выбирает и неорганизованные данные и повторы. Спасибо!
 
Ответить
Сообщениеjakim, да, теперь всё работает идеально - выбирает и неорганизованные данные и повторы. Спасибо!
Автор - Strateg_ru
Дата добавления - 08.08.2016 в 07:37
Strateg_ru Дата: Понедельник, 08.08.2016, 07:43 | Сообщение № 13
Группа: Пользователи
Ранг: Участник
Сообщений: 65
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
krosav4ig, сожалею - это сложнее, чем я смог понять :(
Мне этот результат надо сейчас будет вставлять в ещё более сложную конструкцию, поэтому и хотелось без макросов, лучше и без массивов. У меня самого квалификация в экселе пока на уровне обычных многовложенных формул, поэтому, как вклеить макрос в свою конструкцию - боюсь, что с ходу не разберусь.
Но, всё равно спасибо - ваше решение работает (хоть и не понимаю, как))
 
Ответить
Сообщениеkrosav4ig, сожалею - это сложнее, чем я смог понять :(
Мне этот результат надо сейчас будет вставлять в ещё более сложную конструкцию, поэтому и хотелось без макросов, лучше и без массивов. У меня самого квалификация в экселе пока на уровне обычных многовложенных формул, поэтому, как вклеить макрос в свою конструкцию - боюсь, что с ходу не разберусь.
Но, всё равно спасибо - ваше решение работает (хоть и не понимаю, как))
Автор - Strateg_ru
Дата добавления - 08.08.2016 в 07:43
Strateg_ru Дата: Понедельник, 08.08.2016, 07:44 | Сообщение № 14
Группа: Пользователи
Ранг: Участник
Сообщений: 65
Репутация: 0 ±
Замечаний: 0% ±

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

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