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

Вход

Регистрация

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

 

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

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин  
Мир 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
Группа: Админы
Ранг: Местный житель
Сообщений: 14465
Репутация: 2384 ±
Замечаний: ±

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

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


Яндекс-деньги: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
Группа: Админы
Ранг: Местный житель
Сообщений: 14465
Репутация: 2384 ±
Замечаний: ±

Excel 2010
тему можно считать закрытой
Вы уверены?


Яндекс-деньги:41001419691823 | WMR:126292472390
 
Ответить
Сообщение
тему можно считать закрытой
Вы уверены?

Автор - 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
Группа: Админы
Ранг: Местный житель
Сообщений: 14465
Репутация: 2384 ±
Замечаний: ±

Excel 2010
рекомендовано писать что-то подобное
Рекомендовано, но не обязательно)

если бы кто-то предложил другие варианты
Что бы предложить другие варианты надо понимать, что не так в уже предложенном


Яндекс-деньги:41001419691823 | WMR:126292472390
 
Ответить
Сообщение
рекомендовано писать что-то подобное
Рекомендовано, но не обязательно)

если бы кто-то предложил другие варианты
Что бы предложить другие варианты надо понимать, что не так в уже предложенном

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

рекомендовано писать что-то подобное
Рекомендовано, но не обязательно)
Понял Вас)

если бы кто-то предложил другие варианты
Что бы предложить другие варианты надо понимать, что не так в уже предложенном
Предложенный вариант мне понравился!
 
Ответить
Сообщение
рекомендовано писать что-то подобное
Рекомендовано, но не обязательно)
Понял Вас)

если бы кто-то предложил другие варианты
Что бы предложить другие варианты надо понимать, что не так в уже предложенном
Предложенный вариант мне понравился!

Автор - 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
Группа: Админы
Ранг: Местный житель
Сообщений: 14465
Репутация: 2384 ±
Замечаний: ±

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


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

Есть тоже массивная альтернатива, но не требующая плясок с ячейками.
Код
=ИНДЕКС(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
 
Ответить
Сообщение
Есть тоже массивная альтернатива, но не требующая плясок с ячейками.
Код
=ИНДЕКС(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
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3651
Репутация: 647 ±
Замечаний: 0% ±

Excel 2013/2016
можно ли возвращать значения из диапазонов на разных листах?
возможно и да, но это не рекомендуется, так как формула будет много сложнее, тяжелее для расчета и скорее всего приведет к летучести. посмотрите эту тему, как пример.


Замечательный Временно просто медведь , процентов на 20.
 
Ответить
Сообщение
можно ли возвращать значения из диапазонов на разных листах?
возможно и да, но это не рекомендуется, так как формула будет много сложнее, тяжелее для расчета и скорее всего приведет к летучести. посмотрите эту тему, как пример.

Автор - bmv98rus
Дата добавления - 17.08.2021 в 17:25
Filipp Дата: Среда, 18.08.2021, 18:35 | Сообщение № 16
Группа: Пользователи
Ранг: Новичок
Сообщений: 10
Репутация: 0 ±
Замечаний: 0% ±

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


Да, согласен, спасибо большое!
 
Ответить
Сообщение
возможно и да, но это не рекомендуется, так как формула будет много сложнее, тяжелее для расчета и скорее всего приведет к летучести. посмотрите эту тему, как пример.


Да, согласен, спасибо большое!

Автор - Filipp
Дата добавления - 18.08.2021 в 18:35
Egyptian Дата: Четверг, 19.08.2021, 16:47 | Сообщение № 17
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 163
Репутация: 53 ±
Замечаний: 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
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3651
Репутация: 647 ±
Замечаний: 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
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 163
Репутация: 53 ±
Замечаний: 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
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3651
Репутация: 647 ±
Замечаний: 0% ±

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

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


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

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

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

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

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