MulTEx »

Разнесение данных на разные листы/книги

Данная функция является частью надстройки MulTEx

MulTEx

Вызов команды:
MulTEx -группа Книги/Листы -Книги -Разнесение данных


Команда, делающее обратное Сбору данных с нескольких листов/книг MulTEx_Icons_Consolidation - разносит данные листа на несколько других листов либо книг. Количество листов/книг зависит от количества уникальных значений критериев для разнесения. Листы и Книги создаются при выполнении команды. Для чего это может быть нужно? Например, есть график отпусков сотрудников, который надо разбить по фамилии руководителя отдела и каждому руководителю отправить по почте список только его сотрудников:
График отпусков
Сидеть и выбирать данные каждого сотрудника на отдельные листы вручную долго и скучно. Плюс еще и отправить надо на указанный адрес. А команда Разнесение данных сама разобьет данные на листы и книги и отправит их по указанным адресам.

Основные
Разнесение данных - Основные
Разнести:

  • Все данные - при выборе будет произведено разнесение абсолютно всех данных, расположенных на листе.
  • Указанный диапазон - будут разнесены только те данные, которые расположены внутри указанного диапазона. Диапазон следует указывать без заголовка. Иначе заголовок будет определен как отдельный критерий и для него будет так же создан свой лист/книга.

Критерии:

  • На основании значений - разнесение данных происходит на основании значений в ячейках. На рисунках выше таблица разносится именно на основании значений и в качестве столбца с критериями указан столбец с фамилиями руководителей. Это значит, что после выполнения команды будет создана новая книга, содержащая ровно столько листов, сколько руководителей в таблице. И в каждом листе будут содержаться только те сотрудники, которые работают в отделе, подчиненном данному руководителю.
  • На основании цвета заливки - разнесение данных происходит на основании цвета заливки ячеек. В данном случае новым листам/книгам будет присвоено имя, равное числовому коду цвета заливки ячеек, а цвет ярлыков листов - равен цвету заливки ячеек.
  • На основании цвета шрифта - разнесение данных происходит на основании цвета шрифта. В данном случае новым листам/книгам будет присвоено имя, равное числовому коду цвета шрифта, а цвет ярлыков листов - равен цвету шрифта.
  • На фиксированное количество строк - данные будут разнесены пропорционально указанному количеству строк в окне напротив данной опции. Будут созданы листы/книги, количество строк с данными в которых будет равно указанному. При этом имена книг и листов будут называться диапазонами строк. Например, если выбрать разбиение диапазона по 10 строк, то результирующие листы или книги будут названы: 1-10, 11-20, 21-30 и т.д.

