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

Вход

Регистрация

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

 

= Мир MS Excel/Оптимизация работы со сводными таблицами - Мир MS Excel

Регистрация · Логин: · Пароль: · · Забыли пароль?
Страница 1 из 11
Модератор форума: _Boroda_, Pelena, Manyasha, SLAVICK 
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Оптимизация работы со сводными таблицами (Сводные таблицы/Pivot Table)
Оптимизация работы со сводными таблицами
harb Дата: Вторник, 19.01.2016, 23:02 | Сообщение № 1
Группа: Пользователи
Ранг: Прохожий
Сообщений: 6
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
Здравствуйте, уважаемые форумчане :)

Есть следующая задача:

Каждый месяц заполняется excel-файл, состоящий из ~20 листов (1 лист на каждый день), каждая страница которого выглядит следующим образом:



Есть название маршрута (два пункта вида А-B, B-C, C-A и т.д.), расстояние, расход в литрах и сумма.

Название маршрута и расстояние копируются из отдельной excel таблицы (около 700 уникальных маршрутов).

Расход вычисляется по формуле - расстояние * 0,14 (0,14 - расход 14 литров бензина на 100 километров)

Сумма - произведение расхода в литрах на цену бензина. (вбивается в формулу в индивидуальном порядке на каждый день)

В конце каждой таблицы значения сумм складываются в сумму за день.
На последней, ~20 странице, суммы за каждый день складываются в сумму за месяц.

В чем проблема? Рабочий процесс обычно выглядит примерно так:

Берется шаблон по неделям (excel файл с заранее созданными 20 страницами и разметкой), в котором уже есть 50% необходимых маршрутов. Остальные 50% добиваются прямо в нем. После чего, с помощью CTRL+F я ищу свежедобавленные маршруты, у которых пока отсутствует параметр расстояния. Если он есть в том самом файле, со списком всех найденных маршрутов - копирую и вставляю для него данные о расстоянии, если нет - помечаю "жирным".

После этого просматриваю все ~20 страниц, добавляю все "несуществующие" маршруты в ту самую таблицу с маршрутами и ищу для них расстояния в гугле. После чего - вновь прочесываю сводную и добавляю их обратно.

Проблема: поиск уже имеющихся маршрутов в списке из 700+ позиций даже с CTRL+F отнимает львиную долю времени, а запись "ненайденных маршрутов" подавно. Плюс - легко ошибиться в массиве одинаковых параметров.

Есть ли способ как то автоматизировать процесс? Условно, чтобы при введении названия маршрута - excel либо автоматически подбирал к нему расстояние из дополнительного файла или показывал, что данный маршрут отсутствует, а при его создании в том самом списке - автоматически бы подсасывал его?


Простите за сумбурность, если что - готов уточнить любой момент.
Буду благодарен за помощь или за полезные туториалы, если они соответствуют описанию моей проблемы
К сообщению приложен файл: 5843814.jpg(18Kb)


Сообщение отредактировал harb - Вторник, 19.01.2016, 23:04
 
Ответить
СообщениеЗдравствуйте, уважаемые форумчане :)

Есть следующая задача:

Каждый месяц заполняется excel-файл, состоящий из ~20 листов (1 лист на каждый день), каждая страница которого выглядит следующим образом:



Есть название маршрута (два пункта вида А-B, B-C, C-A и т.д.), расстояние, расход в литрах и сумма.

Название маршрута и расстояние копируются из отдельной excel таблицы (около 700 уникальных маршрутов).

Расход вычисляется по формуле - расстояние * 0,14 (0,14 - расход 14 литров бензина на 100 километров)

Сумма - произведение расхода в литрах на цену бензина. (вбивается в формулу в индивидуальном порядке на каждый день)

В конце каждой таблицы значения сумм складываются в сумму за день.
На последней, ~20 странице, суммы за каждый день складываются в сумму за месяц.

В чем проблема? Рабочий процесс обычно выглядит примерно так:

