Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel (архив)
Страницы: 1
RSS
Как произвести мегаоптимизацию?
 
#1
09.02.2010 00:49:08
Гиганты мыслей экселевских, помогите, пожалуйста!  
 
Надо отсортировать и вывести так, чтобы в Call Classification остался только Others и Tandem для Orig. номеров начинающихся на 1xxx, 2xxx, 3xxx, 4ххх, 8ххх. Смысл, посчитать для этих вызовов Duration (sec) и кол-во для каждой группы номеров. А ещё более дальнейший смысл, получить данные и впоследствии диаграмму по кол-ву и времени исходящих вызовов по каждой группе номеров в день. Но загвоздка в том, что эти данные по каждому дню надо получить из ~210 экселевских файла со статистикой за каждый день, в каждом файле до 10 тыс. записей.
 
 
 
#2
09.02.2010 01:01:04
ser, таблицу сделали - это хорошо. Вот если бы ещё рядом показали как должно получиться (для проверки и отладки)...
 
 
 
#3
09.02.2010 01:31:56
Проверьте.
 
 
 
#4
09.02.2010 08:01:51
Я так понимаю отчеты это выгрузка из СУБД. А доступ к базе есть? Может прямой запрос к базе решит вашу проблему. Тогда не нужны 210 книг или, если это провайдер, то можно попросить его выдавать вам расшифровку в нужном формате. Просто это жуть 210 страниц получать каждый день.    
 
Вдогонку:  
А оно вам надо? Ну получили вы общее время по группам звонков, построили график,  
на звонки  1-й группы - 5 часов общего времени, кол-во звонков 1200 и по другим примерно такое же.    
Потом посчитали стоимость услуг. Узнали кто сколько звонит и кому. А дальше куда пойдут эти данные?    
Для плана? Для бюджета?  
Если да, то запланировать точно трудно, приблизительно возможно.    
Пытаетесь сэкономить на этом? Как-то тоже не очень реально.    
 
А вот сделать лимиты и потом его контролировать, вот это реально. Всё то что свыше лимита выставлять сотрудникам и вычитать из зарплаты.  
 
Но для этого не нужно каждый день считать звонки. Так что, про "мегаоптимизацию" это чуть-чуть преувеличено.  
 
Простите может за излишний интерес, но был опыт внедрения биллинга, тоже делал такие же запросы и отчеты, но  решения по ним принимались не всегда.  
 
Очень не хочется видеть работу людей на корзину.
 
 
 
#5
09.02.2010 15:47:02
{quote}{login=McCinly}{date=09.02.2010 08:01}{thema=}{post}Я так понимаю отчеты это выгрузка из СУБД. А доступ к базе есть? Может прямой запрос к базе решит вашу проблему. Тогда не нужны 210 книг или, если это провайдер, то можно попросить его выдавать вам расшифровку в нужном формате. Просто это жуть 210 страниц получать каждый день.    
 
Вдогонку:  
А оно вам надо? Ну получили вы общее время по группам звонков, построили график,  
на звонки  1-й группы - 5 часов общего времени, кол-во звонков 1200 и по другим примерно такое же.    
Потом посчитали стоимость услуг. Узнали кто сколько звонит и кому. А дальше куда пойдут эти данные?    
Для плана? Для бюджета?  
Если да, то запланировать точно трудно, приблизительно возможно.    
Пытаетесь сэкономить на этом? Как-то тоже не очень реально.    
 
А вот сделать лимиты и потом его контролировать, вот это реально. Всё то что свыше лимита выставлять сотрудникам и вычитать из зарплаты.  
 
Но для этого не нужно каждый день считать звонки. Так что, про "мегаоптимизацию" это чуть-чуть преувеличено.  
 
Простите может за излишний интерес, но был опыт внедрения биллинга, тоже делал такие же запросы и отчеты, но  решения по ним принимались не всегда.  
 
Очень не хочется видеть работу людей на корзину.{/post}{/quote}  
 
Юрий М, спасибо!  
 
Прошу прощения, что сразу не сделал в таблице какой итог хотелось бы видеть. Исправляюсь.  
 
McCinly, согласен, лучше было бы подцепиться к БД и с помощью запросов получать необходимые данные, но так получилось, что доступа к базе нет, а есть 210 экселевских файлов, один файл, это лог за день (а не 210 файлов ежедневно). На первом этапе хотелось бы проанализировать по времени кол-во звонков и время по группам. На втором этапе уже буду думать, как вычленять подгруппы и т.д.  
Вся соль в том, что каждая группа, это разная компания и кто-то звонит больше, кто-то меньше, поэтому для внутренних расчётов это полезнейшая информация. Поэтому в стратегическом контексте, это можно было бы назвать мегаоптимизацией))  
 
