سبد خرید
0

سبد خرید شما خالی است.

تمام دسته ها
  • تمام دسته ها
  • Power BI
  • Power Pivot
  • Power Query
  • ابزارها
  • افزونه ها
  • اکسل مدیا
  • توابع اکسل
  • دسته‌بندی نشده
  • دیده بان
  • گوگل شیت
  • مسائل کاربردی
  • معرفی کتاب
  • نمودار ها
  • وی بی - ماکرو

ترکیب Mail Merge ورد و اکسل

ابزار Mail Merge
۴/۵ - (۶ امتیاز)

ابزار Mail Merge در ورد

وقتی میخوایم سندی رو با داده های متغیر و به تعداد زیاد تهیه کنیم یکی از بهترین راه ها استفاده از Mail Merge است. این ابزار در نرم افزار Word وجود داره که میتونه وصل بشه به اکسل و داده های متغیر رو فراخوانی کنه و سند مورد نظر رو تولید کنه. کارهایی مشابه موارد زیر رو میتونیم با استفاده از این ابزار انجام بدیم:

  • صدور یک مدرک آموزشی برای صد نفر از شرکت کنندگان
  • صدور کارنامه برای دانش آموزان یک مدرسه
  • صدور فیش حقوقی
  • صدور دعوتنامه، نامه و …
  • صدور فاکتور برای مشتریان مجموعه و …

این مقاله طی ۲ بخش ارائه میشه:

  1. معرفی و نحوه استفاده از Mail Merge و ارتباطش با اکسل
  2. حل مشکل فرمت دهی داده ها در Word

بخش اول: معرفی و نحوه استفاده از Mail Merge و ارتباطش با اکسل

فرض کنید میخواهیم فاکتورهای مشتریانمون رو به آدرس های اونها پست کنیم. پاکت های نامه رو آماده کردیم و میخوایم برای هر مشتری یک لیبل که شامل اطلاعات گیرنده مثل اسم، آدرس و سایر اطلاعات هر مشتری هست آماده کنیم. برای تهیه و چاپ این لیبل (که بعدا بچسبونیم روی پاکت نامه) از این ابزار استفاده میکنیم و با چند کلیک همه لیبل های مورد نیاز صادر میشه.

برای استفاده از این ابزار به چه چیزی نیاز داریم؟

  • دیتابیس داده های متغیر ( در این مثال اسم، آدرس، تلفن و … – مشابه شکل ۱)
  • تمپلیت سند مورد نظر در Word ( در این مثال فرمت خام پاکت برای قسمت گیرنده – مشابه شکل ۲)

Mail Merge  نمونه دیتابیس از اطلاعات متغیر سند مورد نظر

شکل ۱ – Mail Merge  نمونه دیتابیس از اطلاعات متغیر سند مورد نظر

Mail Merge نمونه خام سند مورد نظر (در اینجا پاکت نامه)

شکل ۲ – Mail Merge- نمونه خام سند مورد نظر (در اینجا پاکت نامه)

نکته:
این سند (فرم خام) میتونه تمپلیت یک نامه دعوت به مناقصه، فیش حقوقی، کارت ورود به جلسه و …. باشه. منطق کار برای همه این اسناد مشابه هست.

 

در ادامه نحوه استفاده از این ابزار و صدور همه لیبل های مورد نظر رو شرح میدیم.

بعد از  آماده کردن دیتابیس داده های متغیر و تمپلیت سند مورد نظر، فایل تمپلیت لیبل مورد نظر رو باز میکنیم و از تب Mailings روی گزینه Start Mail Merge کلیک میکنیم.

از منوی باز شده، روی گزینه Step-by-Step Mail Merge Wizard کلیک میکنیم.

انتخاب ویزارد در Mail Merge

شکل ۳- Mail Merge – انتخاب ویزارد

نکته:
داده های متغیر میتونه داخل خود این ابزار نوشته بشه و وارد بشه. اما به دلیل راحتی و امکان انجام محاسبات در فایل اکسل و استفاده از نتایج در نامه ها و … از اکسل استفاده میکنیم.

 

