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

Вход

Регистрация

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

 

= Мир MS Excel/ВПР или другой способ извлечь данные объединенной ячейки - Мир MS Excel

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

Всем добрый день!
Помогите пожалуйста разобраться с проблемой.
В файле Excel есть ряд проектов, у которых есть исполнители. Исполнители указываются в отдельных строках, а проект растягивается на все строки столбца, в которых есть исполнители по данному проекту. На одном листе есть список проектов и исполнителей, а на других листах - данные по исполнителям. Необходимо прописать формулу, которая бы проходилась по исполнителям и возвращала проекты, в которых те задействованы.
Хотел реализовать это через комбинацию ВПР и СЧЕТЕСЛИ, но столкнулся с проблемой, что из объединенных ячеек ВПР берет название проекта только для верхней ячейки, а по остальным ничего не выдает, соответственно формула работает только для первого исполнителя.
Подскажите пожалуйста, можно ли как-то изящно реализовать такую функцию, возможно не через ВПР? С VBA не знаком.

Файл с примером приложил.

Заранее огромное спасибо!
К сообщению приложен файл: 5488205.xls (24.0 Kb)
 
Ответить
СообщениеВсем добрый день!
Помогите пожалуйста разобраться с проблемой.
В файле Excel есть ряд проектов, у которых есть исполнители. Исполнители указываются в отдельных строках, а проект растягивается на все строки столбца, в которых есть исполнители по данному проекту. На одном листе есть список проектов и исполнителей, а на других листах - данные по исполнителям. Необходимо прописать формулу, которая бы проходилась по исполнителям и возвращала проекты, в которых те задействованы.
Хотел реализовать это через комбинацию ВПР и СЧЕТЕСЛИ, но столкнулся с проблемой, что из объединенных ячеек ВПР берет название проекта только для верхней ячейки, а по остальным ничего не выдает, соответственно формула работает только для первого исполнителя.
Подскажите пожалуйста, можно ли как-то изящно реализовать такую функцию, возможно не через ВПР? С VBA не знаком.

Файл с примером приложил.

Заранее огромное спасибо!
Автор - Filipp
Дата добавления - 17.08.2021 в 10:16
Serge_007 Дата: Вторник, 17.08.2021, 10:28 | Сообщение № 2
Группа: Админы
Ранг: Местный житель
Сообщений: 16475
Репутация: 2749 ±
Замечаний: ±

Excel 2016
Здравствуйте

Так надо (см. вложение)?
К сообщению приложен файл: 20210817_Filipp.xls (58.5 Kb)

ЮMoney:41001419691823 | WMR:126292472390
 
Ответить
СообщениеЗдравствуйте

Так надо (см. вложение)?
Автор - Serge_007
Дата добавления - 17.08.2021 в 10:28
Filipp Дата: Вторник, 17.08.2021, 11:17 | Сообщение № 3
Группа: Пользователи
Ранг: Новичок
Сообщений: 10
Репутация: 0 ±
Замечаний: 0% ±

Serge_007, да, то что нужно, большое Вам спасибо!
 
Ответить
СообщениеSerge_007, да, то что нужно, большое Вам спасибо!
Автор - Filipp
Дата добавления - 17.08.2021 в 11:17
Filipp Дата: Вторник, 17.08.2021, 11:19 | Сообщение № 4
Группа: Пользователи
Ранг: Новичок
Сообщений: 10
Репутация: 0 ±
Замечаний: 0% ±

Спасибо, хороший вариант решения, получен, поэтому тему можно считать закрытой.
 
Ответить
СообщениеСпасибо, хороший вариант решения, получен, поэтому тему можно считать закрытой.
Автор - Filipp
Дата добавления - 17.08.2021 в 11:19
Serge_007 Дата: Вторник, 17.08.2021, 11:23 | Сообщение № 5
Группа: Админы
Ранг: Местный житель
Сообщений: 16475
Репутация: 2749 ±
Замечаний: ±