Спасибо, что откликнулись!
 
 
 
#6
09.02.2010 15:53:41
А что не так в моём примере?
 
 
 
#7
09.02.2010 18:18:22
{quote}{login=Юрий М}{date=09.02.2010 03:53}{thema=}{post}А что не так в моём примере?{/post}{/quote}  
У Вас по Others и Tandem сортирует, а хотелось бы ещё по группам 1xxx, 2xxx, 3xxx, 4ххх, 8ххх информацию получать. См. в предыдущем посте вложение.
 
 
 
#8
09.02.2010 18:35:11
Объясните, почему в Вашем последнем примере не следует учитывать строку 25? Там значение Tandem и номер начинается с восьмёрки.  
P.S. Очень оперативно отвечаете :-)
 
 
 
#9
09.02.2010 19:24:54
{quote}{login=Юрий М}{date=09.02.2010 06:35}{thema=}{post}Объясните, почему в Вашем последнем примере не следует учитывать строку 25? Там значение Tandem и номер начинается с восьмёрки.  
P.S. Очень оперативно отвечаете :-){/post}{/quote}  
Потому что нас интересуют только звонки с четырёхзначных номеров. Если Orig. больше четырёх цифр, то эти звонки можно не учитывать, будь они Incoming или Tandem.  
Сорри, не могу часто заходить на сайт((
 
 
 
#10
09.02.2010 19:36:01
Про звонки только с четырёхзначных номеров впервые слышу - изначально говорилось только про первую цифру. Строка "начинающихся на 1xxx, 2xxx, 3xxx, 4ххх, 8ххх" ничего не говорит о формате. Ключевое слово - начинающихся. Посмотрю попозже.
 
 
 
#11
09.02.2010 19:43:47
{quote}{login=Юрий М}{date=09.02.2010 07:36}{thema=}{post}Про звонки только с четырёхзначных номеров впервые слышу - изначально говорилось только про первую цифру. Строка "начинающихся на 1xxx, 2xxx, 3xxx, 4ххх, 8ххх" ничего не говорит о формате. Ключевое слово - начинающихся. Посмотрю попозже.{/post}{/quote}  
Я подумал, что, к примеру, 1ххх, т.е. 4-значный номер, будет понятно))  
 
Спасибо!
 
 
 
#12
09.02.2010 22:54:24
Имена файлов какие?    
 
Может сделаю сводную запросом. В MS Query  
 
SELECT `Лист1$`.`Orig#`, `Лист1$`.`Dest#`, `Лист1$`.`Call Classification`, `Лист1$`.`Duration (sec)`  
FROM `D:\Имя_Первого_файла.xls`.`Лист1$` `Лист1$`  
WHERE (`Лист1$`.`Call Classification`='Others') AND (`Лист1$`.`Orig#`<10000) OR (`Лист1$`.`Call Classification`='Tandem') AND (`Лист1$`.`Orig#`<10000)  
ORDER BY `Лист1$`.`Orig#`  
UNION ALL  
SELECT `Лист1$`.`Orig#`, `Лист1$`.`Dest#`, `Лист1$`.`Call Classification`, `Лист1$`.`Duration (sec)`  
FROM `D:\Имя_Второго_файла.xls`.`Лист1$` `Лист1$`  
WHERE (`Лист1$`.`Call Classification`='Others') AND (`Лист1$`.`Orig#`<10000) OR (`Лист1$`.`Call Classification`='Tandem') AND (`Лист1$`.`Orig#`<10000)  
ORDER BY `Лист1$`.`Orig#`  
UNION ALL    
и т.д.    
 
Получите список на его основе сводную с суммой, количеством и мегаоптимизацией.  
 
ЗЫ 48444 )))  
 
Если есть желание можете прислать файлы в архиве на моё мыло, вышлю сводную на основе запроса. У вас какой офис? у меня 2007 и 2003, но предпочитаю 2007.
 
 
 
#13
09.02.2010 23:08:34
Вот файлик, правда пока запрос к одной таблице.
 
 
 
