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

Вход

Регистрация

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

 

= Мир MS Excel/Статьи об Excel

МЕНЮ САЙТА
  • 1
  • 2
  • 3

КАТЕГОРИИ РАЗДЕЛА

ОПРОСЫ
Какой версией Excel Вы пользуетесь?
Всего ответов: 51798
Главная » Статьи » Эффективная работа в Excel » Интеграция Excel с другими приложениями

Слияние данных MS Excel и MS Word
Часто данные электронной таблицы требуется использовать при составлении различных документов: отчетов, писем, договоров и т.д. В этом случае удобно использовать слияние данных MS Excel и MS Word.

Процедура слияния состоит из нескольких этапов.

1 этап. Подготовка данных электронной таблицы
Таблица, предназначенная для слияния, должна удовлетворять некоторым требованиям:
  • в таблице не должно быть объединенных ячеек. Вернее сказать так: ЕСЛИ в таблице есть объединённые ячейки, то надо быть готовым к тому, что при экспорте объединение будет отменено, и соответственно образуются лишние пустые строки и/или столбцы, что может нарушить структуру таблицы. В общем, объединённые ячейки - это зло :)
  • все столбцы должны иметь уникальные названия, которые будут использоваться при слиянии. Если в таблице отсутствует первая строка с названиями столбцов, то её заменит первая строка данных, а значит, она в рассылке участвовать не будет.
В качестве примера возьмем таблицу с перечнем клиентов фитнес клуба "Экселент"



2 этап. Подготовка шаблона документа Word
На этом этапе в текстовом редакторе Word формируется документ, в который в дальнейшем будут внедряться данные электронной таблицы. Текст этого документа представляет собой общую для всех рассылок часть.

Предположим всем клиентам, у которых срок действия клубной карты истекает в следующем месяце, планируется разослать письма с уведомлением.
Текст письма будет одинаковым за исключением обращения, номера клубной карты и даты окончания её действия. Эти данные будут импортироваться из таблицы Excel (выделено синим)



Таким образом, на этом этапе в документе Word печатается общий для всех писем текст.

Для более удобной дальнейшей работы при слиянии рекомендуется установить параметр Затенение полей в положение Всегда, чтобы отличать вставленные поля слияния от обычного текста. Если этот параметр включен, поля отображаются на сером фоне. На печать этот фон, естественно, не выводится.

3 этап. Работа Мастера слияния MS Word
Открываем файл письма в MS Word.
Проще всего осуществить слияние данных, следуя указаниям Мастера слияния. В версиях после Word2003 Мастер слияния запускается с помощью кнопки Начать слияние на вкладке Рассылки
В версиях до Word2007 следует выполнить команду меню Сервис -- Письма и рассылки -- Слияние. Кроме того, для более удобной работы версиях до Word2007 можно вывести панель инструментов Слияние

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

Работа Мастера слияния включает 6 шагов.
1 шаг: выбираем тип документа для рассылки, в нашем случае это Письма

2 шаг: выбираем документ, на основе которого будут создаваться рассылки, им может быть открытый текущий документ, шаблон или существующий документ. При выборе варианта Шаблон или Существующий документ появляется возможность указать нужный файл в Проводнике. Мы выбираем Текущий документ

3 шаг: выбираем получателей. В нашем случае источником данных будет таблица Excel, значит отмечаем вариант Использование списка. Затем с помощью кнопки Обзор... выбираем нужный файл в Проводнике

После выбора файла раскрывается диалоговое окно с выбранной таблицей. Если нам нужны все записи, то сразу нажимаем ОК. При необходимости можно список отсортировать, отфильтровать нужные записи, либо найти их с помощью соответствующих команд. Возможности фильтрации и поиска здесь, конечно, намного беднее, чем в Excel, но сделать простейшую выборку по текстовым или числовым значениям можно. Кроме того, возможно выбрать записи для рассылки вручную с помощью флажков :)

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