Excel 2016
Цитата Filipp, 17.08.2021 в 11:19, в сообщении № 4 ()
тему можно считать закрытой
Вы уверены?

ЮMoney:41001419691823 | WMR:126292472390
 
Ответить
Сообщение
Цитата Filipp, 17.08.2021 в 11:19, в сообщении № 4 ()
тему можно считать закрытой
Вы уверены?
Автор - Serge_007
Дата добавления - 17.08.2021 в 11:23
Filipp Дата: Вторник, 17.08.2021, 11:26 | Сообщение № 6
Группа: Пользователи
Ранг: Новичок
Сообщений: 10
Репутация: 0 ±
Замечаний: 0% ±

В принципе, я не против если бы кто-то предложил другие варианты.
Просто в правилах было рекомендовано писать что-то подобное, поэтому я и написал так.


Сообщение отредактировал Serge_007 - Вторник, 17.08.2021, 11:38
 
Ответить
СообщениеВ принципе, я не против если бы кто-то предложил другие варианты.
Просто в правилах было рекомендовано писать что-то подобное, поэтому я и написал так.
Автор - Filipp
Дата добавления - 17.08.2021 в 11:26
Serge_007 Дата: Вторник, 17.08.2021, 11:41 | Сообщение № 7
Группа: Админы
Ранг: Местный житель
Сообщений: 16475
Репутация: 2749 ±
Замечаний: ±

Excel 2016
Цитата Filipp, 17.08.2021 в 11:26, в сообщении № 6 ()
рекомендовано писать что-то подобное
Рекомендовано, но не обязательно)

Цитата Filipp, 17.08.2021 в 11:26, в сообщении № 6 ()
если бы кто-то предложил другие варианты
Что бы предложить другие варианты надо понимать, что не так в уже предложенном

ЮMoney:41001419691823 | WMR:126292472390
 
Ответить
Сообщение
Цитата Filipp, 17.08.2021 в 11:26, в сообщении № 6 ()
рекомендовано писать что-то подобное
Рекомендовано, но не обязательно)

Цитата Filipp, 17.08.2021 в 11:26, в сообщении № 6 ()
если бы кто-то предложил другие варианты
Что бы предложить другие варианты надо понимать, что не так в уже предложенном
Автор - Serge_007
Дата добавления - 17.08.2021 в 11:41
Filipp Дата: Вторник, 17.08.2021, 11:58 | Сообщение № 8
Группа: Пользователи
Ранг: Новичок
Сообщений: 10
Репутация: 0 ±
Замечаний: 0% ±

Цитата Serge_007, 17.08.2021 в 11:41, в сообщении № 7 ()
рекомендовано писать что-то подобное
Рекомендовано, но не обязательно)
Понял Вас)

Цитата Serge_007, 17.08.2021 в 11:41, в сообщении № 7 ()
если бы кто-то предложил другие варианты
Что бы предложить другие варианты надо понимать, что не так в уже предложенном
Предложенный вариант мне понравился!
 
Ответить
Сообщение
Цитата Serge_007, 17.08.2021 в 11:41, в сообщении № 7 ()
рекомендовано писать что-то подобное
Рекомендовано, но не обязательно)
Понял Вас)

Цитата Serge_007, 17.08.2021 в 11:41, в сообщении № 7 ()
если бы кто-то предложил другие варианты
Что бы предложить другие варианты надо понимать, что не так в уже предложенном
Предложенный вариант мне понравился!
Автор - Filipp
Дата добавления - 17.08.2021 в 11:58
Filipp Дата: Вторник, 17.08.2021, 13:40 | Сообщение № 9
Группа: Пользователи
Ранг: Новичок
Сообщений: 10
Репутация: 0 ±
Замечаний: 0% ±

Serge_007, подскажите пожалуйста, как в присланном файле Вы сделали так, чтобы под объединенной ячейкой остались значения?
 