#14
10.02.2010 01:51:48
Проверьте. Таблицу увеличил путём простого копирования - нужно было для проверки. Отчёт формируется на втором листе. Таблица каждый раз очищается. Пришлось делать многократный проход циклом - на больших объёмах может заметно увеличиться время обработки.
 
 
 
#15
18.02.2010 17:25:18
{quote}{login=Юрий М}{date=10.02.2010 01:51}{thema=}{post}Проверьте. Таблицу увеличил путём простого копирования - нужно было для проверки. Отчёт формируется на втором листе. Таблица каждый раз очищается. Пришлось делать многократный проход циклом - на больших объёмах может заметно увеличиться время обработки.{/post}{/quote}  
Юрий М, это просто гениально! Спасибо большое!  
 
8000 записей обрабатывает ~ за 15 сек.))  
 
- а можно спросить, возможно ли ещё разбить группу 2ххх на: 25хх выводить отдельно, а также 2ххх также выводить отдельно, но предварительно из этой группы удалив 25хх?  
- и было бы вообще на грани фантастики, если бы из этого всего получилась результатная информация, как пример, на листе 3.
 
 
 
#16
19.02.2010 02:46:34
Арифметику Вам придётся самому проверить.
 
 
 
#17
19.02.2010 22:42:14
{quote}{login=Юрий М}{date=19.02.2010 02:46}{thema=}{post}Арифметику Вам придётся самому проверить.{/post}{/quote} Юрий М, слов нет, просто фантастика! Всё работает! Только в группе 8ххх постоянно упускается 1 звонок и соответственно его время не добавляется к длительности для данной группы, и при занесении расчётов во второй лист, если в первом листе отсутствует, к примеру, группа 3ххх вылетает ошибка и ссылается на строку <BR>.Cells(30, 4) = .Cells(30, 2) / .Cells(30, 3)<BR> на всякий случай такой вариант во вложении, если профессиональный интерес возникнет к своему творчеству)) А так всё преидеальнейшим образом организовано! Ещё бы к этому прилепить идентифицирование по коду города и стоимость мин. провайдера по направлениям, то получилась бы практически биллинговая система! Но это уже другая история)) <BR><STRONG>Файл удален</STRONG> - велик размер. [Модераторы]
 
 
 
#18
19.02.2010 22:54:45
Приложили неудачный пример:  
1. Велик размер файла - я его удалю.  
2. По Tandem нет ни одного четырёхзначного.  
В следующем примере покажите строку, которая не обсчитыается в подгруппе 8.  
Следите за размером файла - можно уменьшить к-во строк и/или очистить таблицы с отчётами.
 
 
 
#19
21.02.2010 01:35:12
Проверьте. По группе 8ххх пропусков не обнаружил.
 
 
 
#20
21.02.2010 03:43:59
{quote}{login=Юрий М}{date=21.02.2010 01:35}{thema=}{post}Проверьте. По группе 8ххх пропусков не обнаружил.{/post}{/quote}  
 
Юрий М, прошу прощения, в запале упустил момент с размером файла))  
 
Смотрите, сейчас у нас всего 3 звонка сделанные из группы 8ххх. В лист 2 заносится правильная информация, а вот в результирующий лист 3 в таблицу Итого Others для группы 8ххх, попадает только 2 первых звонка, хотя в Итого Others & Tandem попадает правильная информация о 3 звонках.  
 
А также сразу не заметил, у нас не выводится сумма по группе 8ххх в листе 2, а сразу идёт Total Others, кстати, считающаяся абсолютно правильно, т.е. учитывающая длительность группы 8ххх.  
Можно ли перед Total Others добавить Subtotal Group # 5?
 
 
 
#21
21.02.2010 04:24:32
Проверьте.
 
 
 
#22
21.02.2010 18:31:55
Добавил группу 5ххх. Необходимо проверить на различных комбинациях исходных данных. Надеюсь, Вы не вспомните, что ещё нужны группы 6, 7... :-)
 
 
 
#23
24.02.2010 05:28:43
{quote}{login=Юрий М}{date=21.02.2010 06:31}{thema=}{post}Добавил группу 5ххх. Необходимо проверить на различных комбинациях исходных данных. Надеюсь, Вы не вспомните, что ещё нужны группы 6, 7... :-){/post}{/quote}  
Юрий М, в который раз, огромное спасибо! Для меня это магия какая-то, я восхищён тем, что Вы сделали!))  
 
Не сочтите ещё одну просьбу за наглость, но раз уж "мегаоптимизация", то хотелось бы начатое довести до ума. Сразу всё не описывал, потому что не думал, что кто-то сможет помочь, а сейчас, появились проблески надежды.  
 
