подскажите как сделать отчёт из нескольких книг в одной

Категория Помощь

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

Комментарии  

 
0 #10 anna 07.06.2013 12:11
Понятно! спасибо за подробный ответ.
Да, ВПР ссылается по номеру, неудобно, конечно, если надо потом будет вставлять стоблец, придется переделывать. Меня именно интересовал вопрос про использование имен. Значит именнованная таблица и имя по-разному воспринимается.
Цитировать
 
 
0 #9 eduard 05.06.2013 10:52
Anna, разобрался. Здесь особенность заключается в том, как Excel хранит данные. Именованная таблица и имя, которое вы присваиваете таблице учитываются программой по-разному.

Если задать диапазону A:B на листе имя вручную (я сделал "price"), и ссылаться на него, то проблемы не возникает. Смотрите приложенные файлы.

Говоря об этом примере в целом, именно здесь я бы не стал использовать имена. В основном из-за недоработанност и формулы ВПР (VLOOKUP). В ее структуре предполагается жестко указывать номер столбца, из которого необходимо брать значение. Было бы гораздо удобнее, если бы предлагалось указать два параллельных диапазона. Такая формула есть - ПРОСМОТР (LOOKUP) - но и у нее есть недостаток, ее нельзя заставить искать точное соответствие.

Чем это чревато? Предположим, завтра вам понадобится в прайс вставить колонку между номенклатурным номером (я не рекомендую искать цену по имени, лучше для этого использовать уникальный номенклатурный номер) и ценой (например, единицу измерения или валюту). Ваша формула при этом "поедет", так как она будет ссылаться на другую ячейку. И это никак не зависит от того, пользуетесь ли вы именами или указываете просто ссылку на ячейки, столбцы.

Проблема решается с помощью использования двух функций: ПОИСКПОЗ (MATCH) и ИНДЕКС (INDEX) и присвоением еще двух имен: для каждой колонки отдельно. Как - смотрите в приложенном файле.
Цитировать
 
 
0 #8 anna 02.06.2013 14:52
Эдуард, да так работает. У меня не работает, когда, например, есть две книги одна "tovar" в ней есть именнованная таблица "Товар" (созданная с помощью команды "Форматировать как таблицу"), другая "Price" в ней есть именнованная таблица "Прайс" (созданная с помощью команды "Форматировать как таблицу"). С помощью функции ВПР из таблицы Прайс подтягиваю цены, чтобы подставить их в таблицу Товар. Так вот, когда книги открыты, то все нормально, ссылка ссылается по имени на таблицу, если делаю изменения в книге Прайс и закрываю ее, а потом открываю Товар! Делаю обновить данные, то вместо формулы пишется #ССЫЛКА!, хотя прописан адрес.
Наверно, запутала Вас. Прикрепляю пример.
Цитировать
 
 
0 #7 eduard 30.05.2013 21:07
Например, у Вас есть файл "Book1.xlsx", в котором есть имена, на которые вы хотите сослаться, например "revenue_USD". Для того чтобы в другой книге сделать ссылку на это имя напишите формулу =Book1!revenue_ USD. Эта функция очень хорошо работает, если вы ссылаетесь на конкретную ячейку (имя присвоено ячейке). Если оно присвоено диапазону, то работайте с ним как с диапазоном.
Цитировать
 
 
0 #6 anna 29.05.2013 07:11
Эдуард,
подскажите, пожалуйста. Вы пишите, что лучше использовать имена, если хотим использовать данные из других книг для отображения их в главной книги. Если просто ссылаться на данные, через равно (=), то, вроде, все нормально. Но, когда эти внешние данные, например, использую в формуле, то даже после обновления вместо имен пишется ошибка #ССЫЛКА. Ведь имена имеют область дйствия? Или это только для формул действует? И нельзя в формулах ссылаться на именнованные данные из других книг. А можно только, если непосредственно указываешь, через равно (=), что в эту ячейку хочу поместить данные из другой книги, и указываешь именно имя. Так? Или я запуталась?
Цитировать
 
 
0 #5 leonid 27.05.2013 22:24
Например: нужен общий отчёт из отдела продаж, состоящий из 3 менеджеров. Создаю 4 книги, одна из которых главная, в трёх других менеджеры заполняют свои отчёты. Главная книга состоит из 5 страниц. Первая страница - главная, в которой концентрируются все данные , все остальные вспомогательные . Вторая, третья и четвертые страницы привязаны к отчётам менеджеров через имена. Пятая страница нужна для выстраивания данных со второй, третьей и четвёртой страницы друг под другом через знак "=" и через ВПР с этой страници все данные привязываются в нужные столбцы в главную страницу. Ну и конечно же все страницы защищаются паролем, который запрещает изменять данные вручную в таблице. Но этот способ не всем подойдёт, потому что нужен индификационный номер к которому нужно привязывать ВПР на первом листе в главном файле. Мне он подходит, потому что я работаю в тендерном отделе и находя заказ я присваиваю ему номер и по этому номеру уже начинают работать и отчитываться менеджеры.
Цитировать
 
 
0 #4 leonid 24.05.2013 19:43
обновление данных с добавлением к старым, все книги будут храниться на сервере в одной папке, обновление отчётов должно происходить в режиме онлайн(т.е. как с функцией ВПР, если я в одной книге меняю данные они тут же меняются в другой книге)
Цитировать
 
 
0 #3 viktorij 23.05.2013 02:01
Леонид, а где "физически" хранятся книги каждого менеджера? С какой периодичностью планируется обновление отчетов? Обновление должно быть полным, или с добавлением новых данных к старым? Опишите процесс, "как есть".
Цитировать
 
 
0 #2 eduard 23.05.2013 02:01
При открытии только главной книги данные из закрытых книг обновить можно, но при этом часто бывают ошибки: какая-то книга не досчиталась и т.п. поэтому я рекомендую все же обновлять книги.
Что касается интерфейса для собирания данных из разных книг, я рекомендую пользоваться именами. Это очень удобно, если кто-то вставит в исходный файл какие-то строки, столбцы или удалит их, формулы не поедут.
Обновление данных можно делать просто из функциональност и Edit links to files...
Когда доберусь до дома обязательно скину ссылку со статьей на эту тему.
Цитировать
 
 
0 #1 leonid 23.05.2013 02:01
Через ВПР получается записать в один столбец данные из одной книги. А как в один столбец записать данные с нескольких книг, чтобы они обновлялись при открытие главной книги
Цитировать
 

Добавить комментарий


Защитный код
Обновить