4 шаг: вставляем нужные поля в документ. Прежде, чем выбрать один из предложенных Мастером вариантов работы, следует установить курсор в тексте туда, куда Вы хотите вставить данные. Если Вы забыли это сделать, тоже ничего страшного, поля можно вставить в любое место документа, а затем перенести. В нашем случае ставим курсор после слова "Уважаем" перед восклицательным знаком. Так как нам нужны отдельные поля, выбираем Другие элементы...

Раскрывается диалоговое окно для выбора полей слияния.


Выбираем поле Имя, нажимаем Вставить, то же самое для поля Отчество. Закрываем окно Вставка полей слияния и добавляем пробелы между вставленными полями. Если параметр Затенение полей установлен в положение Всегда, то вставленные поля будут отчетливо видны на сером фоне. Устанавливаем курсор после №, снова нажимаем ссылку Другие элементы..., выбираем № клубной карты - Вставить. Аналогично вставляем поле Дата окончания действия карты

Кроме указанных выше полей требуется вставить окончание обращения ый(ая), которое зависит от значения поля Пол. Для этого воспользуемся  специальным полем, позволяющим вставлять одно из двух значений в зависимости от данных. Поставим курсор сразу после слова "Уважаем", нажмём кнопку Правила на вкладке Рассылки и выберем вариант IF...THEN...ELSE. В версиях до Word2007 аналогичная кнопка называется Добавить поле Word и находится на панели инструментов Слияние

В раскрывшемся диалоговом окне зададим параметры


После нажатия ОК, получим результат


5 шаг: просматриваем получившиеся письма, используя кнопки навигации. На этом шаге также можно изменить список получателей, применив фильтр или убрав флажки. Так как мы применили фильтр ранее, получателей осталось 3.

Присмотревшись повнимательней к полученному результату, видим, что он не вполне соответствует нашим ожиданиям

Номер клубной карты вместо 001768 отображается как 1768, а дата и вовсе не по-нашему: сначала месяц, а потом день, хотя в таблице Excel всё было в порядке. Такие же неприятности могут возникнуть при импорте ячеек с десятичными числами, денежным форматом и т.д. Вывод неутешительный: при слиянии форматирование чисел и дат не сохраняется. Но выход есть!