Ответить
СообщениеSerge_007, подскажите пожалуйста, как в присланном файле Вы сделали так, чтобы под объединенной ячейкой остались значения?
Автор - Filipp
Дата добавления - 17.08.2021 в 13:40
Serge_007 Дата: Вторник, 17.08.2021, 13:50 | Сообщение № 10
Группа: Админы
Ранг: Местный житель
Сообщений: 16475
Репутация: 2749 ±
Замечаний: ±

Excel 2016
1. - разъединяете объединенную ячейку с данными
2. - дублируете в получившиеся пустые ячейки значение верхней ячейки
3. - в любом месте файла объединяете пустые ячейки (столько, сколько было в объединённой, которую Вы разъединили на шаге 1) в одну
4. - копируете получившуюся объединённую ячейку
5. - выделяете разъедененные на шаге 1 ячейки
6. - специальная вставка
7. - вставить форматы

ЮMoney:41001419691823 | WMR:126292472390
 
Ответить
Сообщение1. - разъединяете объединенную ячейку с данными
2. - дублируете в получившиеся пустые ячейки значение верхней ячейки
3. - в любом месте файла объединяете пустые ячейки (столько, сколько было в объединённой, которую Вы разъединили на шаге 1) в одну
4. - копируете получившуюся объединённую ячейку
5. - выделяете разъедененные на шаге 1 ячейки
6. - специальная вставка
7. - вставить форматы
Автор - Serge_007
Дата добавления - 17.08.2021 в 13:50
Filipp Дата: Вторник, 17.08.2021, 15:09 | Сообщение № 11
Группа: Пользователи
Ранг: Новичок
Сообщений: 10
Репутация: 0 ±
Замечаний: 0% ±

Большое Вам спасибо!


Сообщение отредактировал Serge_007 - Вторник, 17.08.2021, 15:35
 
Ответить
СообщениеБольшое Вам спасибо!
Автор - Filipp
Дата добавления - 17.08.2021 в 15:09
bmv98rus Дата: Вторник, 17.08.2021, 15:18 | Сообщение № 12
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4106
Репутация: 768 ±
Замечаний: 0% ±

Excel 2013/2016
Есть тоже массивная альтернатива, но не требующая плясок с ячейками.
Код
=INDEX(C:C;LOOKUP(SMALL(IF((B$18=B$2:B$10);ROW($2:$10));ROW(A1));IF($C$2:$C$10<>"";ROW($C$2:$C$10))))


Serge_007,
Код
=INDEX(C$2:C$10;SMALL(IF((B$18=B$2:B$10)*ROW($1:$9);ROW($1:$9));ROW(A1)))

лишнее *ROW($1:$9)
К сообщению приложен файл: example2400.xlsx (9.8 Kb)

Замечательный Временно просто медведь , процентов на 20.

Сообщение отредактировал bmv98rus - Вторник, 17.08.2021, 15:18
 
Ответить
СообщениеЕсть тоже массивная альтернатива, но не требующая плясок с ячейками.
Код
=INDEX(C:C;LOOKUP(SMALL(IF((B$18=B$2:B$10);ROW($2:$10));ROW(A1));IF($C$2:$C$10<>"";ROW($C$2:$C$10))))


Serge_007,
Код
=INDEX(C$2:C$10;SMALL(IF((B$18=B$2:B$10)*ROW($1:$9);ROW($1:$9));ROW(A1)))

лишнее *ROW($1:$9)
Автор - bmv98rus
Дата добавления - 17.08.2021 в 15:18
Filipp Дата: Вторник, 17.08.2021, 15:37 | Сообщение № 13
Группа: Пользователи
Ранг: Новичок
Сообщений: 10
Репутация: 0 ±
Замечаний: 0% ±

