Мегамозги от Excel присутствуют? - Кофейня и курилка

Ken13

На форуме 18 лет Сообщения: 865 Откуда: Орёл Авто: Лёха

# 25 Июня 2010 13:00

Задался тут целью-автоматизировать составление одного большущего отчета. Ломаю голову с одной из формул.

Имеем список из множества строк. Таблица 1. Каждому лицу может соответствовать проект от одного до нескольких. По каждому проекту свои показатели.

Цель-запихнуть в шахматку цифры (Таблица 2).

Пробовал формулу ВПР в связке с ЕСЛИ -пока не получается.

По идее должна идти сверка-отбор сперва по признакам Лица, а потом второй раз по признаку Проекта. Макросами тыщу лет не занимался уже-так что совсем далек. Может кто знает какую формулу можно растянуть по пустым ячейкам в шахматке, чтобы все становилось на свои места?

ThomasMoose

На форуме 18 лет Сообщения: 162 Откуда: мо Авто: MPS, Merida Matts 50-D

# 25 Июня 2010 13:09

мне кажется тут не впр не гпр не помогут потому как:

"Значения в первом столбце аргумента «таблица» должны быть расположены в возрастающем порядке, иначе функция ВПР может возвратить неправильный результат. Дополнительные сведения см. в разделе"

и
"Если в этом первом столбце имеется два или более значений, соответствующих аргументу «искомое_значение», используется первое найденное значение. Если точное соответствие не найдено, возвращается значение ошибки #Н/Д."

текст из справки

ThomasMoose

На форуме 18 лет Сообщения: 162 Откуда: мо Авто: MPS, Merida Matts 50-D

# 25 Июня 2010 13:10

а сколько лиц и проектов всего?

Ken13

На форуме 18 лет Сообщения: 865 Откуда: Орёл Авто: Лёха

# 25 Июня 2010 13:12

moosey-pussy писал(а):

а сколько лиц и проектов всего?

Всего уникальных лиц около 250. на каждое лицо может приходиться от 1 до 16 проектов

Ken13

На форуме 18 лет Сообщения: 865 Откуда: Орёл Авто: Лёха

# 25 Июня 2010 13:14

moosey-pussy писал(а):

"Если в этом первом столбце имеется два или более значений, соответствующих аргументу «искомое_значение», используется первое найденное значение. Если точное соответствие не найдено, возвращается значение ошибки #Н/Д."

текст из справки

Вот я и пытаюсь внести диррективы, чтобы возвращалось не первое попавшееся значение, а то которое соответствовало бы второму условия (соответствие проектов).

Правда боюсь не получится...

ThomasMoose

На форуме 18 лет Сообщения: 162 Откуда: мо Авто: MPS, Merida Matts 50-D

# 25 Июня 2010 13:24

поковырялся.
думаю без макросов, составления матриц и их транспонирования не обойтись.

anikus

На форуме 17 лет Сообщения: 175 Откуда: Чел-Град Авто: VAZ 2114; 1,6i; 06.2008=>Угнали сволочи!!! // VAZik 21150, 2001 года рождения.

# 25 Июня 2010 13:59

а если попробовать через "сводную таблицу"

Ken13

На форуме 18 лет Сообщения: 865 Откуда: Орёл Авто: Лёха

# 25 Июня 2010 14:02

anikus писал(а):

а если попробовать через "сводную таблицу"

Видимо придется так. Но все-равно полностью от копипаста не избавиться, т.к. моя табличка №2 -это часть одной обширной таблицы.

Ken13

На форуме 18 лет Сообщения: 865 Откуда: Орёл Авто: Лёха

# 25 Июня 2010 14:02

moosey-pussy писал(а):

поковырялся.
думаю без макросов, составления матриц и их транспонирования не обойтись.

Да...Спасибо за посильную помощь Good

Moggucci

На форуме 20 лет Сообщения: 1817 Откуда: Казань Авто: ВАЗ 2112 - MB W212

# 25 Июня 2010 14:04

Ken13, то, что у тебя на картинке - классический случай сводной таблицы.

Таблица с данными - табл 1, сводная таблица - табл 2.

Moggucci

На форуме 20 лет Сообщения: 1817 Откуда: Казань Авто: ВАЗ 2112 - MB W212

# 25 Июня 2010 14:13

Ken13, залил для тебя пример:

http://ifolder.ru/18303475

Чтобы понять, как она устроена, щелкаешь правой клавишей мыши на сводную таблицу, пункт меню "Мастер сводных таблиц", кнопка "Макет..."

anikus

На форуме 17 лет Сообщения: 175 Откуда: Чел-Град Авто: VAZ 2114; 1,6i; 06.2008=>Угнали сволочи!!! // VAZik 21150, 2001 года рождения.

# 25 Июня 2010 14:28

Ken13 писал(а):

Видимо придется так.

ну не знаю, чем тебя не устраивает сводная таблица