Как Вы поняли, это логи с телефонной станции. Данной станцией пользуются 5 групп (1xxx, 2ххх, 3ххх, 4ххх, 8ххх), а также существует общий отдел, который занимается приёмом входящих звонков 25хх и есть ещё группа служебных звонков 98хх.  
 
На станции существуют следующие типы звонков, т.е. Call Classification:  
1) Others - исходящие звонки, кроме внутренних: когда Orig. - хххх внутренний номер, а Dest. - всегда как минимум 7-значный городской номер.  
2) Tandem - факсимильные соединения:    
а) исходящие - когда Orig. - хххх внутренний номер, а Dest. - всегда как минимум 7-значный городской номер  
б) входящие - когда Orig. - всегда как минимум 7-значный городской номер, а Dest. - хххх внутренний номер.  
3) Incoming - входящие звонки, извне: когда Orig. - всегда как минимум 7-значный городской номер, а Dest. - хххх внутренний номер.  
4) Internal - звонки только с участием хххх внутренних номеров (т.е. внутренние соединения в и между группами).  
 
Вашим чудо подсчётом удалось решить вопрос с 1) и 2)а) типами звонков, т.е. со всеми видами исходящих звонков во внешний мир. Это очень полезная информация, которой будут ежедневно следовать, но для детального изучения нужно что-то более подробное.  
Хотелось бы все вышеописанные типы звонков чуть более детализировать и выводить их кол-во и длительность почасно в сутки для каждой группы и отдельно для каждого номера из диапазона 25хх.  
 
Посмотрите, пожалуйста, в файл во вложении я добавил листы 4 и 5 с формами, которые как мне кажется и будут итогом той самой мегаоптимизации (хотя и кажутся громоздкими, но я уже не знаю как более консолидировать и упростить), т.к. позволят строить графики по нагрузке, рассчитать кол-во соединительных линий необходимых для связи с внешним миром, рассчитать оборот факсимильных сообщений, свериться с расчётами провайдера и отследить работу отдела по приёму звонков, а также следить за особо разговорчивыми сотрудниками (если удастся в листе2 отсортировать столбцы в пределах групп по длительности)).  
 
Если получится это реализовать, то это будет поистине верхом гениальности и профессионализма!  
На всякий случай здесь полный лог http://www.rapidshare.ru/1390100, во вложении всего четверть.
 
 
 
#24
24.02.2010 19:36:05
Ну у Вас и аппетит... Полагаю, что помог Вам уже достаточно.
 
 
 
#25
24.02.2010 20:18:40
{quote}{login=Юрий М}{date=24.02.2010 07:36}{thema=}{post}Ну у Вас и аппетит... Полагаю, что помог Вам уже достаточно.{/post}{/quote}  
Конечно помогли очень здорово! Ещё раз спасибо!  
А если вышеописанное за небольшое денежное вознаграждение?))  
Я бы с удовольствием сам сделал, но, к сожалению, не программист я((
 
 
 
#26
25.02.2010 13:09:58
ser, у нас тут на Форуме живет хороший специалист - EducatedFool. Не обращайте внимание на ник - это он так прикалывается :-). Думаю, что он это сделает быстрее и более качественно, чем я. Надеюсь, он заглянет в тему и отзовётся. Если нет - в его подписи есть адрес, где его можно найти.
 
 
 
#27
25.02.2010 13:36:10
Не знаю, Юра, смогу ли я сделать это быстрее - аппетиты у человека растут быстрее, чем пишется код)))  
 
 
Но, тем не менее, могу попробовать реализовать все вышеизложенные пожелания.  
Конечно, за денежное вознаграждение (а не "небольшое денежное вознаграждение")  
 
 
2 ser  
Пишите (в теме, или в личку - контакты в подписи), сколько Вы готовы заплатить за работу - посмотрим, стОит ли вообще за это браться.  
 
 
PS: По поводу фразы:  
> А если вышеописанное за небольшое денежное вознаграждение?))  
 
Вы, скорее всего, хотели написать "А если НИЖЕОПИСАННОЕ за небольшое денежное вознаграждение?"  
На мой взгляд, вам стоило бы вознаградить Юру за макрос из 430 (!) строк кода.
Надстройки для Excel  Парсинг сайтов  Мониторинг цен конкурентов
 
 
 
Страницы: 1
Читают тему
Наверх