همچنین در نرم افزار ورد یک سری  تمپلیت مثل نامه، ایمیل، برچسب و … وجود داره که ساختار مشخصی دارن و قابل استفاده هستن. اما ما در اینجا از تمپلیت خودمون استفاده کردیم که نشون بدیم هیچ محدودیتی برای فایل ورد وجود نداره و هر ساختاری میتونه داشته باشه.

با زدن این گزینه ویزارد Mail Merge در سمت راست صفحه باز میشه و ۶ گام وجود داره که باید طی بشه.

گام اول: انتخاب سند مورد نظر (در اینجا Letters رو انتخاب میکنیم که بتونیم هر ساختاری رو تعیین کنیم و Next رو میزنیم)

ویزارد گام اول

شکل ۴ – Mail Merge – ویزارد گام اول

گام دوم: در نوار نمایش داده شده از ما میخواد که تمپلیت نامه مورد نظر رو مشخص کنیم. چون ما از ابتدا فایل تمپلیت رو باز کردیم، گزینه Use the current document رو انتخاب میکنیم و Next رو میزنیم.

ویزارد گام دوم

شکل ۵ Mail Merge – ویزارد گام دوم

گام سوم: در این مرحله باید فایل داده های متغیر در نامه رو معرفی کنیم. روی Browse کلیک میکنیم و فایل اکسل دیتابیس رو انتخاب میکنیم. بعد از انتخاب فایل اکسل و انتخاب شیت مورد نظر از فایل، پنجره شکل ۶ نمایش داده میشه. OK رو میزنیم و به گام بعد میریم.

گام سوم، تعیین فایل داده های متغیر

شکل ۶- Mail Merge- گام سوم، تعیین فایل داده های متغیر

در این قسمت میتونیم افرادی رو که نمیخوایم از تغییر فیلتر، مشخص کنیم، یا اینکه داده ها رو مرتب کنیم و تکراری ها رو حذف کنیم و …

 

گام چهارم: در گام چهارم از پنجره نمایش داده شده باید داده های متغیر در متن نامه رو مشخص کنیم برای این کار روی گزینه More Items کلیک میکنیم.

گام چهارم تعیین داده های متغیر

شکل ۷-Mail Merge – گام چهارم تعیین داده های متغیر

از پنجره نمایش داده شده، باید متغیرها رو تعیین کنیم. برای این کار طبق زیر عمل میکنیم:

ابتدا جلوب فیلد مورد نظر کلیک میکنیم و نشانگر موس رو در مکان مورد نظر (مثلا جلوی نام خانوادگی) قرار میدیم. بعد روی More Items کلیک میکنیم و از پنجره نمایش داده شده، فیلد مورد نظر (نام گیرنده) که باید جلوی نام خانوادگی قرار بگیره رو کلیک میکنیم. برای همه قسمت های لیبل همین کار رو مطابق ویدئو زیر تکرار میکنیم و Next رو میزنیم.

میل مرج ورد و اکسل

گام پنجم: در این مرحله میتونیم لیبل های ایجاد شده رو ببینیم. برای این کار کافیه روی علامت >> بزنیم و لیبل های ایجاد شده رو ملاحظه کنیم.

گام پنجم بررسی نامه های ایجاد شده

شکل ۸- Mail Merge – گام پنجم بررسی نامه های ایجاد شده

نکته:
در این مرحله میتونیم با بررسی هر لیبل، تغییرات مورد نیاز رو اعمال کنیم و از قسمت Exclude this recipient برخی دریافت کنندگان رو حذف میکنیم.

 

گام ششم و آخر:

در این مرحله میتونیم همه یا برخی از لیبل های ایجاد شده رو مشاهده کنیم. برای مشاهده لیبل ها روی گزینه Edit Individual letters کلیک میکنیم و از پنجره نمایش داده شده تعیین میکنیم که همه (All) لیبل ها رو ببینیم یا برخی از لیبل ها نمایش داده بشه.

گام ششم مشاهده همخ لیبل های ایجاد شده

شکل ۹ – Mail Merge – گام ششم مشاهده همخ لیبل های ایجاد شده