Цитата bmv98rus, 17.08.2021 в 15:18, в сообщении № 12 ()
Есть тоже массивная альтернатива, но не требующая плясок с ячейками.
Код
=ИНДЕКС(C:C;ПРОСМОТР(НАИМЕНЬШИЙ(ЕСЛИ((B$18=B$2:B$10);СТРОКА($2:$10));СТРОКА(A1));ЕСЛИ($C$2:$C$10<>"";СТРОКА($C$2:$C$10))))
bmv98rus, большое спасибо!


Сообщение отредактировал Serge_007 - Вторник, 17.08.2021, 15:38
 
Ответить
Сообщение
Цитата bmv98rus, 17.08.2021 в 15:18, в сообщении № 12 ()
Есть тоже массивная альтернатива, но не требующая плясок с ячейками.
Код
=ИНДЕКС(C:C;ПРОСМОТР(НАИМЕНЬШИЙ(ЕСЛИ((B$18=B$2:B$10);СТРОКА($2:$10));СТРОКА(A1));ЕСЛИ($C$2:$C$10<>"";СТРОКА($C$2:$C$10))))
bmv98rus, большое спасибо!
Автор - Filipp
Дата добавления - 17.08.2021 в 15:37
Filipp Дата: Вторник, 17.08.2021, 16:20 | Сообщение № 14
Группа: Пользователи
Ранг: Новичок
Сообщений: 10
Репутация: 0 ±
Замечаний: 0% ±

bmv98rus, подскажите пожалуйста, при помощи данной функции можно ли возвращать значения из диапазонов на разных листах?
 
Ответить
Сообщениеbmv98rus, подскажите пожалуйста, при помощи данной функции можно ли возвращать значения из диапазонов на разных листах?
Автор - Filipp
Дата добавления - 17.08.2021 в 16:20
bmv98rus Дата: Вторник, 17.08.2021, 17:25 | Сообщение № 15
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4106
Репутация: 768 ±
Замечаний: 0% ±

Excel 2013/2016
Цитата Filipp, 17.08.2021 в 16:20, в сообщении № 14 ()
можно ли возвращать значения из диапазонов на разных листах?
возможно и да, но это не рекомендуется, так как формула будет много сложнее, тяжелее для расчета и скорее всего приведет к летучести. посмотрите эту тему, как пример.

Замечательный Временно просто медведь , процентов на 20.
 
Ответить
Сообщение
Цитата Filipp, 17.08.2021 в 16:20, в сообщении № 14 ()
можно ли возвращать значения из диапазонов на разных листах?
возможно и да, но это не рекомендуется, так как формула будет много сложнее, тяжелее для расчета и скорее всего приведет к летучести. посмотрите эту тему, как пример.
Автор - bmv98rus
Дата добавления - 17.08.2021 в 17:25
Filipp Дата: Среда, 18.08.2021, 18:35 | Сообщение № 16
Группа: Пользователи
Ранг: Новичок
Сообщений: 10
Репутация: 0 ±
Замечаний: 0% ±

Цитата bmv98rus, 17.08.2021 в 17:25, в сообщении № 15 ()
возможно и да, но это не рекомендуется, так как формула будет много сложнее, тяжелее для расчета и скорее всего приведет к летучести. посмотрите эту тему, как пример.


Да, согласен, спасибо большое!
 
Ответить
Сообщение
Цитата bmv98rus, 17.08.2021 в 17:25, в сообщении № 15 ()
возможно и да, но это не рекомендуется, так как формула будет много сложнее, тяжелее для расчета и скорее всего приведет к летучести. посмотрите эту тему, как пример.


Да, согласен, спасибо большое!
Автор - Filipp
Дата добавления - 18.08.2021 в 18:35
Egyptian Дата: Четверг, 19.08.2021, 16:47 | Сообщение № 17
Группа: Проверенные
Ранг: Ветеран
Сообщений: 514
Репутация: 185 ±
Замечаний: 0% ±