Берется шаблон по неделям (excel файл с заранее созданными 20 страницами и разметкой), в котором уже есть 50% необходимых маршрутов. Остальные 50% добиваются прямо в нем. После чего, с помощью CTRL+F я ищу свежедобавленные маршруты, у которых пока отсутствует параметр расстояния. Если он есть в том самом файле, со списком всех найденных маршрутов - копирую и вставляю для него данные о расстоянии, если нет - помечаю "жирным".

После этого просматриваю все ~20 страниц, добавляю все "несуществующие" маршруты в ту самую таблицу с маршрутами и ищу для них расстояния в гугле. После чего - вновь прочесываю сводную и добавляю их обратно.

Проблема: поиск уже имеющихся маршрутов в списке из 700+ позиций даже с CTRL+F отнимает львиную долю времени, а запись "ненайденных маршрутов" подавно. Плюс - легко ошибиться в массиве одинаковых параметров.

Есть ли способ как то автоматизировать процесс? Условно, чтобы при введении названия маршрута - excel либо автоматически подбирал к нему расстояние из дополнительного файла или показывал, что данный маршрут отсутствует, а при его создании в том самом списке - автоматически бы подсасывал его?


Простите за сумбурность, если что - готов уточнить любой момент.
Буду благодарен за помощь или за полезные туториалы, если они соответствуют описанию моей проблемы