Поместить:

  • На разные листы одной книги - данные будут разнесены на разные листы одной книги. Книга создается в процессе выполнения команды. Количество листов соответствует количеству уникальных значений для разнесения. Имя каждого листа соответствует значению критерия, данные по которому занесены в этот лист. Если в качестве критерия выбрано На основании цвета заливки или На основании цвета шрифта, то именем листов будут числовые коды цвета заливки или шрифта ячеек.

    Примечание: если в значении критерия имеются символы, недопустимые в имени листа(:/?*[]), то эти символы удаляются. Если критерий содержит исключительно запрещенные символы, то они все будут удалены, а лист, на который будут помещены такие данные будет назван "_invalid_chars_".
    Если количество символов в значении превышает 30(максимально допустимое количество символов в имени листа - 31), то значение обрезается до 30 символов.

  • Каждый критерий в отдельной книге - данные будут разнесены на книги. Книги создаются в процессе выполнения команды и сохраняются в папку, указанную в поле ниже(Сохранять книги в папку). По умолчанию книги сохраняются на Рабочем столе пользователя. Количество книг соответствует количеству уникальных значений для разнесения. Имя каждой книги соответствует значению критерия, данные по которому в неё занесены. Если в качестве критерия выбрано На основании цвета заливки или На основании цвета шрифта, то именем книг будут числовые коды цвета заливки или шрифта ячеек.

    Примечание: если в значении критерия имеются символы, недопустимые в имени книги(:/?*"<>|), то эти символы удаляются. Если критерий содержит исключительно запрещенные символы, то они все будут удалены, а книга, на которую будут помещены такие данные будет названа "_invalid_chars_".
    Если количество символов в значении превышает 30, то значение обрезается до 30 символов(для большей "удобочитаемости", а так же для предотвращения ошибки, возникающей при длине пути к файлу, превышающей корректное определение файла операционной системой).

Номер столбца с критериями разнесения - указывается номер столбца, в котором расположены значения критериев для разнесения. Если на вкладке Основные выбрано Все данные - указывается номер столбца на листе. Т.е. если таблица данных расположена в диапазоне C3:G20 и критерии расположены в столбце D, то следует указать номер столбца 4. Если на вкладке Основные выбрано Указанный диапазон, то указывается номер столбца внутри выбранного диапазона. Т.е. если указан диапазон C3:G20 и критерии расположены в столбце D, то следует указать номер столбца 2.

Копировать только значения и форматы - в новые листы/книги будут скопированы только значения и форматы ячеек. Формулы будут заменены на значения, что позволяет избежать ошибочных значений #ССЫЛКА!(#REF!) при копировании формул со ссылками на другие листы.

Копировать заголовок на каждый лист - в новые листы/книги будет скопирован диапазон ячеек, указанный в поле. Диапазон для заголовков может быть расположен на любом листе любой открытой книги, а не обязательно на листе со значениями для разнесения. Рекомендуется указывать диапазон, ячейки самой нижней строки которого заполнены полностью. Это необходимо для корректного определения конца заголовка программой при вставке строк данных на лист.

Отправка
Если после разбиения данных на листы книги, необходимо отправлять созданные "нарезки" определенным адресатам - необходимо выбрать параметры рассылки на этой вкладке. Если по каким-то причинам какие-то данные не получится отправить - то будет создано сообщение, информирующее об этом.
Разнесение данных - Отправка

Отправлять создаваемые листы/книги - если установлен, то созданные листы/книги будут отправлены на указанные адреса e-mail. Адреса могут быть указаны как в самой таблице для разнесения, так и отдельным списком соответствия.

  • Брать адреса e-mail из столбца - указывается номер столбца. Если на вкладке Основные выбрано Все данные, то указывается номер столбца на листе, даже если сами данные для разнесения начинаются с 3 или любого другого столбца. Т.е. если таблица данных расположена в диапазоне C3:G20 и адреса e-mail при этом расположены в столбце G, то следует указать номер столбца 7. Если на вкладке Основные выбрано Указанный диапазон, то указывается номер столбца внутри выбранного диапазона. Т.е. если указан диапазон C3:G20 и адреса e-mail при этом расположены в столбце G, то следует указать номер столбца 5.
    В данном случае необходимо, чтобы в самой таблице для разнесения присутствовал столбец с корректными адресами e-mail, на которые необходимо отправлять созданные файлы. При этом необязательно указывать e-mail для каждой строки - достаточно, если e-mail будет записан один раз для каждого критерия.
  • Адреса по списку соответствия - при выборе данного пункта необходимо заранее подготовить список соответствия адресов e-mail критериям в таблице. На примере таблицы выше список может выглядеть так:
    Список e-mail
    При этом критерии и сами адреса e-mail должны располагаться в двух смежных столбцах: слева критерии, справа - адреса. На примере таблиц выше это столбцы B и C. Т.к. в качестве критерия разнесения на вкладке Основные выбраны были ФИО руководителя из столбца Руководитель, то в качестве списка соответствия необходимо указать диапазон B2:C5.

Отправка - в этом блоке указывается способ отправки файлов и тема письма.
Тема письма - произвольный текст, который будет указан в создаваемых письмах в качестве темы. Если не указан, то в качестве темы каждого письма будет имя отправляемого файла.

  • Отправлять средствами Excel - файлы будут отправлены почтовой программой, установленной по умолчанию. Аналогично стандартной отправке листов и книг из самого Excel:
    • Excel 2007: Кнопка Офис -Отправить(Send) -Сообщение(E-mail) -Как вложение(Send as attachment)
    • Excel 2010: Файл(File) -Сохранить и отправить(Save & Send) -Отправить(Send Using E-mail) -Как вложение(Send as attachment)
  • Отправлять через Outlook - файлы будут отправлены при помощи почтовой программы Outlook. Будет создано стандартное сообщение с темой и в каждое сообщение будет вложен свой файл.
  • Отправлять через CDO - отправка файлов данным методом не требует наличия почтовой программы на компьютере. Отправка производится при помощи CDO(Collaboration Data Objects) - библиотеки, встроенной в операционную систему Windows. Для того, чтобы её использовать необходимо лишь знать настройки почтового сервера(Яндекс, Рамблер, Mail.ru и др.).
      Выбрать шаблон - имя шаблона. Сделано для удобство выбора настроек отправки, чтобы не вбивать каждый раз одни и те же настройки. Данный метод использует те же настройки, что и команда Отправка листа/книги по почте. Поэтому если ранее в команде Отправка листа/книги по почте были созданы шаблоны для отправки через CDO - то можно использовать любой из этих шаблонов. Выбрав значение из списка остальные поля заполнятся значениями, записанными для данного шаблона. Если шаблонов нет - то данные о сервере, порте, пользователе и пароле вбиваются вручную.

      Сервер - имя сервера SMTP. SMTP - Simple Mail Transfer Protocol - простой протокол передачи почты - это сетевой протокол, предназначенный для передачи электронной почты в сетях TCP/IP. Указывается почтовый сервер. Если у вас почтовый ящик на сервисе mail.ru заканчивается на inbox.ru, list.ru или bk.ru, то соответственно меняется и адрес SMTP-сервера(smtp.inbox.ru, smtp.list.ru и smtp.bk.ru). Точные значения для настроек серверов можно на сайте поставщика услуг(Яндекс, Mail и т.п.) в описаниях настроек для Outlook и найти тот параметр, который отвечает за SMTPserver(адрес SMTP-сервера, порт, а также правила авторизации).

      Пользователь - имя пользователя. Как правило совпадает с учетной записью для входа в почту.

      Пароль - пароль для входа в почту.

      Порт - порт сервера SMTP. У большинства равен 25 или 465. Точное значения порта можно узнать только на самом сервере. Большинство из них размещают информацию по подключению почтовых программ к серверу, откуда можно узнать точные данные.

      Использовать SSL - Secure Sockets Layer. Сейчас многие почтовые серверы используют шифрование методом SSL, что необходимо учитывать при настройке отправки. Если указанный почтовый сервер использует SSL и галка не будет поставлена - с большой долей вероятности письма просто не будет отправлены сервером. Точные значения для настроек серверов можно на сайте поставщика услуг(Яндекс, Mail и т.п.) в описаниях настроек для Outlook и найти тот параметр, который отвечает за SSL.

Также см.:
Создание отдельных книг из листов текущей книги


Расскажи друзьям, если статья оказалась полезной:
Обсуждение: оставлено 10 коммент.
  1. z77797:

    Было бы здорово если б можно было выделять шапку таблицы, которая вставлялась бы в каждый лист. И еще - чтоб ширина столбцов сохранялась при разнесении.

    Разнесение на книги не работает - ошибка VBA 1004, сохранение файлов с одинаковым именем.

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

  3. Sokolik:

    "Было бы здорово если б можно было выделять шапку таблицы, которая вставлялась бы в каждый лист. И еще — чтоб ширина столбцов сохранялась при разнесении."
    в полной версии эти функции уже доступны???

  4. Роман:

    Здравствуйте . а как воспользоваться можно этим ?написано только что делает . а как применить не понятно .

  5. Роман, а что непонятного? Вроде как весь функционал расписан, а не только что делает - или Вы не все успели прочитать? :-)
    Может хотели спросить где эта команда расположена? Скачиваете надстройку MulTEx(http://www.excel-vba.ru/multex/) - все там.

  6. Роман:

    Cпасибо большое , программа супер . все работает как надо . целый день искал что то подобное.никто меня не понимал что я хочу , а тут вот на 100% нужная прога для меня , спасибо большое

  7. Ольга:

    подскажите, пожалуйста, можно ли использовать данную команду, если мне необходимо разнести данные на несколько листов по нескольким критериям? я имею в виду, что у одной строки может быть от 1 до 20 столбцов с критериями, соответственно, необходимо, чтобы эта строка попала во все листы. у вас я вижу возможность выбора только 1 столбца с критерием
    если для моей задачи можно использовать ваш макрос, то подскажите, как это сделать?

    • Только если в отдельном столбце при помощи функции СЦЕПИТЬ объединить значения этих столбцов.
      Правда, не совсем ясно: то ли в любом из 20-ти столбцов может быть критерий, то ли все 20 столбцов считается одним критерием...

      • Ольга:

        нет, имеется в виду, что в 20 разных столбцах могут быть 20 разных критериев, соответственно, если у позиции есть все 20 критериев, то при разнесении она должна попасть на 20 листов
        например:
        наименование/критерий1/критерий2/кртерий3
        стартер/камаз/маз/трактор
        генератор/камаз/__/трактор
        в данном случае результат должен быть следующий:
        3 листа - камаз, маз, трактор
        на листе камаз и трактор - обе позиции
        на листе маз - только стартер

Поделитесь своим мнением

Комментарии, не имеющие отношения к комментируемой статье, могут быть удалены без уведомления и объяснения причин. Если есть вопрос по личной проблеме - добро пожаловать на Форум


Для оформления сообщений Вы можете использовать следующие тэги:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">

Логин
Наши партнеры
Перейти
Перейти
Счетчики
Анализ сайта

Яндекс.Метрика
© 2016 Excel для всех  Войти