Excel 2013/2016
В рамках темы еще немассивный вариант.
Код
=INDEX($C$2:$C$11;MATCH({"Яяяя"\"Zzzz"};$C$2:INDEX($C$2:$C$11;AGGREGATE(15;6;ROW($C$1:$C$11)/($B$18=$B$2:$B$11);ROW(A1)))))
К сообщению приложен файл: samples.xlsx (9.7 Kb)
 
Ответить
СообщениеВ рамках темы еще немассивный вариант.
Код
=INDEX($C$2:$C$11;MATCH({"Яяяя"\"Zzzz"};$C$2:INDEX($C$2:$C$11;AGGREGATE(15;6;ROW($C$1:$C$11)/($B$18=$B$2:$B$11);ROW(A1)))))
Автор - Egyptian
Дата добавления - 19.08.2021 в 16:47
bmv98rus Дата: Четверг, 19.08.2021, 18:42 | Сообщение № 18
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4106
Репутация: 768 ±
Замечаний: 0% ±

Excel 2013/2016
не массивный с агрегатом проще
Код
=INDEX(C:C;LOOKUP(AGGREGATE(15;6;ROW($2:$10)/(B$18=B$2:B$10);ROW(A1));ROW($C$2:$C$10)/($C$2:$C$10<>"")))

при этом разница по скорости минимальна.
К сообщению приложен файл: 8663342.xlsx (9.8 Kb)

Замечательный Временно просто медведь , процентов на 20.

Сообщение отредактировал bmv98rus - Четверг, 19.08.2021, 18:47
 
Ответить
Сообщениене массивный с агрегатом проще
Код
=INDEX(C:C;LOOKUP(AGGREGATE(15;6;ROW($2:$10)/(B$18=B$2:B$10);ROW(A1));ROW($C$2:$C$10)/($C$2:$C$10<>"")))

при этом разница по скорости минимальна.
Автор - bmv98rus
Дата добавления - 19.08.2021 в 18:42
Egyptian Дата: Четверг, 19.08.2021, 20:53 | Сообщение № 19
Группа: Проверенные
Ранг: Ветеран
Сообщений: 514
Репутация: 185 ±
Замечаний: 0% ±

Excel 2013/2016
Ну и контрольный выстрел по объединенным ячейкам - скармливаем ПРОСМОТР индексу.
Код
=INDEX(LOOKUP(ROW($C$2:$C$15),1/ISERR(-$C$2:$C$15)*ROW($C$2:$C$15),$C$2:$C$15),AGGREGATE(15,6,ROW($A$1:$A$20)/($B$2:$B$15=$B$18),ROW(A1)))

Хотя, подозреваю, это будет медленнее предыдущих вариантов.
 
Ответить
СообщениеНу и контрольный выстрел по объединенным ячейкам - скармливаем ПРОСМОТР индексу.
Код
=INDEX(LOOKUP(ROW($C$2:$C$15),1/ISERR(-$C$2:$C$15)*ROW($C$2:$C$15),$C$2:$C$15),AGGREGATE(15,6,ROW($A$1:$A$20)/($B$2:$B$15=$B$18),ROW(A1)))

Хотя, подозреваю, это будет медленнее предыдущих вариантов.
Автор - Egyptian
Дата добавления - 19.08.2021 в 20:53
bmv98rus Дата: Пятница, 20.08.2021, 07:28 | Сообщение № 20
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4106
Репутация: 768 ±
Замечаний: 0% ±

Excel 2013/2016
отличия по скорости есть, но они не кардинальные

но надо смотреть что будет при увеличении количества данных.

Замечательный Временно просто медведь , процентов на 20.

Сообщение отредактировал bmv98rus - Пятница, 20.08.2021, 07:30
 
Ответить
Сообщениеотличия по скорости есть, но они не кардинальные

но надо смотреть что будет при увеличении количества данных.
Автор - bmv98rus
Дата добавления - 20.08.2021 в 07:28
Мир MS Excel » Вопросы и решения » Вопросы по Excel » ВПР или другой способ извлечь данные объединенной ячейки (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

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