Поля документа Word, в которые вставляются наши данные, представляют собой код, который, во-первых, можно посмотреть, а во-вторых, изменить. Чтобы увидеть код поля, например, с номером клубной карты, следует кликнуть по нему правой кнопкой мыши (ПКМ) и выбрать команду Коды/Значения полей.
Получим следующее 
{ MERGEFIELD "M__клубной_карты" }
Сам код изменять мы не будем, а вот формат допишем. Принцип добавления формата будет понятен любому, кто хоть немного знаком с созданием пользовательского формата в Excel. Чтобы число всегда состояло из шести цифр, формат должен состоять из шести нулей:
{ MERGEFIELD "M__клубной_карты"  \#  "000000"  }. Теперь снова ПКМ по полю -- Обновить поле, и видим число в нужном формате.

Аналогично поступаем с датой 
{ MERGEFIELD "дата_окончания_действия_карты"  \@  "DD.MM.YYYY"  }

И получаем окончательный вариант


Подробнее о кодах полей Word можно прочитать в справочной системе Word или на официальном сайте Microsoft

6 шаг: выбираем вариант Изменить часть писем... и просматриваем получившийся документ, при необходимости его можно отредактировать, сохранить как отдельный файл или отправить на печать




       
*если в файле с базой клиентов при открытии в Excel2003 вместо формул будут отображаться ошибки #ИМЯ!, подключите надстройку Пакет анализа (Сервис -- Надстройки)

Категория: Интеграция Excel с другими приложениями | Добавил: Pelena (22.11.2012)
Просмотров: 237727 | Комментарии: 62 | Теги: рассылки, Слияние данных | Рейтинг: 4.7/14


Всего комментариев: 61« 1 2 3 »
0  
26    Pelena   (27.01.2015 09:13) [ Материал]
   Пришлите кусок файла Excel и документ мне на почту, она есть на Глваной странице сайта в контактах

0   Спам
27    rvshestakov   (27.01.2015 09:32) [ Материал]
   Благодарю за помощь!

0   Спам
28    smihailov   (12.02.2015 16:20) [ Материал]
   Здравствуйте!
Как сделать слияние с шаблоном WORD данные для которого расположены на различных страницах таблицы EXCEL?

0  
29    Pelena   (12.02.2015 16:33) [ Материал]
   Здравствуйте. Для слияния данные должны быть оформлены в виде одной таблицы (базы данных). Если данные расположены вразброс, то надо использовать другие способы. Задайте вопрос на форуме, приложив пример

0   Спам
30    smihailov   (10.03.2015 17:50) [ Материал]
   Здравствуйте, Pelena!
Вы правильно пишите "..надо использовать другие способы", хотелось бы узнать какие, подскажите пожалуйста.
Пример простой: 1-й лист - список клиентов и их адреса, 2-й лист - список клиентов и их банковские реквизиты и т.д.
Как выполнить слияние шаблона Word и данных по клиенту, но взятых с разных страниц таблицы Exel?
Выполнить слияние с одним листом проблем нет, но таблица получается очень широкая и неудобно её редактировать, вот и пришла мысль разбить данные по листам.
Если есть какое-то решение моей проблемы подскажите пож.

0  
31    Pelena   (10.03.2015 20:03) [ Материал]
   Сделайте общий лист, подтяните туда формулами данные с нужных листов и используйте его в качестве источника слияния. Редактировать будете на одном листе, а слияние делать с другого.
Повторюсь: Excel не позволяет выбрать для слияния несколько листов

0   Спам
32    marysabel   (16.04.2015 00:35) [ Материал]
   Здравствуйте, Pelena!
Ситуация такая же, как и описанная в 26 посте... В таблице формат даты - дд.мм.гггг, в шаблоне меняется на мм/дд/гг... Не подскажете, как проблема у rvshestakov решилась?

0  
33    Pelena   (16.04.2015 07:41) [ Материал]
   Там была другая проблема, она решилась при создании копии файла и заново проведенным слиянием. По Вашему вопросу: если формат \@ "dd.MM.yyyy" не помогает, задайте вопрос на форуме, приложив файлы с примерами

0   Спам
34    rinalaz   (01.06.2015 16:11) [ Материал]
   Здравствуйте! Очень нужная тема. Но у меня два вопроса:
1 вопрос: если делаю отправку "как вложение", то отправляется совершенно пустое письмо только с вложением. Как сделать чтобы было и вложение и текст (хотя бы всем одинаковый?) ? А возможно ли сделать рассылку как текст электронного письма, но при этом с двумя-тремя одинаковыми файлами?
2 вопрос - особенно актуальный. если рассылка как вложение - работает. Если рассылка электронных писем как текста -работает. Но если рассылка электронных писем, а на последнем самом шаге выбираем как HTML (мне очень надо - там ссылки) - не работает! вообще никакой реакции у word и у Outlook ! Помогите пожалуйста, в чем может быть дело?

0  
35    Pelena   (01.06.2015 16:32) [ Материал]
   Я с Outlook не работаю. Вам лучше задать вопрос на форуме в соответствующем разделе

0   Спам
36    rinalaz   (01.06.2015 16:33) [ Материал]
   так это из word рассылка то идет....

0  
37    Pelena   (01.06.2015 16:44) [ Материал]
   Word формирует документ, а рассылкой занимается почтовая программа

0   Спам
38    rinalaz   (01.06.2015 16:46) [ Материал]
   как я понимаю, что именно word и не формирует в данном случае...и поэтому дальше отправки не идет. Ведь если "как тест", то все идет.

0   Спам
39    uekz   (11.11.2015 11:24) [ Материал]
   Здравствуйте! У меня при слиянии неправильно вставляются цифры Например: 0,0490 вставляется 4,9000000000000002E-2. Почему? Хотя некоторые цифры показываются правильно

0  
40    Pelena   (11.11.2015 12:50) [ Материал]
   Здравствуйте. Видимо, у вас значение рассчитывается формулой. Используйте формат. В статье есть пример изменения формата, только Вам надо не "0000000", а "0,0000", например. Подробнее про пользовательские форматы можно почитать в статье.

0   Спам
41    -svetlyak-   (19.01.2016 15:02) [ Материал]
   Добрый день!
Увидела волнующий меня вопрос, но ответа на него так и не нашла. У меня проблема как в посте 32. При слиянии число и месяц меняются местами. Т.е. в исходной таблице в ячейке дата стоит 25.01.2016, а при слиянии получается 01/25/2016. Где именно в настройках это можно изменить? Спасибо!

0  
42    Pelena   (19.01.2016 15:17) [ Материал]
   Здравствуйте. Эта проблема решается заданием формата поля. Прочитайте внимательно в статье 5 шаг, там есть ответ на Ваш вопрос

0   Спам
43    -svetlyak-   (19.01.2016 15:33) [ Материал]
   Спасибо огромное! Теперь всё поняла! Попробовала. Получилось! Ещё раз спасибо!

0   Спам
44    Рамиля   (16.03.2016 15:49) [ Материал]
   При выборе "Вставить поле" вместо <<Имя>> (да и вместо всех других полей), как у Вас в примере, у меня сразу вставляется код { MERGEFIELD "Имя"}. И просмотр слияния не дает результата. Результаты слияния я вижу только после выбора "Найти и объединить" в виде отдельно созданных писем. Почему, подскажите?

+1  
45    Pelena   (16.03.2016 16:37) [ Материал]
   Проверьте, возможно, в параметрах Word установлена галка Показывать коды полей

0   Спам
46    Огерка   (27.05.2016 22:01) [ Материал]
   Спасибо за статью! Все отлично работает при стандартном решении.
Столкнулась с такой проблемой: мне нужно распечатать результат слияния не на отдельном листе каждый клиент, а несколько клиентов на одном листе. Т.е. письмо информационное, короткое, не хочется тратить много бумаги. Хочу распечатывать по 5-6 писем на одном листе. Можно ли это осуществить? Помогите, плиз!

+1  
47    Pelena   (27.05.2016 22:29) [ Материал]
   В Мастере слияния выберите не Письмо, а Наклейки. Вот тут есть пример http://www.excelworld.ru/forum/2-2986-32404-16-1354892876

0   Спам
48    Tagir   (07.12.2016 14:10) [ Материал]
   Мира вашему дому. Подскажите пжлста как задать условие в ворде для добавления из таблицы экселя двух или трех строк с разными данными ? Необходимо создать договор в котором могут присутствовать несколько жильцов одной квартиры(я пытался создать примерно такое условие { IF {mergefield кол.} = «NEXT» {mergefield кол.} "добавляется шаблон с данными из следующей строки" "иначе пусто" } но ворд выдает ошибку что в полях нельзя указывать правила next nextif skipif). Может можно решить проблему другими путями офиса ?

0  
49    Pelena   (07.12.2016 14:14) [ Материал]
   Думаю, что непосредственно Слиянием никак. Макросом, наверное, можно. Задайте вопрос на форуме

0   Спам
50    Tagir   (08.12.2016 10:27) [ Материал]
   Где именно можете подсказать ?

1-25 26-50 51-61
Добавлять комментарии могут только зарегистрированные пользователи.
[ Регистрация | Вход ]
jooble Яндекс цитирования
© 2010-2020 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!