Автор - harb
Дата добавления - 19.01.2016 в 23:02
_Boroda_ Дата: Вторник, 19.01.2016, 23:19 | Сообщение № 2
Группа: Модераторы
Ранг: Экселист
Сообщений: 9818
Репутация: 4146 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Без примера файла могу только посоветовать использовать ВПР или связку ИНДЕКС(...;ПОИСКПОЗ(, или ПРОСМОТР


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеБез примера файла могу только посоветовать использовать ВПР или связку ИНДЕКС(...;ПОИСКПОЗ(, или ПРОСМОТР

Автор - _Boroda_
Дата добавления - 19.01.2016 в 23:19
harb Дата: Вторник, 19.01.2016, 23:54 | Сообщение № 3
Группа: Пользователи
Ранг: Прохожий
Сообщений: 6
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
_Boroda_, вот примеры файлов из первого поста :)
К сообщению приложен файл: 8772211.xlsx(9Kb) · __01.05-31.05.xls(32Kb)


Сообщение отредактировал harb - Среда, 20.01.2016, 00:13
 
Ответить
Сообщение_Boroda_, вот примеры файлов из первого поста :)

Автор - harb
Дата добавления - 19.01.2016 в 23:54
harb Дата: Вторник, 19.01.2016, 23:55 | Сообщение № 4
Группа: Пользователи
Ранг: Прохожий
Сообщений: 6
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
Вот второй файл. Почему-то не добавился.
 
Ответить
СообщениеВот второй файл. Почему-то не добавился.

Автор - harb
Дата добавления - 19.01.2016 в 23:55
_Boroda_ Дата: Среда, 20.01.2016, 01:18 | Сообщение № 5
Группа: Модераторы
Ранг: Экселист
Сообщений: 9818
Репутация: 4146 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
А зачем Вам два файла? Просто добавьте в шаблон еще один лист (его можно скрыть), в котором будут ВСЕ известные Вам маршруты. Если вдруг какого-то не оказалось, то просто дописываете его туда и пользуетесь как шаблонов уже этим новым файлом.
Посмотрите а моем файле - там 4 маршрута уже есть, а на первом листе я добавил новый маршрут, которого еще нет в списке на листе "Маршруты". Он автоматически выделяется красным (с помощью Усовного форматирования на вкладке Главная). В формуле столбца J используется именованный диапазон "Маршруты". Посмотреть на него можно, нажав Контрл F3.
Еще переписал Вам формулы на листах и сделал формат ячеек
Основной;;
Ваша задача при появлении нового маршрута вручную добавить его в лист Маршруты вниз списка и поставить ему нужное расстояние. И потом, чтобы этот маршрут сохранился для следующих месяцев, Вам нужно использовать для шаблона файл с уже добавленным этим маршрутом. Или можно заносить этот новый маршрут в текуший файл и в файл-шаблон.
Таким образом, у Вас постепенно будет увеличиваться список маршрутов и когда-нибудь Вы придете к тому, что расстояния всех возможных маршрутов будут занесены в этот справочник.
К сообщению приложен файл: _01.05-31.05_1.xls(48Kb)


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеА зачем Вам два файла? Просто добавьте в шаблон еще один лист (его можно скрыть), в котором будут ВСЕ известные Вам маршруты. Если вдруг какого-то не оказалось, то просто дописываете его туда и пользуетесь как шаблонов уже этим новым файлом.
Посмотрите а моем файле - там 4 маршрута уже есть, а на первом листе я добавил новый маршрут, которого еще нет в списке на листе "Маршруты". Он автоматически выделяется красным (с помощью Усовного форматирования на вкладке Главная). В формуле столбца J используется именованный диапазон "Маршруты". Посмотреть на него можно, нажав Контрл F3.
Еще переписал Вам формулы на листах и сделал формат ячеек
Основной;;
Ваша задача при появлении нового маршрута вручную добавить его в лист Маршруты вниз списка и поставить ему нужное расстояние. И потом, чтобы этот маршрут сохранился для следующих месяцев, Вам нужно использовать для шаблона файл с уже добавленным этим маршрутом. Или можно заносить этот новый маршрут в текуший файл и в файл-шаблон.
Таким образом, у Вас постепенно будет увеличиваться список маршрутов и когда-нибудь Вы придете к тому, что расстояния всех возможных маршрутов будут занесены в этот справочник.

Автор - _Boroda_
Дата добавления - 20.01.2016 в 01:18
harb Дата: Среда, 20.01.2016, 07:01 | Сообщение № 6
Группа: Пользователи
Ранг: Прохожий
Сообщений: 6
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
цитата удалена

Спасибо, механика оказалась прозрачной и весьма рабочей! Но у меня еще остался один вопрос, смогу задать его вечером, не закрывайте, пожалуйста, тему :o
[moder]Не надо цитировать пост целиком. Это нарушение Правил форума[/moder]

Простите за нарушение, обязуюсь выполнять все впредь :D

В отчетах названия маршрутов выглядит следующим образом:

A-B
B-C
C-A
A-D
D-A

Где каждая строчка - отдельная ячейка (строка) в excel.
Но руководство требует также прикладывать вторую сводную, в которой 27 строчек компилируется в одну длинную строчку вида:

A-B-C-A-D-A (по аналогии с предыдущим примером).

И так на каждый день. Вопрос - можно ли автоматизировать процесс составления одной длинной строки с маршрутами, на основе данных из существующего отчета?
Прикладываю пример сведения:
К сообщению приложен файл: 3691322.xls(20Kb)


Сообщение отредактировал harb - Среда, 20.01.2016, 20:50
 
Ответить
Сообщениецитата удалена

Спасибо, механика оказалась прозрачной и весьма рабочей! Но у меня еще остался один вопрос, смогу задать его вечером, не закрывайте, пожалуйста, тему :o
[moder]Не надо цитировать пост целиком. Это нарушение Правил форума[/moder]

Простите за нарушение, обязуюсь выполнять все впредь :D

В отчетах названия маршрутов выглядит следующим образом:

A-B
B-C
C-A
A-D
D-A

Где каждая строчка - отдельная ячейка (строка) в excel.
Но руководство требует также прикладывать вторую сводную, в которой 27 строчек компилируется в одну длинную строчку вида:

A-B-C-A-D-A (по аналогии с предыдущим примером).

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

Автор - harb
Дата добавления - 20.01.2016 в 07:01
harb Дата: Среда, 20.01.2016, 20:51 | Сообщение № 7
Группа: Пользователи
Ранг: Прохожий
Сообщений: 6
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
Ап темы
 
Ответить
СообщениеАп темы

Автор - harb
Дата добавления - 20.01.2016 в 20:51
Pelena Дата: Среда, 20.01.2016, 22:33 | Сообщение № 8
Группа: Модераторы
Ранг: Экселист
Сообщений: 10400
Репутация: 2354 ±
Замечаний: 0% ±

Excel 2010 & Mac Excel 2011
Откуда брать данные для создания этой "длинной строчки"?
[p.s.]Никак не пойму, при чём тут сводные таблицы?[/p.s.]


"Черт возьми, Холмс! Но как??!!"
ЯД 41001765434816
 
Ответить
СообщениеОткуда брать данные для создания этой "длинной строчки"?
[p.s.]Никак не пойму, при чём тут сводные таблицы?[/p.s.]

Автор - Pelena
Дата добавления - 20.01.2016 в 22:33
harb Дата: Среда, 20.01.2016, 23:01 | Сообщение № 9
Группа: Пользователи
Ранг: Прохожий
Сообщений: 6
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
Простите, если что-то неправильно понимаю :) На работе называют этот документ "сводной", поэтому и подумал, что стоит запостить это здесь.

Данные для "длинной строчки" берутся из файла, который отредактировал ув. пользователь _Boroda_.
В общем есть 2 файла. в одном - каждый день - это отдельный лист и таблица с множеством строчек. Второй, о котором идет речь - это компилляция всех "листов" из первого документа в одну общую таблицу, по принципу 1 лист - 1 строчка. Принцип превращения 1 листа из документа первого типа в длинную строчку описал выше. На данный момент делаю это все руками, хотелось бы автоматизировать.

Простите за лютую топорность, очень надеюсь, что кто-то сможет мне с этим помочь.


Сообщение отредактировал harb - Среда, 20.01.2016, 23:02
 
Ответить
СообщениеПростите, если что-то неправильно понимаю :) На работе называют этот документ "сводной", поэтому и подумал, что стоит запостить это здесь.