Ken13 писал(а):

все точно также как ты просил.

Ken13 писал(а):

моя табличка №2 -это часть одной обширной таблицы

там есть фильтр, отфильтруешь кого нужно и делов то.

Ken13

На форуме 18 лет Сообщения: 865 Откуда: Орёл Авто: Лёха

# 25 Июня 2010 14:36

Moggucci писал(а):

Ken13, залил для тебя пример:

http://ifolder.ru/18303475

Чтобы понять, как она устроена, щелкаешь правой клавишей мыши на сводную таблицу, пункт меню "Мастер сводных таблиц", кнопка "Макет..."

Сводная таблица малоудобна, т.к. я вырвал клок оз общей таблицы. В случае со сводной придется все-равно копировать и вставлять значения в общую таблицу вручную.

Но все-равно спасибо! Wink

Последний раз редактировалось: Ken13 (25 Июня 2010 14:39), всего редактировалось 1 раз

Ken13

На форуме 18 лет Сообщения: 865 Откуда: Орёл Авто: Лёха

# 25 Июня 2010 14:37

anikus писал(а):

ну не знаю, чем тебя не устраивает сводная таблица

спасибо. Сводная устраивает, но не очень удобна. Ручной труд будет необходим все-равно.

Но из двух зол-эта меньшая. Согласен

ThomasMoose

На форуме 18 лет Сообщения: 162 Откуда: мо Авто: MPS, Merida Matts 50-D

# 25 Июня 2010 15:11

сделал!

ThomasMoose

На форуме 18 лет Сообщения: 162 Откуда: мо Авто: MPS, Merida Matts 50-D

# 25 Июня 2010 15:13

есть данные:

лицо 1 проект 1 11
лицо 1 проект 2 12
лицо 2 проект 1 21
лицо 2 проект 2 22
лицо 2 проект 3 23
лицо 3 проект 3 33
лицо 3 проект 1 31

добавляем ещё 2 столбца:
третий формируем как =СЦЕПИТЬ(A1;B1)
четвертый =C1
растягиваем вниз

получаем:

лицо 1 проект 1 11 лицо 1проект 1 11
лицо 1 проект 2 12 лицо 1проект 2 12
лицо 2 проект 1 21 лицо 2проект 1 21
лицо 2 проект 2 22 лицо 2проект 2 22
лицо 2 проект 3 23 лицо 2проект 3 23
лицо 3 проект 3 33 лицо 3проект 3 33
лицо 3 проект 1 31 лицо 3проект 1 31

ThomasMoose

На форуме 18 лет Сообщения: 162 Откуда: мо Авто: MPS, Merida Matts 50-D

# 25 Июня 2010 15:16

формируем матрицу, строки - лица, столбцы - проекты:
проект 1 проект 2 проект 3
лицо 1
лицо 2
лицо 3

В ячейку бьём: =ВПР(СЦЕПИТЬ($A11;B$10);$D$1:$E$7;2;ЛОЖЬ)
растягиваем.

ThomasMoose

На форуме 18 лет Сообщения: 162 Откуда: мо Авто: MPS, Merida Matts 50-D

# 25 Июня 2010 15:21

существующее ограничение:
одно лицо одним и тем же проектом несколько раз заниматься не может. для тебя это существенное ограничение, додумывать по этому поводу надо чтонибудь?
з.ы. дашь мыло - пришлю файл.

Последний раз редактировалось: ThomasMoose (25 Июня 2010 15:47), всего редактировалось 1 раз

Ken13

На форуме 18 лет Сообщения: 865 Откуда: Орёл Авто: Лёха

# 25 Июня 2010 15:27

moosey-pussy писал(а):

формируем матрицу, строки - лица, столбцы - проекты:
проект 1 проект 2 проект 3
лицо 1
лицо 2
лицо 3

В ячейку бьём: =ВПР(СЦЕПИТЬ($A11;B$10);$D$1:$E$7;2;ЛОЖЬ)
растягиваем.

Ты красиво все решил. Все получилось! Спасибо! Drinks or Beer

Ken13

На форуме 18 лет Сообщения: 865 Откуда: Орёл Авто: Лёха

# 25 Июня 2010 15:29

moosey-pussy писал(а):

существующее ограничение:
одно лицо одним и тем же проектом несколько раз заниматься не может. для тебя это существенное ограничение, додумывать по этому поводу надо чтонибудь?
з.ы. дашь мыло - пришлю файл.

Не, одно лицо по одному проекту может встретиться только один раз!!!!

Сейчас мыло в личку кину

ThomasMoose

На форуме 18 лет Сообщения: 162 Откуда: мо Авто: MPS, Merida Matts 50-D

# 25 Июня 2010 15:46

./

Показать сообщения:

Перейти:

ИНФОРМАЦИЯ ПО ИКОНКАМ И ВОЗМОЖНОСТЯМ