با زدن All یک فایل ورد ایجاد شده که در هر صفحه یک لیبل با اطلاعات مورد نظر ایجاد شده که میتونیم پرینت بگیریم.

بخش دوم: حل مشکل فرمت دهی داده ها در Word

مسئله ای که در مورد استفاده از mail merge مطرحه، اینه که داده هایی که در اکسل فرمت خاصی دارن، در سند ورد بدون فرمت منتقل میشن. مثلا اگر عددی جدا کننده سه رقم سه رقم داره، در ورد بدون این جداکننده دیده میشه. یا مثلا اگر عددی بصورت درصد نمایش داده میشه، با مقدار واقعی در سند ورد منتقل میشه. یعنی مقدار ۳۰% بصورت ۰.۳ نمایش داده میشه. پس هدف این هست که داده ها با فرمت موجود در اکسل، دقیقا به همان صورت در ورد هم نمایش داده بشن. به مثال زیر توجه کنید:

شکل 9 - تغییر فرمت داده های موجود در اکسل در زمان انتقال به ورد
شکل ۹ – تغییر فرمت داده های موجود در اکسل در زمان انتقال به ورد

 

همونطور که در شکل ۹ مشخص شده است، هم ستون کد ملی و هم ستون مبلغ، هر دو فرمت سل دارن (فرمت جدا کننده سه رقم و فرمت مربوط به کد ملی) و زمانی که در فایل ورد فراخوانی شده اند، بدون فرمت انتقال داده شده اند. در حالیکه میخواهیم هم شماره ملی و هم مبلغ مورد نظر، با فرمت های نمایش داده شده در اکسل، در ورد نیز نمایش داده شوند. برای حل این مسئله میتونیم از طریق DDE (Dynamic Data Exchange) به فایل اکسل متصل بشیم.

روش اول: نحوه استفاده از Dynamic Data Exchange برای اتصال به شیت اکسل

قبل از اینکه فرایند Mail Merge رو شروع کنیم در نرم افزار Word این تنظیمات رو انجام میدیم:

    1. از مسیر File/ Options/ Advanced تیک گزینه زیر رو میزنیم:              Confirm file format conversion on open

      شکل ۱۰- تنظیمات تغییر فرمت

  1. حالا مراحل mail merge رو شروع میکنیم. برای دیدن جزییات مراحل حتما مقاله مربوط به mail merge رو مطالعه کنید. وقتی نوبت به انتخاب گیرنده ها رسید، گزینه use existing file رو انتخاب میکنیم.
  2. از پنجره فایل اکسل مورد نظر رو انتخاب کرده و Ok میزنیم.
  3. در پنجره باز شده، تیک گزینه Show all رو میزنیم و از موراد نمایش داده شده، MS Excel Worksheets via DDE (*.xls) رو انتخاب کرده و ok میزنیم.
  4. سپس از پنجره نمایش داده شده، Entire spreadsheet رو انتخاب میکنیم.
  5. حالا طبق روال قبل ادامه میدیم. یعنی در متن سند، فیلدهایی که باید قرار بگیرن رو مشخص میکنیم.
  6. در نهایت وقتی اسناد رو ایجاد میکنیم، فیلدهایی که فرمت دهی شده باشن، با همون فرمت اکسل نمایش داده میشوند. (شکل ۱۱)
final result
شکل ۱۱- انتقال داده ها با فرمت مشخص از اکسل

روش دوم: تغییر فرمت بخشی از فیلدها در فایل Word

ارتباط به یک فایل اکسل از طریق DDE سریع ترین راه برای حل این مشکل بشمار میره. اما اگر به هر دلیلی نتونستید از این روش استفاده کنید میتونید از یک سری کدنویسی (مشابه با کدنویسی فرمت سل) در قسمت Merge Field استفاده کنید.