Данные для "длинной строчки" берутся из файла, который отредактировал ув. пользователь _Boroda_.
В общем есть 2 файла. в одном - каждый день - это отдельный лист и таблица с множеством строчек. Второй, о котором идет речь - это компилляция всех "листов" из первого документа в одну общую таблицу, по принципу 1 лист - 1 строчка. Принцип превращения 1 листа из документа первого типа в длинную строчку описал выше. На данный момент делаю это все руками, хотелось бы автоматизировать.

Простите за лютую топорность, очень надеюсь, что кто-то сможет мне с этим помочь.

Автор - harb
Дата добавления - 20.01.2016 в 23:01
Pelena Дата: Среда, 20.01.2016, 23:09 | Сообщение № 10
Группа: Модераторы
Ранг: Экселист
Сообщений: 10400
Репутация: 2354 ±
Замечаний: 0% ±

Excel 2010 & Mac Excel 2011
Я всё равно не понимаю. Вот дата 2.11.15. Почему получилась цепочка Улица 1 - Улица 2 - Улица 3 - Улица 4 - Улица 5?
И обратный вопрос: какая цепочка должна получиться из листа 05.05.15

Сделайте более наглядные примеры, что есть и что должно получиться.

[p.s.]На мой взгляд, тут лучше макрос использовать[/p.s.]


"Черт возьми, Холмс! Но как??!!"
ЯД 41001765434816
 
Ответить
СообщениеЯ всё равно не понимаю. Вот дата 2.11.15. Почему получилась цепочка Улица 1 - Улица 2 - Улица 3 - Улица 4 - Улица 5?
И обратный вопрос: какая цепочка должна получиться из листа 05.05.15

Сделайте более наглядные примеры, что есть и что должно получиться.

[p.s.]На мой взгляд, тут лучше макрос использовать[/p.s.]

Автор - Pelena
Дата добавления - 20.01.2016 в 23:09
SLAVICK Дата: Среда, 20.01.2016, 23:32 | Сообщение № 11
Группа: Модераторы
Ранг: Старожил
Сообщений: 1922
Репутация: 650 ±
Замечаний: 0% ±

2007,2010,2013,2016
Наверное это поможет :)


Иногда все проще чем кажется с первого взгляда.
 
Ответить
СообщениеНаверное это поможет :)

Автор - SLAVICK
Дата добавления - 20.01.2016 в 23:32
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Оптимизация работы со сводными таблицами (Сводные таблицы/Pivot Table)
Страница 1 из 11
Поиск:

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