کدهای فرمت دهی Numeric switch نوعی کد هستند که میتونن بخشی از داده های یک سند ورد رو بصورت دلخواه تغییر بدن. برای این که بتونیم از این کدها استفاده کنیم طبق زیر عمل میکنیم:

  • فیلد مورد نظر در میل مرج که باید فرمتش تغییر کنه (مثلا ستون Gift که میزان مبلغ رو نمایش میده) انتخاب میکنیم.
  • کلید ترکیبی Shift+F9 یا Alt+F9 رو میزنیم تا کد مربوطه نمایش داده بشه. با این کار یک کد عمومی متناسب با فیلد انتخابی به صورت {MERGEFIELD name} نمایش داده میشه که Name نام فیلد انتخابی است. در اینجا کد بصورت {MERGEFIELD “Gift”} نمایش داده میشه.
  • حالا کد مورد نظر رو تایپ میکنیم (در ادامه با مثال مشاهده میکنیم)
  • بعد از وار کردن کد، F9 میزنیم که آپدیت بشه.
  • در نهایت هم کلید ترکیبی Shift+F9 یا Alt+F9 رو میزنیم که کد پنهان بشه و ادامه مراحل Mail Merge رو انجام میدیم.

فرمت نامبر در Mail Merge

مثلا میخواهیم فیلد مبلغ جایزه رو بصورت جداکننده سه رقم سه رقم جدا کنیم. برای این کار، بعد از اینکه فیلد gift رو جلوی مبلغ قرار دادیم، عبارت «gift» رو انتخاب میکنیم و Shift F9 میزنیم، این عبارت به { MERGEFIELD “GIFT”} تبدیل میشه و حالا جلوی “GIFT” کد متناسب با جداکننده سه رقم رو اضافه میکنیم. یعنی:

{MERGEFIELD “GIFT” \#,۰}

حالا F9 رو میزنیم و دوباره به “GIFT” تبدیل میشه و سپس بقیه مراحل Mail Merge رو ادامه میدیم.

 

نکته:
برای نمایش درصد، توجه داشته باشید که اگر اعداد داخل اکسل، بصورت واقعی درصد شده باشند، مقدارشون تقسیم بر صد هست. یعنی اگر ۳۰% دیده میشه، مقدار اصلی ۰.۳ هست پس اول یک ستون ایجا کنید و در ۱۰۰ ضرب کنید و سپس ستون جدید رو بیارید داخل میل مرج. یا اینکه محاسبات انجام بدید مثل زیر (Percent اسم ستونی هست که درصد نمایش میده):

{% ۰ #\ ۱۰۰*{MERGEFIELD Percent }=  }

 

همینطور که برای اعداد میتونیم فرمت بدیم، برای تاریخ و زمان هم میتونیم فرمت بدیم و مثلا فرمت داده های از جنس تاریخ و زمان رو در فایل ورد مطابق با نظر خودمون اصلاح کنیم. برا یاین کار از کدهای زیر استفاده میکنیم:

فرمت نامبر در Mail Merge

نکته:
در فرمت های مربوط به این قسمت M برای ماه و m برای دقیقه استفاده میشه.

 

نمایش صفر پشت عدد در Mail Merge

برای انتقال داده هایی مثل شماره موبایل، کدملی و … که در ابتدا، صفر دارن، باید فرمت سلول ها رو تکست کنیم و صفر ها رو دستی وارد کرده باشیم. چون اگر از طریق custom format در اکسل و مثلا ۰۰۰۰۰ صفرها نمای شداده شده باشن، به ورد منتقل نمیشن. پس ابتدا فرمت سلول ها رو تکست میکنیم و صفرهای ابتدا رو دستی وارد میکنیم.

در این مقاله با یک مثال کامل، نحوه استفاده از Mail Merge رو توضیح دادیم و یکی از مهمترین مشکلات که در مورد تغییر فرمت داده ها در مقصد بود رو با دو روش حل کردیم. با این توضیحات دیگه مشکلی برای استفاده از Mail Merge  برای صدور اسنادی مثل قرارداد، فیش حقوقی و … وجود نداره. اما اگر باز هم مایل به استفاده از کد VBA برای این کار هستید، مقاله مربوط به صدور و پرینت فیش حقوقی رو مطالعه کنید و از کد آماده در اون مقاله استفاده کنید.

مشاهده ویدئو این آموزش

در حال بارگذاری پخش کننده...
آواتار
144

فارغ التحصیل لیسانس مهندسی صنایع، ارشد مدیریت صنعتی از دانشگاه تربیت مدرس و عاشق اکسل هستم. از سال 1388 که ترم 2 لیسانس بودم، به توصیه استاد مشاورم شروع به خوندن اکسل بصورت حرفه ای کردم و همچنان در حال مطالعه و یادگیری و البته آموزش به بقیه هستم.

دیدگاه کاربران
  • محمود ۷ خرداد ۱۴۰۲ / ۱:۵۷ ب٫ظ

    سلام.قابلیت میلینگ در خود اکسل وجود نداره.بعضی از فرمها با ورد امکان ساختش نیست

    • آواتار
      حسنا خاکزاد ۷ خرداد ۱۴۰۲ / ۵:۲۳ ب٫ظ

      درود بر شما
      با کدنویسی VBA همه چیز شدنی هست
      ولی امکان از پیش آماده ای نیست د راکسل

  • جمال مقدم ۱۷ اردیبهشت ۱۴۰۲ / ۴:۳۷ ب٫ظ

    سلام. آیا در زمان انتقال فیلد عددی از اکسل که فرمت آن با جداکننده کاما هست و با Mail Merge به ورد منتقل می‌شود، راهی هست که در ورد هم با جداکننده کاما نمایش داده شده و قرار گیرد؟

    • آواتار
      حسنا خاکزاد ۱۸ اردیبهشت ۱۴۰۲ / ۵:۰۹ ب٫ظ

      درود
      فایل ورد رو باز کنید:
      File/ Options/ General/ Confirm file format conversion رو تیک بزنید
      بعد مسیر میل مرج رو برید تا برسید به فراخوانی فایل اکسل. وقتی اینکار و کردید و فایل اکسل رو انتخاب کردید، در پنجره نمایش داده شده تیک show all رو بزنید و از لیست نمایش داده شده این MS Excel Worksheets via DDE (*.xls رو انتخاب کنید و بعد در پنجره Named or cell range محدوده ای که فرمت خاصی داره رو انتخاب کنید و ادامه مسیر میل مرج تا انتها

      • عطیمی ۳۰ آبان ۱۴۰۲ / ۴:۱۹ ب٫ظ

        سلام
        اعداد چگونه به صورت فارسی نمایش داده می شوند؟

        • آواتار
          حسنا خاکزاد ۲ آذر ۱۴۰۲ / ۳:۴۵ ب٫ظ

          درود از داخل ورد باید انجام بدید
          از option تیکHIndi رو بزنید

  • خزائی ۱۱ اسفند ۱۴۰۱ / ۱:۰۴ ب٫ظ

    سلام و خسته نباشید خدمت شما
    لطفا راهنمایی کنید که چطور امکان داره زمانی که این دعوت نامه ها رو آماده میکنه هر دعوت نامه رو به صورت جدا ذخیره کنه چون اینجا تمام دعوت نامه هارو در یک فایل ذخیره می کنه
    من میخوام زمانی که دعوت نامه کلی آماده می کنم دعوت نامه هارو جدا ذخیره کنه نه کلی
    ممنون میشم راهنمایی کنید

    • آواتار
      حسنا خاکزاد ۲۰ فروردین ۱۴۰۲ / ۲:۳۴ ب٫ظ

      یا با یک کد وی بی جدا کنید
      یا pdf کنید و با امکانات pdf هر صفحه رو extract کنید

  • sali ۱۷ دی ۱۴۰۱ / ۸:۴۶ ق٫ظ

    سلام
    در زمان استفاده merge mail امکان انتقال فرمت سل ها وجود دارد؟

    • آواتار
      حسنا خاکزاد ۱۸ دی ۱۴۰۱ / ۱۰:۴۱ ب٫ظ

      درود
      بله یکسری کد داخل ورد باید استفاده بشه
      نمونه داخل پیج اینستاگرام گذاشتیم قبلا

  • علیرضا مصباح فر ۱۴ آبان ۱۴۰۱ / ۱۰:۴۸ ق٫ظ

    سلام
    امکان استفاده از نمودارهای اکسل در وورد با استفاده از merge mail وجود دارد؟

    • سامان چراغی ۹ آذر ۱۴۰۱ / ۱۰:۲۳ ق٫ظ

      سلام
      خیر، تا جائیکه میدونم چنین امکانی نداره.

      • سلیمان ۸ بهمن ۱۴۰۱ / ۶:۰۳ ب٫ظ

        سلام استاد، امیدوارم حالتون خوب باشه و در صحت و سلامت کامل باشین، عارضم خدمتتون که وقتی از میل مرج استفاده میکنم و تاریخ رو در نامه ورد اضافه میکنم تاریخی رو که از فایل اکسل به فایل وردم اضافه کردم بر عکس میشه ، ایا راه حلی هست که بشه این مشکل رو برطرف کرد؟ لطفا جواب رو برام ایمیل بفرمایین استاد خاکزاد، با تشکر

        • سامان چراغی ۷ اردیبهشت ۱۴۰۲ / ۸:۵۳ ب٫ظ

          درود، تشکر از لطف شما
          این مسئله در ورد قابل حل هست و ارتباطی به اکسل ندارد.

  • محسن ۳ اردیبهشت ۱۴۰۱ / ۱:۰۴ ب٫ظ

    سلام
    خداقوت راه حل مشکل برعکس شدن تاریخ از اکسل به ورد حین انجام کار چیه ؟!؟

  • Ali Ghulami ۲۶ آبان ۱۴۰۰ / ۱۰:۱۵ ق٫ظ

    سلام
    راهی است که بتوانیم با استفاده از mail merge حدودا چهار تا فیش ویا کارت دعوت افراد مختلف را در یک ورق ورد به پرنت تهیه کنیم .
    ممنون میشیم اگر اطلاعات ارائه کنید.

    • آواتار
      حسنا خاکزاد ۲۶ آبان ۱۴۰۰ / ۱۱:۴۹ ق٫ظ

      درود
      ۴ تا فیش رو کپی کنید زیر هم
      فایل اکسلتون رو طوری بچینید که هر ۴ سری در یک ردیف باشه
      یعنی فرض کنید کل اون ۴ تا یک فیش هست و همه اون فیلدها رو میخواد پر کنه

      • مجید بهشتی ۸ خرداد ۱۴۰۲ / ۴:۱۳ ب٫ظ

        با سلام
        برای این مشکل بر عکس شدن تاریخ از اکسل در ورد چه کار باید بکنم

        • آواتار
          حسنا خاکزاد ۹ خرداد ۱۴۰۲ / ۶:۱۸ ب٫ظ

          درود
          واقعیتش من برخورد نکردم و همیشه همونی که بوده رو بردم توی ورد و درست بوده
          ولی اگه میبینید برعکس میشه بیاید توی اکسل برعکس کنید که توی ورد درست بشینه!

          راه حل حرفه ای براش نمیدونم. در واقع دنبالش نرفتم

  • mehtaz ۲۷ اردیبهشت ۱۴۰۰ / ۷:۳۴ ب٫ظ

    با سلام
    بنظر من وازه قدرتمند برآزنده نرم افزار اکسل هستش و هر جا خواستم حرف از اکسل بزنم میگویم “نرم افزار قدرتمند اکسل”.
    افراد زیادی در کارهایشان از این نرم افزار استفاده میکنن ولی کمتر افرادی هستن که از قابلیت ها و توانایی های آن آشنائی داشته باشند و وقتی آن قابلیت ها را برای این افراد توضیح و نشانشان میدیم بسیار متعجب و شگفت زده میشن.

    • سامان چراغی ۲۷ اردیبهشت ۱۴۰۰ / ۸:۳۲ ب٫ظ

      سلام
      بله کاملا درست میفرمائید.

  • حسابدار ۱۶ دی ۱۳۹۹ / ۹:۴۴ ق٫ظ

    تشکر از راهنمایی گام به گام شما

  • سید میلاد ۱۴ دی ۱۳۹۹ / ۱:۵۵ ب٫ظ

    بسیار عالی و مفید

ارسال دیدگاه

نشانی ایمیل شما منتشر نخواهد شد. بخش‌های موردنیاز علامت‌گذاری شده‌اند *

توسط
تومان