تجزیه وترکیب متن در اکسل

فرض کنید درون یک سلول یک متن یا عدد خاصی قرار دارد و شما قصد دارید که محتویات این سلول را به چند قسمت تقسیم کنید و در سلول‌های دیگری قرار دهید و یا بالعکس بخواهید محتویات چند سلول را ترکیب کنید و در یک سلول قرار دهید. این کار براحتی در اکسل قابل انجام است. در ادامه مطلب با آموزش امروز  همراه باشید تا با نحوه انجام این کار آشنا شوید.

تجزیه متن یک سلول به چند سلول

برای تجزیه محتویات یک سلول دو روش وجود دارد:

1- نوشتن فرمول 

2- ابزار در در

فرض کنید درون یک سلول یک متن یا عدد خاصی قرار دارد و شما قصد دارید که محتویات این سلول را به چند قسمت تقسیم کنید و در سلول‌های دیگری قرار دهید و یا بالعکس بخواهید محتویات چند سلول را ترکیب کنید و در یک سلول قرار دهید. این کار براحتی در اکسل قابل انجام است. در ادامه مطلب با آموزش اکسل امروز همراه باشید تا با نحوه انجام این کار آشنا شوید.

تجزیه متن یک سلول به چند سلول

برای تجزیه محتویات یک سلول دو روش وجود دارد:

1- نوشتن فرمول 

2- ابزار Text To Columns

 قبل از بیان این قسمت، لازم است با تعدادی از توابع جداسازی و یکپارچه سازی متون آشنا شوید.

• تابع Right:

این تابع تعداد مشخصی از آخرین کاراکترهای یک رشته متنی را استخراج می‌کند (متن انگلیسی از سمت راست و متن فارسی از سمت چپ). ساختار (Syntax) این تابع به شکل زیر است:

=RIGHT(text,num_chars)

آرگومان text: این آرگومان، متنی است که می‌خواهید قسمتی از آن را استخراج کنید و می‌تواند ارجاع به یک سلول و یا نوشتن متن باشد.

آرگومان Num_chars: تعداد کاراکترهایی است که می‌خواهید از انتهای رشته متن استخراج شود.

• تابع Left:

این تابع تعداد مشخصی از اولین کاراکترهای یک رشته متنی را استخراج می‌کند (متن انگلیسی از سمت چپ و متن فارسی از سمت راست). ساختار این تابع به شکل زیر است:

=LEFT(text,num_chars)

آرگومان text: این آرگومان، متنی است که می‌خواهید قسمتی از آن را استخراج کنید و می‌تواند ارجاع به یک سلول و یا نوشتن متن باشد.

آرگومان Num_chars: تعداد کاراکترهایی است که می‌خواهید از ابتدای رشته متن استخراج شود.

• تابع Mid:

این تابع تعداد مشخصی از کاراکترهای یک رشته متن را با توجه به موقعیت شروع در رشته متن و تعدادی که شما مشخص می‌کنید، استخراج می‌کند. ساختار این تابع به شکل زیر است:

=MID(text,start_num,num_chars)

ورودی اول رشته متنی است که می خواهید قسمتی از آن را استخراج کنید، ورودی دوم موقعیت اولین کاراکتر و ورودی سوم تعداد کاراکترهایی است که می‌خواهید از متن خارج کنید.

•  تابع Len:

این تابع تعداد کاراکترهای یک رشته متن را محاسبه می‌کند. اگر بین کلمات یک رشته متن فاصله وجود داشته باشد، این تابع هر فاصله را به عنوان یک کاراکتر می‌شمارد. ساختار این تابع به شکل زیر است:

=LEN(text)

این تابع یک آرگومان بیشتر ندارد و آن متن مورد نظر است که می‌خواهید تعداد کاراکترهای آن‌را محاسبه کنید.

تجزیه متن یک سلول

برای این کار می‌توان به دو روش عمل نمود:

روش اول) از طریق نوشتن فرمول

با استفاده از فرمولهایی که در بالا توضیح داده شد می توان محتوای یک سلول را تجزیه نمود. ممکن است دو حالت پیش آید:

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

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

مثال) جداسازی نام و نام‌ خانوادگی

همانطور که در تصویر زیر مشاهده می‌کنید برای جداسازی نام و نام خانوادگی چند تابع درون تابعی دیگر نوشته شده است. جدا کردن نام از نام‌ خانوادگی بوسیله تابع LEFT انجام می‌شود. برای ورودی دوم این تابع (تعداد کاراکترهای مورد نظر که قرار است استخراج شوند یعنی تعداد کاراکترهای نام) از تابع FIND استفاده می‌شود که در اینجا توضیح داده شده است. این تابع موقعیت کاراکتر فاصله را درون رشته متن می‌یابد و سپس یک واحد از آن کم می‌گردد تا موقعیت آخرین حرف نام مشخص شود.
برای جدا کردن نام خانوادگی، تابع MID بکار گرفته می‌شود. برای ورودی دوم این تابع (موقعیت اولین کاراکتر نام خانوادگی) از تابع FIND استفاده می‌گردد. این تابع موقعیت کاراکتر فاصله را درون رشته متن می‌یابد و سپس یک واحد به آن اضافه می‌گردد تا موقعیت اولین حرف نام خانوادگی تعیین شود. برای ورودی سوم تابع MID می‌بایست به طریقی مشخص شود که از اولین حرف نام خانوادگی به بعد چه تعداد کاراکتر باید استخراج شود. این کار به راحتی با استفاده از تابع LEN امکانپذیر است.

روش دوم) از طریق ابزار Text To Columns

در اینجا نیز ممکن است همان دو حالت پیش آید.

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

1. سلول‌هایی که قرار است تجزیه شوند را انتخاب نمایید.
2. از تب Data، بر روی گزینه Text to columns کلیک کنید.
3. گزینه Fixed Width را انتخاب نمایید.
4. بوسیله کلیک کردن در نقاطی که می‌خواهید کاراکترها از یکدیگر جدا شوند، ستون‌ها را تجزیه کنید.
5. در فیلد Destination آدرس سلولی که می‌خواهید داده‌ها درج شوند را وارد کنید و بر روی Finish کلیک نمایید.

حالت دوم: برای حالتی مشابه جدا کردن نام و نام خانوادگی، به طریق زیر عمل کنید:

1. سلول‌هایی که قرار است تجزیه شوند را انتخاب نمایید.
2. از تب Data، بر روی گزینه Text to columns کلیک کنید.
3. گزینه Delimited را انتخاب نمایید.
4. گزینه Space را انتخاب کنید.
5. در فیلد Destination آدرس سلولی که می‌خواهید داده ها درج شوند را وارد کنید و بر روی Finish کلیک نمایید.

ترکیب چند سلول در یک سلول دراکسل

برای این کار می توان به دو روش عمل نمود:

روش اول) در روش اول از کاراکتر & بین محتوای سلول‌ها استفاده می شود. فرض کنید می‌خواهیم نام و نام خانوادگی که در سلول‌های جداگانه درج شده‌اند را در یک سلول ترکیب کنیم. برای اینکه فاصله‌ای نیز بین نام و نام خانوادگی درج شود، فاصله را داخل گیومه قرار دهید و به فرمول بی‌افزایید. در تصویر زیر چگونگی انجام این کار نشان داده شده است.

روش دوم) از تابع CONCATENATE استفاده می‌شود. ورودی های این تابع داده‌هایی است که باید با یکدیگر ترکیب شوند. برای مثال زیر این تابع سه ورودی دارد؛ ورودی اول نام، ورودی دوم یک فاصله و ورودی سوم نام خانوادگی می باشد.

Text To Columns

 قبل از بیان این قسمت، لازم است با تعدادی از توابع جداسازی و یکپارچه سازی متون آشنا شوید.

• تابع Right:

این تابع تعداد مشخصی از آخرین کاراکترهای یک رشته متنی را استخراج می‌کند (متن انگلیسی از سمت راست و متن فارسی از سمت چپ). ساختار (Syntax) این تابع به شکل زیر است:

=RIGHT(text,num_chars)

آرگومان text: این آرگومان، متنی است که می‌خواهید قسمتی از آن را استخراج کنید و می‌تواند ارجاع به یک سلول و یا نوشتن متن باشد.

آرگومان Num_chars: تعداد کاراکترهایی است که می‌خواهید از انتهای رشته متن استخراج شود.

• تابع Left:

این تابع تعداد مشخصی از اولین کاراکترهای یک رشته متنی را استخراج می‌کند (متن انگلیسی از سمت چپ و متن فارسی از سمت راست). ساختار این تابع به شکل زیر است:

=LEFT(text,num_chars)

آرگومان text: این آرگومان، متنی است که می‌خواهید قسمتی از آن را استخراج کنید و می‌تواند ارجاع به یک سلول و یا نوشتن متن باشد.

آرگومان Num_chars: تعداد کاراکترهایی است که می‌خواهید از ابتدای رشته متن استخراج شود.

• تابع Mid:

این تابع تعداد مشخصی از کاراکترهای یک رشته متن را با توجه به موقعیت شروع در رشته متن و تعدادی که شما مشخص می‌کنید، استخراج می‌کند. ساختار این تابع به شکل زیر است:

=MID(text,start_num,num_chars)

ورودی اول رشته متنی است که می خواهید قسمتی از آن را استخراج کنید، ورودی دوم موقعیت اولین کاراکتر و ورودی سوم تعداد کاراکترهایی است که می‌خواهید از متن خارج کنید.

•  تابع Len:

این تابع تعداد کاراکترهای یک رشته متن را محاسبه می‌کند. اگر بین کلمات یک رشته متن فاصله وجود داشته باشد، این تابع هر فاصله را به عنوان یک کاراکتر می‌شمارد. ساختار این تابع به شکل زیر است:

=LEN(text)

این تابع یک آرگومان بیشتر ندارد و آن متن مورد نظر است که می‌خواهید تعداد کاراکترهای آن‌را محاسبه کنید.

تجزیه متن یک سلول

برای این کار می‌توان به دو روش عمل نمود:

روش اول) از طریق نوشتن فرمول

با استفاده از فرمولهایی که در بالا توضیح داده شد می توان محتوای یک سلول را تجزیه نمود. ممکن است دو حالت پیش آید:

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

مثال) جداسازی نام و نام‌ خانوادگی

همانطور که در تصویر زیر مشاهده می‌کنید برای جداسازی نام و نام خانوادگی چند تابع درون تابعی دیگر نوشته شده است. جدا کردن نام از نام‌ خانوادگی بوسیله تابع LEFT انجام می‌شود. برای ورودی دوم این تابع (تعداد کاراکترهای مورد نظر که قرار است استخراج شوند یعنی تعداد کاراکترهای نام) از تابع FIND استفاده می‌شود که در اینجا توضیح داده شده است. این تابع موقعیت کاراکتر فاصله را درون رشته متن می‌یابد و سپس یک واحد از آن کم می‌گردد تا موقعیت آخرین حرف نام مشخص شود.
برای جدا کردن نام خانوادگی، تابع MID بکار گرفته می‌شود. برای ورودی دوم این تابع (موقعیت اولین کاراکتر نام خانوادگی) از تابع FIND استفاده می‌گردد. این تابع موقعیت کاراکتر فاصله را درون رشته متن می‌یابد و سپس یک واحد به آن اضافه می‌گردد تا موقعیت اولین حرف نام خانوادگی تعیین شود. برای ورودی سوم تابع MID می‌بایست به طریقی مشخص شود که از اولین حرف نام خانوادگی به بعد چه تعداد کاراکتر باید استخراج شود. این کار به راحتی با استفاده از تابع LEN امکانپذیر است.

روش دوم) از طریق ابزار Text To Columns

در اینجا نیز ممکن است همان دو حالت پیش آید.

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

1. سلول‌هایی که قرار است تجزیه شوند را انتخاب نمایید.
2. از تب Data، بر روی گزینه Text to columns کلیک کنید.
3. گزینه Fixed Width را انتخاب نمایید.
4. بوسیله کلیک کردن در نقاطی که می‌خواهید کاراکترها از یکدیگر جدا شوند، ستون‌ها را تجزیه کنید.
5. در فیلد Destination آدرس سلولی که می‌خواهید داده‌ها درج شوند را وارد کنید و بر روی Finish کلیک نمایید.

حالت دوم: برای حالتی مشابه جدا کردن نام و نام خانوادگی، به طریق زیر عمل کنید:

1. سلول‌هایی که قرار است تجزیه شوند را انتخاب نمایید.
2. از تب Data، بر روی گزینه Text to columns کلیک کنید.
3. گزینه Delimited را انتخاب نمایید.
4. گزینه Space را انتخاب کنید.
5. در فیلد Destination آدرس سلولی که می‌خواهید داده ها درج شوند را وارد کنید و بر روی Finish کلیک نمایید.

ترکیب چند سلول در یک سلول

برای این کار می توان به دو روش عمل نمود:

روش اول) در روش اول از کاراکتر & بین محتوای سلول‌ها استفاده می شود. فرض کنید می‌خواهیم نام و نام خانوادگی که در سلول‌های جداگانه درج شده‌اند را در یک سلول ترکیب کنیم. برای اینکه فاصله‌ای نیز بین نام و نام خانوادگی درج شود، فاصله را داخل گیومه قرار دهید و به فرمول بی‌افزایید. در تصویر زیر چگونگی انجام این کار نشان داده شده است.

روش دوم) از تابع CONCATENATE استفاده می‌شود. ورودی های این تابع داده‌هایی است که باید با یکدیگر ترکیب شوند. برای مثال زیر این تابع سه ورودی دارد؛ ورودی اول نام، ورودی دوم یک فاصله و ورودی سوم نام خانوادگی می باشد.

امید واریم آموزش اکسل امروز نیز مورد توجه شما عزیزان قرار بگیرد

می خواهیم اطلاعات دو لیست در اکسل را با هم مقایسه نموده و ورودی هایی که منحصر به فرد هستند (غیر تکراری) را از هر دو لیست پیدا کنیم. بارها اتفاق افتاده که در هنگام کار با صدها ردیف از اطلاعات در اکسل خواسته ایم داده های خود را از دو لیست به صورت دستی مقایسه نماییم. برای آنکه مجبور به مقایسه دستی نباشیم، از ابزارformatting  Conditional و COUNTIF استفاده می کنیم.

در این مثال توضیح داده می شود که چگونه با استفاده از conditional formatting دو ستون از داده ها را با هم مقایسه کنیم.

مقایسه ی دولیست در excel

  برای این منظور به روش زیر عمل می کنیم.

ابتدا ناحیه A1:A6 را انتخاب نموده و نام FirstList را برای آن انتخاب می کنیم. (برای نام گذاری در کادر اسم نام مورد نظر را بدون فاصله تایپ کرده و کلید Enter را فشار می دهیم)

به همین ترتیب ناحیه B1:B6 را انتخاب نموده و برای آن نام SecondList را انتخاب می کنیم.

ناحیه A1:A6 را انتخاب می کنیم. (می توانیم از لیست کادر نام عبارت FirstList را انتخاب نماییم)

از سربرگ Home ابزار Conditional formatting را انتخاب نمایید و سپس بر روی گزینه New rule کلیک کنید.

از پنجره ظاهر شده عبارت “Use a formula to determine which cells to format” را انتخاب نمایید.

فرمول را به صورت زیر وارد نمایید.

=COUNTIF(SecondList,A1)=0

 بر روی دکمه Format کلید کنید و از پنجره ظاهر شده یک رنگ دلخواه انتخاب نمایید.

بر روی دکمه ok کلیک کنید. 

 چک باکس stop if true را در حالت انتخاب قرار داده و بر روی دکمه ok کلیک کنید.

نتیجه را مشاهده می کنید

 همانطور کهمی بینید Delhi Daredevils  و  Royal Challengers Bangalore منحصر به فرد هستند (در ناحیه SecondList قرار ندارند.)

برای مشخص کردن آیتم هایی که در SecondList باشند و در FirstList نباشند از فرمول زیر استفاده می کنیم.

=COUNTIF(FirstList,B1)=0

 در اینجا فرمول (COUNTIF(SecondList,A1 تعدادآیتم های ناحیه SecondList  که برابر آیتم  سلول A1 هستند را شمارش می کند (به عبارت دیگر آیا آیتم واقع درسلول A1 در SecondList هست یا خیر).

پس اگر =0(COUNTIF(SecondList,A1آیتم واقع در سلول A1 در ناحیه SecondList موجود نیست. و در پایان Conditional formatting آیتم های منحصر به فرد را به رنگ مشخص شده نمایش می دهد.

 راه دوم برای مقایسه لیست ها

یک کپی از صفحه موجود کپی نموده و سپس تمام قوائد conditional formatting  را از آن حذف کنید (انتخاب هر دو ستون –  انتخاب conditional formatting از سربرگ Home- انتخاب clear rules و سپس انتخاب clear rules from selected cells)

ابتدا ناحیه داده ها را انتخاب نمایید.

از سربرگ home ابزار conditional formatting و سپس گزینه Duplicate values را انتخاب نمایید.

از پنجره ظاهر شده زیر عبارت Format cells that contain گزینه Unique را انتخاب نمایید و سپس بر روی دکمه ok کلیک نمایید.

نتیجه مشابه مراحل قبل می باشد.                                                                                            شمارش سلول‌های خاص در اکسل                                                                                            

انواع تابع Count تحت عنوان‌های: COUNTA-COUNT-COUNTIFS -COUNTIF – COUNTBLANK. به‌صورت کلی تابع Count به‌منظور شمارش تعداد سلول‌ها در ناحیه‌ای مشخص، استفاده می‌شود.

تابع Countدر اکسل:

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

=COUNT(A1:A7)

از بین سلول A1 تا A7، تعداد سلول‌هایی که حاوی مقادیر عددی باشند را می‌شمارد.

تابع COUNTA:

با استفاده از این تابع، قادر به شمارش تعداد کل سلول‌ها در ناحیه‌ای مشخص شده می‌باشید؛ اما نکته‌ای که وجود دارد این است که این تابع، تمامی سلول‌ها به‌غیر از سلول‌های خالی را شمارش می‌کند. مانند تابع:

=COUNTA(A1:A7)

که تمام سلول‌های حاوی کارکتر را می‌شمارد.

تابع COUNTBLANK:

با استفاده از این تابع، excel قادر به شمارش تعداد سلول‌های خالی در ناحیه‌ای مشخص شده می‌باشید؛ اما نکته‌ای که وجود دارد این است که این تابع، هیچ‌یک از سلول‌های دارای مقدار را شمارش نمی‌کند. مانند:

=COUNTBLANK(A1:A7)

تابع COUNTIF:

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

COUNTIF(range,”criteria”)

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

جهت یافتن سلول‌هایی که مقداری برابر با 1000 دارند، از فرمول زیر استفاده می‌کنیم:

=COUNTIF(A1:A7,”1000″)

جهت یافتن سلول‌های حاوی مقداری بزرگ‌تر از 1500، از فرمول زیر را استفاده می‌کنیم:

=COUNTIF(A1:A7,”>1500″)

جهت یافتن سلول‌های متنی که با مقدار X شروع می‌شوند، فرمول زیر را استفاده می‌کنیم:

=COUNTIF(A1:A8,”ITPro*”)

تابع COUNTIFS:

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

COUNTIFS(criteria_range1, “criteria1”, criteria_range2, “criteria2″…)

 با استفاده از ترکیب تابع INDEX و ابزار CONDITIONAL FORMATTING موقعیت یک مقدار را در یک جدول از داده ها پیدا نموده و آن را با رنگ متمایز مشخص نماییم.

در این آموزش اکسل پیشرفته از تابع INDEX و ابزارهای CONDITIONAL FORMATTING و DATA VALIDATION استفاده می کنیم.

فرض کنید جدولی داریم  که از 6 ردیف و 5 ستون تشکیل شده است. تعداد 30 عدد مختلف در این جدول قرار دارد (داده ها می توانند تکراری هم باشند). می خواهیم سطر و ستون دلخواه را وارد نموده و با استفاده از تابع INDEX مقدار واقع در تقاطع این سطر و ستون را بدست آوریم. ضمناً عدد پیدا شده نیز باید با یک رنگ دلخواه در جدول مشخص گردد.

ابتدا جدول را تشکیل داده و اعداد را در آن وارد می کنیم. 

در کنار جدول در دو سلول عبارت های "سطر و ستون " را وارد کرده و در زیر هر کدام یک عدد که بیانگر شماره سطر و ستون باشد را وارد می کنیم. با استفاده از ابزار DATA VALIDATION مقادیری که توسط کاربر وارد می شود را محدود می کنیم تا کاربر نتواند عددی بیش از تعداد سطر و ستون های جدول را وارد نماید و اگر چنین اتفاقی بیفتد، با یک پیام مناسب به کاربر هشدار داده و مقدار وردی غلط را نپذیر

برای سلول حاوی شماره ستون نیز دقیقاً مراحل تصویری بالا را تکرار می کنیم با این تفاوت که به جای عدد 6 باید عدد 5 را قرار دهیم، چون تعداد ستون های جدول ما 5 عدد می باشد.

بعد از وارد کردن شماره سطر و ستون جدول، با استفاده از تابع INDEX مقدار حاصل از تقاطع سطر و ستون را به شکل زیر بدست می آوریم.

(INDE I5:M10,F5,G5=

در این فرمول I5:M10 محدوده جدول و F5 سلول حاوی شماره سطر و G5 سلول حاوی شماره ستون می باشد.

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

برای این کار ابتدا محدوده داده های جدول را انتخاب می کنیم. سپس مطابق تصاویر با استفاده از ابزار CONDITIONAL FORMATTING فرمت مورد نظر را برای محدوده داده های جدول تنظیم می کنیم.

اعمال فرمت دلخواه از یک فرمول در اکسل  به شرح زیر استفاده کرده ایم.

(AND(ROW()=$F$10+4,COLUMN()=$G$10+8=

در اینجا از تابع AND استفاده شده، چون هم زمان باید دو مقدار سطر و ستون جدول در شرایط مورد نظر برقرار باشند. توابع ROW و COLUMN در اکسل نیز مقدار سطر و ستون وارد شده برای اعمال فرمت را مشخص می کنند.

ضمناً $F$10  و $G$10 مقادیر سلول سطر و ستونی هستند که قبلاً وارد کرده ایم. (برای جلوگیری از تغییر سلول حاوی شماره سطر و ستون این آدر سها به صورت مطلق وارد شده اند)

اما چرا عدد 4 به مقدار سطر (ردیف)  و عدد 8 به مقدار ستون اضافه شده اند؟

اگر به موقعیت جدول اطلاعات دقت کرده باشید اولین ردیف اطلاعات ما در سطر (ردیف) پنجم شیت قرار دارد و همچنین اولین ستون جدول اطلاعات از ستون نهم شروع شده، بنابراین برای هدایت فرمت مورد نظر به درون داده های جدول، این مقادیر به فرمول اضافه شده اند.

پس از انجام مراحل گفته شده، اگر مقدار سطر و ستون را وارد کنید عدد حاصل از تقاع سطر و ستون برگردانده شده و مقدار آن نیز با یگ رنگ مجزا در جدول مشخص می گردد.                                                         امید است از آموزش امروز لذت برده باشید                                                                                    موفیقیت هایتان مستدام                                                                     

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

برای این منظور از ترکیب سه تابع sum، IF،Frequncey  در اکسل  استفاده می کنیم.

فرض کنید در ناحیه A2:A12 تعدادی عدد وارد کرده ایم. فرمول را به شکل زیر می نویسیم:

=SUM(IF(FREQUENCY(A2:A12,A2:A120)>0,A2:A12,0))

این فرمول اعداد ستون داده شده را جمع می کند به طوری که هر عدد بدون در نظر گرفتن اینکه چند بار تکرار شده باشد فقط یک بار در نتیجه جمع ظاهر می شود.

برای درک بهتر این فرمول ابتدا باید نتیجه تابع FREQUENCY را بررسی کنیم.

شکل کلی تابع FREQUENCY به این صورت است.

FREQUENCY(data_array,bins_array)

همانطور که در فرمول می بینید به جای data_array و bins_array ناحیه A2:A12 قرار گرفته است. توجه داشته باشید اگر از  تابع FREQUENCY به تنهایی (بدون ترکیب با فرمول دیگری) استفاده می کنید باید آن را به صورت آرایه ای بنویسید.

در این مثال ما تعداد 11 عدد را در ناحیه A2:A12 وارد کرده ایم. اگر بخواهیم با استفاده از تابع FREQUENCY تعداد تکرار هر عدد را حساب کنیم باید ابتدا تعداد 11 سلول را انتخاب نموده و سپس فرمول  (FREQUENCY(A2:A12,A2:A120 را در اولین سلول نوشته و کلیدهای ترکیبی ctrl+shift+enter را فشار دهیم تا فرمول به صورت آرایه ای ثبت شود.

تذکر: در فرمول های آرایه ای علامت های {    } در ابتدا و انتهای فرمول به صورت اتوماتیک نوشته می شوند.

در فرمول =SUM(IF(FREQUENCY(A2:A12,A2:A120)>0,A2:A12,0)) اگر تعداد تکرار در تابع FREQUENCY بیشتر از عدد صفر باشد آن عدد یک باردر محاسبه منظور می شود و اگر صفر بود یعنی تکرار آن عدد بیش از یک بار بوده و در جمع در نظر گرفته نمی شود.

در ستون A اعداد تکراری مشخص شده اند

فرمول ستون B به صورت آرایه ای ثبت شده است

در ستون C اگر مقدار ستون B از 0 بیشتر شود عدد واقع در ستون A قرار می گیرد و در غیر این صورت نتیجه تابع IF عدد 0 می باشد

در ستون D جمع نتیجه تابع IF محاسبه شده است.

نحوه ورود تاریخ در سلولهای اکسل
در اکسل برای ورود تاریخ باید بین اعداد روز، ماه و سال کاراکتر “/” و یا “-” آورده شود. برای وارد کردن ساعت هم از کاراکتر “:” استفاده می شود.  می توانید در یک سلول هم تاریخ و هم ساعت را وارد کنید.

تابع YEAR در اکسل

اگر بخواهیم از عدد سال یک سلول تاریخی استفاده کنیم باید از تابع YEAR کمک بگیریم. به همین ترتیب توابع DAY و month عدد روز و ماه را بر می گردانند.

تابع date در اکسل


این تابع برای افزودن و یا کم کردن عددی از یک تاریخ خاص استفاده می شود. این تابع سه آرگومان ورودی دارد یعنی عدد سال، عدد ماه و عدد روز. توجه کنید که نرم افزار در صورتیکه در ماه آگوست باشد تعداد روز را 31 روز در نظر می گیرد

تابع HOUR در excel

این تابع مقدار ساعت را بر می گرداند. توابع MINUTE عدد دقیقه و SECOND عدد ثانیه را برمی گرداند.

تابع TIME در اکسل


در صورتیکه قصد داشته باشیم به ساعت عددی را تفریق یا اضافه نماییم باید از تابع TIME استفاده کنیم.


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

1. استفاده از میانبر Ctrl + PgDn و Ctrl + PgUp برای رفتن از یک کاربرگ (worksheet) به کاربرگ دیگر د ر اکسل

2. با استفاده از کلیدهای جهت دار + Ctrl بین سلول‌ها جابه جا شوید

البته واضح است که تنها با کلیدهای جهت دار می‌توانید از یک سلول به سلول دیگری حرکت کنید. اما استفاده از کلیدهای جهت دار + Ctrl این روند را سریع‌تر می‌کند.

3. برای انتخاب داده‌ها از کلید Shift استفاده کنید

کلیدهای ترکیبی Ctrl + Shift+ و کلیدهای جهت دار کمک می‌کند محدوده‌ی داده‌ها را تا آخرین سلول دارای داده انتخاب کنید.

4برای کپی دوبار کلیک کنید

برای کپی یک فرمول لازم نیست حتما آن را تا آخرین سلول درگ کنید. فقط کافی است روی گوشه‌ی پایین سمت راست سلول فرمول دو بار کلیک کنید.

5.  تغییر فرمت سلول با استفاده از کلیدهای میانبر در erxcel

با استفاده از کلیدهای ترکیبی!+Ctrl + Shift می‌توانید فرمت عدد را به دو رقم اعشار تبدیل کنید. برای تبدیل به فرمت دلار کافی است از کلیدهای ترکیبی $+Ctrl + Shift و برای تبدیل به درصد از کلیدهای ترکیبی %+Ctrl + Shift استفاده کنید.

6. برای قفل سلول‌ها از کلید F4 استفاده کنید

گاهی اوقات برای کپی داده‌ی یک سلول می‌خواهید فرمول آن هم کپی شود. گاهی هم می‌خواهید که این کپی صورت نگیرد. وقتی می‌خواهید ورودی سلول را قفل کنید می‌توانید یک علامت $ قبل از اسم سلول (مثلا B1) قرار دهید. تایپ کردن $ قبل از نام هم سلول کار وقت‌گیر و آزاردهنده‌ای است. در عوض می‌توانید به جای تایپ $ از کلید F4 استفاده کنید تا علامت $ ایجاد شود. با هر بار فشار دادن F4 حالت‌های قفل سلول، قفل ردیف، قفل ستون و حالت بدون قفل فعال می‌شود.

7. خلاصه کردن داده‌ها با توابع CountIF و  SumIF در اکسل

تابع CountIF تعداد دفعات تکرار یک داده را در محدوده‌ی انتخاب شده مشخص می‌کند. اولین ورودی این تابع محدوده‌ی مورد نظر و دومین ورودی مربوط به شرط مورد نظر ما است. برای مثال در حالت زیر اسم چند نویسنده به نام‌های سانسا، اریا، راب، جان، ریکون و برن آورده شده است. هر کدام از آن‌ها چند داستان نوشته‌اند که هر داستانی تعدادی بازدیدکننده داشته است. برای مرتب کردن داده‌ها در یک سلول خالی دستور CountIF را نوشته تا از ستون اسم نویسنده‌ها اسم آریا را شمارش کند. پس اولین ورودی تابع ستون B4 تا B50 است و دومین ورودی آن سلول G4‌ است که اسم آریا در آن نوشته شده است.

حالا می‌خواهیم کل بازدیدکنندگان هر داستان را شمارش کنیم. در این حالت از تابع SumIF استفاده می‌کنیم. این تابع سه ورودی دارد. ورودی اول ستون B4 تا B50 است. ورودی دوم سلول مربوط به اسم نویسنده است و ورودی سوم ستون مربوط به تعداد بازدیدکنندگان است. حاصل این تابع به عنوان مجموع کل بازدیدکنندگان آثار یک نویسنده در مقابل اسم وی نمایش داده می‌شود.

8. استخراج داده‌ها با تابع VLOOKUP در excel

فرض کنید لیستی طولانی از نام چند دانشگاه در اختیار داریم که رتبه‌ی هر یک از آن‌ها در سلول مقابلش درج شده است. برای این که بتوانیم در این لیست طولانی، 5 دانشگاه اول را پیدا کنیم از تابع VLOOKUP استفاده می‌کنیم.

تابع VLOOKUP چهار ورودی دارد. ورودی اول مربوط به داده‌ی مورد نظر است. در این مثال ما به دنبال عدد 1 که رتبه‌ی دانشگاه است، هستیم. ورودی دوم مربوط به محدوده‌ی داده‌ها است که در این مثال کل ردیف A تا F انتخاب شده است. ورودی سوم مربوط به شماره ستون داده‌ی مورد نظر است. چون ما به دنبال اسم دانشگاه هستیم ورودی را ستون 2 در نظر می‌گیریم. ورودی آخر مربوط به وقتی است که داده‌ی مورد نظر (مثلا عدد 1) پیدا نشود که به جای آن عدد 0 یا عبارت False را قرار می‌دهیم.

9. استفاده از & برای ترکیب رشته‌های متنی

در اینجا دو ستون داریم که یکی شامل نام و دومی شامل نام خانوادگی است. با استفاده از علامت & می‌توان ستونی ایجاد کرد که نام و نام خانوادگی را در کنار هم داشته باشد. در نرم‌افزار اکسل با کمک & می‌توانید دو قسمت از متن را با هم ترکیب کنید. البته دقت کنید که بین نام و نام خانوادگی حتما فاصله قرار دهید. روش کار به این صورت است: [انتخاب سلول نام خانوادگی]& ” ” & [ّانتخاب سلول نام].

10. پاک کردن متن با توابع LEFT، RIGHT وLEN  در اکسل

این فرمول‌های متنی مخصوص پاک کردن داده‌ها است. برای مثال در حالت زیر اسم چند ایالت آمریکا را داریم که حروف اختصاری هر یک با یک خط فاصله در کنار اسامی قرار گرفته است. با استفاده از تابع LEFT می‌توانیم فقط دو حرف اختصاری هر نام را داشته باشیم. LEFT بر اساس تعداد کاراکترهای در نظر گرفته یک رشته‌ی متنی را به صورت خروجی تحویل می‌دهد. اولین ورودی این تابع سلول مربوط به رشته‌ی متنی و دومی تعداد حروف مورد نظر است.

چنانچه بخواهید نام ایالت را به صورت کامل داشته باشید و خبری از حروف اختصاری نباشد از تابع RIGHT استفاده کنید. RIGHT تعداد کاراکترها را از سمت راست رشته‌ی متنی جدا می‌کند.

اما سوال این است که چه تعداد کاراکتر را باید از سمت راست انتخاب کنید؟ چرا که تعداد حروف ایالت‌ها با هم برابر نیستند. در اینجا استفاده از تابع LEN کارگشا خواهد بود.

تابع LEN کل کاراکترهای یک رشته‌ی متنی را شمارش می‌کند و تنها یک ورودی دارد که آن هم سلول مربوط به رشته‌ی متنی است.

حالا با کمک تعداد کاراکترهای به دست آمده از تابع LEN می‌توانید از تابع RIGHT استفاده کنید.

از آن جا که ما می‌خواهیم حروف اختصاری و خط فاصله را حذف کنیم، وردی تابع RIGHT را عدد تابع LEN منهای 3 قرار می‌دهیم.

11. ایجاد اعداد تصادفی با تابع   RANDدر اکسل

تابع ()RAND می‌تواند اعداد تصادفی بین صفر تا 1 ایجاد کند. این تابع هیچ گونه ورودی لازم ندارد و داخل پرانتز خالی است. با هر بار فشردن کلید F9 این تابع عدد جدیدی تولید می‌کند. دقت داشته باشید که با ایجاد هر گونه تغییر در فضای کار، یک عدد جدید ایجاد خواهد شد.

عملگرهای ریاضی در excel

اکسل برای فرمولها از عملگرهای استاندارد مانند علامت بعلاوه (+) برای عملیات جمع، علامت منها (-) برای عملیات تفریق، علامت ستاره (*) برای عملیات ضرب، علامت اسلش رو به جلو (/) برای عملیات تقسیم، و علامت هشتک (^) برای عملیات به توان رساندن، استفاده می کند.



تمام فرمولها در اکسل باید با علامت برابر بودن (=) آغاز گردد.

درک ارجاع دادن به سلولها در اکسل

اگر چه شما می توانید با استفاده مستقیم از اعداد فرمولهای ساده ای در اکسل بسازید (بعنوان مثال 2+2= یا 5*5=)، اما غالبا شما از آدرس سلولها برای ساختن فرمولها استفاده خواهید کرد. این کار را ارجاع دادن به سلولها می نامند. استفاده از ارجاع سلولها باعث می گردد تا این اطمینان حاصل گردد که فرمولهای شما همیشه صحیح می مانند، زیرا شما می توانید مقدار سلولهای ارجاع داده شده را بدون اینکه به فرمول دست بزنید یا آن را بازنویسی کنید، تغییر بدهید.

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

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

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

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

در مثال زیر ما از فرمول ساده و ارجاع به سلولها استفاده می کنیم تا یک بودجه را محاسبه کنیم.

➊ سلولی را که می خواهید فرمول در آنجا قرار بگیرد را انتخاب کنید، در این مثال ما سلول D12 را انتخاب می کنیم.



➋ علامت برابر است با (=) را تایپ کنید. توجه داشته باشید که این علامت چگونه در سلول و همینطور نوار فرمول نمایش داده می شود.



➌ آدرس سلول اولی را که می خواهید در فرمول به آن ارجاع شود را تایپ کنید. در این مثل ما سلول D10 را تایپ می کنیم. یک حاشیه آبی در اطراف سلولی که به آن ارجاع شده است نمایان می شود.



➍ عملگر ریاضی را که می خواهید استفاده کنید را تایپ کنیدو در این مثال ما علامت جمع (+) را تایپ می کنیم.

➎ آدرس سلولی را که می خواهید بعنوان سلول دوم در فرمول مورد ارجاع قرار بگیرد را تایپ کنید. ما D11 را تایپ می کنیم. یک حاشیه قرمز دور سلول مورد ارجاع نمایان می گردد.



➏ دکمه اینتر در صفحه کلید را فشار دهید. فرمول محاسبه می شود، و مقدار محاسبه شده در سلول مربوط به فرمول نمایان می گردد. اگر دوباره سلول مربوط به فرمول را انتخاب کنید، متوجه خواهید شد که این سلول نتایج محاسبه را نشان می دهد، در حالیکه خود فرمول در نوار فرمول نمایش داده می شود.

اگر نتیجه فرمول بزرگتر از سلولی باشد که قرار است در آنجا نمایش داده شود، نتیجه با نشانه های پوند (#######) نمایش داده می شود. معنای این نشانه ها اینست که عرض سلول به اندازه کافی نیست و محتوا نمی تواند بصورت کامل نمایش داده شود. در این حالت به سادگی فقط عرض سلول را به اندازه کافی افزایش بدهید تا بتوانید نتایج را مشاهده کنید.

ویرایش مقادیر با ارجاع به سلولها

مزیت واقعی ارجاع به سلولها اینست که به شما اجازه می دهد تا بدون نیاز به بازنویسی فرمول، داده هایتان را در برگه ها تغییر بدهید. در مثال زیر، ما مقدار سلول D1 را از 1200 به 1800 تغییر می دهید. فرمول موجود در سلول D3 بصورت اتوماتیک دوباره نتیجه را محاسبه کرده و در سلول D3 نمایش می دهد.

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

ایجاد فرمول با استفاده از روش اشاره و کلیک

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

➊ سلولی را که می خواهید در آن فرمولتان را بنویسید انتخاب کنید، در این مثال ما سلول D4 را انتخاب می کنیم.



➋ علامت برابر است با (=) را تایپ کنید.

➌ سلولی را که می خواهید در ابتدای فرمول به آن ارجاع بدهید را انتخاب کنید.



➍ عملگر ریاضی مورد نظرتان را تایپ کنید. در این مثال ما عملگر ضرب (*) را تایپ می کنیم.

➎ سلول بعدی را که می خواهید در فرمول به آن ارجاع شود را انتخاب کنید.



➎ دکمه اینتر صفحه کلید را بفشارید. فرمول محاسبه می شود و نتایج آن نمایش داده می شود.

کپی کردن فرمولها با استفاده از ویژگی fill handle

فرمولها با استفاده از ویژگی fill handle می توانند به سلولهای مجاورشان کپی شوند. این ویژگی می تواند در زمان و بهره وری شما تاثیر مثبتی بگذارد.

➊ سلولی که فرمول در آن قرار دارد و قصد کپی کردنش را دارید انتخاب کنید.

➋ بعد از اینکه ماوس را رها کردید، فرمول به سلولهای انتخاب شده کپی می شود.

ویرایش یک فرمول در اکسل

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

➊ سلولی که فرمول در آن قرار دارد را انتخاب کنید. در مثال ما D12.



➋ بر روی نوار فرمول کلیک کنید تا بتوانید فرمول را اصلاح کنید. همچنین اگر بر روی سلول با ماوس دبل کلیک کنید امکان ویرایش در همان سلول هم فراهم می گردد.



➌ حاشیه ای در اطراف تمامی سلولهایی که در فرمول به آن ارجاع شده است نمایان می گردد. در این مثال ما قسمت اول فرمول را تغییر می دهیم. مقدار D10 را جایگرین D9 می کنیم.



➍ وقتی کارتان تمام شد دکمه اینتر را بفشارید.



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

اگر نظرتان عوض شده باشد می توانید با فشردن کلید Esc روی صفحه کلید و یا دکمه کنسل در نوار فرمول عملیات ویرایش را لغو کنید.

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

برای انتقال و کپی کردن اطلاعات SQL Server و Excel از روش‌های مختلفی می‌توان استفاده کرد و همواره شرایط خاص خود را خواهد داشت. در مطالب قبلی منتشر شده به نحوه کپی کردن اطلاعات جدول SQL به اکسل پرداختیم و حال در این مطلب به روش معکوس آن یعنی انتقال اطلاعات از Excel به جدول SQL خواهیم پرداخت.

روش کپی کردن فایل بین اکسل و SQL بسیار ساده است و به راحتی قابل انجام می‌باشد، اما برخی نکات در این بین وجود خواهد داشت که باید به آن دقت ویژه‌ای داشت.

در ادامه با آموزش انتقال اطلاعات از Excel به جدول SQL همراه ما باشید.

انتقال اطلاعات از Excel به جدول SQL

1- نرم افزار SQL Server management را باز کرده و به دیتابیس خود متصل شوید.

2- ابتدا یک جدول در پایگاه داده SQL Server خود بسازید.

3- دقت داشته باشید در هنگام ساخت ستون‌های مورد نیاز، تعداد ستون ها و همچنین نوع آن ها بسیار حائز اهمیت می‌باشد؛ به طوری که اگر نوع ستون به درستی مشخص نگردد در هنگام کپی با خطا روبه‌رو خواهید شد.

به عنوان مثال اطلاعات Integer را در Text نمی‌توان کپی کرد و با خطا روبه‌رو خواهید شد.

4- فایل اکسل خود را باز کرده و ردیف‌های مورد نظر را انتخاب و بر روی آن کلیک راست کنید.

5- سپس گزینه Copy را کلیک کنید و یا از کلید ترکیبی Ctrl + C استفاده نمایید.

6- در پایگاه داده خود، بر روی جدول کلیک راست کرده و Edit Top 200 Rows را انتخاب کنید.

7- در پنجره باز شده بر روی نشانگر سطر کلیک راست نمایید.

8- سپس گزینه paste را بزنید و یا کلید ترکیبی Ctrl + V را فشار دهید.

در صورتی که اطلاعات وارد شده و سطرها همخوانی داشته باشند، همانند تصویر زیر اطلاعات به صورت کامل کپی خواهد شد.

در نظر داشته باشید، روش توضیح داده شده برای تعداد سطرهای کمتر از 200 عدد می باشد.

در برخی موارد داده‌هایی که در اکسل فراخوانی می‌کنید در بعضی از سطرها تکراری هستند. داده‌های تکراری حجم و فضای زیادی از فایل اکسل شما را اشغال می‌کنند. مدت زمان محاسباتی صفحاتی با داداه‌های تکراری نیز زیاد است. پیدا کردن سطرهای تکراری در اکسل و حذف آنها به صورت دستی، در مواجهه با فایل هایی که حجم انبوهی از داداه‌ها را در خود دارند، کار طاقت و فرسا و بعضاً ناممکنی است. استفاده از ابزارهای اکسل و چند ابزار اختصاصی، شما را در این امر یاری خواهد کرد. این فرایند در دو مرحله پیدا کردن و حذف کردن انجام  می‌شود.

برای افرادی که با داده‌های حجیم یا پایگاه‌های داده کار می‌کنند، یکی از کنترل‌ها، پیدا کردن سطرهای تکراری و حذف آنها است. مقادیر تکراری در میان داده‌های شما، به دلایل مختلفی ظاهر می‌شوند. اگر می‌خواهید در میان کل داده‌هایتان مقادیر تکراری را پیدا کنید، باید مطمئن شوید که در کدام ستون ها باید مقادیر غیر تکراری وجود داشته باشد. تکراری بودن مقادیر در تمامی ستون ها همیشه خطا محسوب نمی‌شود. از چندین روش گفته شده می‌توانید یکی را برای انجام کارتان انتخاب کنید. روش‌های گفته شده تقریباً متفاوت از هم بوده و هرکدام دارای محاسن و معایب خاص خود است.

پیدا کردن سطرهای تکراری در اکسل و حذف آنها با تابع داده DATA FUNCTION

از این روش زمانی استفاده کنید که:

• نیاز به حذف سریع داده‌های تکراری از کل جدولتان دارید.
• اگر داده‌های تکراری به صورت اتوماتیک و بدون اینکه شما آنها را ببنید حذف شوند، مشکل ساز نخواهد بود.
• داده‌های بسیار زیادی دارید که پیدا کردن چشمی و حذف دستی آنها بسیار طاقت فرسا است

استفاده از این روش بسیار ساده و راحت است. عیب این روش این است که قبل از حذف، نمی‌توانید هیچ گونه آنالیزی بر رو داده‌ها انجام دهید. وقتی کل محدوده را انتخاب کنید و از این تابع استفاده کنید، تابع از شما خواهد پرسید که بر اساس کدام ستون می‌خواهید سطرهای تکراری حذف شوند. اگر فقط یک ستون انتخاب کنید، تابع بدون توجه به مقادیر دیگر ستون ها، هر سطری که در آن ستون خاص داده تکراری داشته باشد را به کل حذف می‌کند. اگر چندین ستون را انتخاب کنید، تابع سطرهایی را که در تمامی ستون های انتخابی، دقیقاً همان مقادیر را داشته باشند حذف خواهد کرد.

در این پنجره اگر تیک My data has headers را بزنید، اکسل از عنوان ستون های شما در کادر مربوط به Columns استفاده خواهد کرد. در کادر Columns تمامی ستون هایی که باید تکراری بودن مقادیر آنها بررسی شود، لیست شده است. پس از اتمام عملیات، اکسل تعداد سطرهای تکراری و غیر تکراری را اعلام خواهد کرد.

پیدا کردن سطرهای تکراری در اکسل با قالب بندی شرطی

از این روش زمانی استفاده کنید که:

• حجم داده هایتان کم است و در صورت نیاز می‌توان  با کنترل چشمی و به صورت دستی داده‌های تکراری را حذف کرد.
• اگر می‌خواهید قبل از حذف داده‌های تکراری، آنها را آنالیز کنید، و بر روی آنها کنترل داشته باشید.
• داده‌ها بسیار پیچیده هستند و برای تشخیص داده‌های تکراری از غیر تکراری قالب بندی شرطی کمکتان خواهد کرد

با استفاده از قالب بندی شرطی می‌توانید سطرهایی با داده های تکراری را به راحتی پیدا کرده و قالب آن را به دلخواه خود تغییر دهید. برای این منظور محدودهای که در آن داده‌های سطرها تکراری هستند را انتخاب کنید. از منوی HOME به زیر منوی Styles رفته در بخش Conditional Formatting کلیک کنید. در منوی باز شده قسمت Highlight Cells Rules را انتخاب کنید و در این قسمت به Duplicate Values بروید. در پنجره باز شده اگر مقدار Duplicate را انتخاب کنید، داده‌های تکرای قالب بندی خواهند شد و اگر Unique را انتخاب کنید، داده‌های بدون تکرار قالب بندی می‌شوند. قالب موردن نظرتان را از سمت راست و از منوی کره‌کره‌ای انتخاب کنید یا در Custom Format قالب دلخواه خود را تعریف کنید.

پیدا کردن سطرهای تکراری در اکسل با استفاده از جدول های پاشنه‌ای

از این روش زمانی استفاده کنید که:

• می‌خواهید لیستی از داده‌های بدون تکرار ایجاد کنید.
• در لیست اصلی داده‌هایتان دنبال داده‌های تکراری هستید و می‌خواهید مطمئن شوید که، همه داده‌ها غیر تکراری هستند.
• وجود داده‌های تکراری نادرست نیست و قصد جمع بندی و خلاصه کردن آنها را دارید

اگر می‌خواهید از داده‌های موجود به سرعت ستونی از داده‌های غیر تکراری تولید کنید، استفاده از جدول پاشنه‌ای مناسب است. برای ایجاد یک جدول پاشنه‌ای، از منوی INSERT به زیر منوی Tables رفته و روی Pivot Table کلیک کنید. در پنجره باز شده محدوده داده‌ها را انتخاب کنید. OK نمایید تا جدولتان ایجاد شود. به طور پیش فرض جدول در برگه‌ای جدید ایجاد خواهد شد، اگر می‌خواهید جدولتان در همان برگه داده‌ها ایجاد شود، دکمه رادیویی Existing Worksheet را انتخاب کرده و سلول مورد نظر، برای درج جدول را انتخاب کنید. اگر داده‌های شما در جای دیگری به غیر از برگه فعلی قرار دارد، دکمه رادیویی Use an external data source  را انتخاب نموده و آنها را فراخوانی کنید.

 پس از ایجاد جدول پاشنه‌ای، از قسمت Choose filed to add report عنوان ستونی که اضافه کرده‌اید را به قسمت ROWS درگ کنید. روش درگ کردن را از محدوده در اکسل بخوانید. با تنظیم Value Filed Settings بر روی شمارنده، تعداد داده‌های تکراری را می‌توانید مشاهده کنید.

تنظیمات بیشتر بر روی جداول پاشنه‌ای

بعضاً جدولی دارید که در آن وجود داده‌های تکراری به معنی نادرست بودن آنها نیست و شما قصد دارید با جمع بندی داده‌های تکراری، حجم داده ها را به حداقل برسانید. برای مثال در لیست خرید آرماتور برای یک کارگاه ساختمانی، خرید‌ها در چندین تاریخ مختلف اتفاق افتاده است و در هر یک از تاریخ ها چندین شماره آرماتور خرید شده است. تکراری بودن شماره آرماتور‌ها در کل جدول یا تکراری بودن تاریخ ها به منزله نادرست بودن داده‌ها نیست. جمع بندی این اطلاعات با جداول پاشنه‌ای به راحتی امکان پذیر است.

 با استفاده از داده‌های موجود، دو جدول پاشنه‌ای ایجاد شده است. در یکی از جدول‌ها تعداد دفعات خرید شده از هر شماره آرماتور نشان داده می‌شود. و در جدولی دیگر، در تاریخ‌های مشخص، تعداد شماره آرماتور، تعداد بندیل‌ها و وزن کل خریداری شده نمایش داده می‌شود. به نظر می‌رسد با استفاده از اطلاعات خام گزارشات سودمند تری به دست آورده ایم!

در این تکنیک بدون اینکه سطری حذف شود، سطرهای تکراری و تعداد آن مشخص شده است. به خاطر داشته باشید که در استفاده از جداول پاشنه‌ای، اساساً یک مجموعه داده‌های جدید ایجاد می‌کنید. بنابراین اگر واقعاً می‌خواهید داده‌های اصلی را ویرایش کنید، از روش های دیگری استفاده کنید.

پیدا کردن سطرهای تکراری در اکسل با استفاده از مرتب کردن داده ها

از این روش زمانی استفاده کنید که:

• تعداد داده‌هایتان کم است و می‌توانید با کنترل چشمی آنها را حذف کنید.
• می‌خواهید سطرهای تکراری را ببنید، آنالیز کنید و قبل از حذف، تکراری بودن آنها را تایید کنید.
• نوع داده‌هایتان ساده است و می‌توانید تکراری بودن آنها را تشخیص بدهید. (داده هایتان 15 رقمی و ترکیبی از عدد و حرف نیست!)

مرتب کردن داده‌ها از سریع ترین روش های پیدا کردن و حذف سطرهای تکراری در اکسل است. فرض کنید داده‌هایتان ساده و تعدادشان کم است، به سرعت آنها را مرتب کنید و تکراری ها را حذف نمایید. اگر داده‌هایتان پیچیده است از این روش استفاده نکنید و به سراغ قالب بندی شرطی بروید.

برای مرتب کردن داده‌ها، کل داده‌هایتان را انتخاب کرده و از منوی DATA به زیر منوی Sort&Filter رفته و بر روی Sort کلیک کنید. در پنجره باز شده در قسمت Column ستونی را که می‌خواهید بر اساس آن مرتب سازی کنید، انتخاب نماید. OK کنید داده‌هایتان مرتب خواهند شد.

پیدا کردن سطرهای تکراری در اکسل با استفاده از فیلتر پیشرفته Advanced Filter

از این روش زمانی استفاده کنید که:

• فقط می‌خواهید داده‌های بدون تکرار را مشاهده کنید.
• قصد ندارید داده‌های تکراری را حذف کنید و پنهان کردن آنها کافی است.

ابزار فیلتر برای پیدا کردن سطرهای تکراری در اکسل، به انتخاب شما، تنها برخی از داده‌ها را پنهان خواهد کرد. توجه داشته باشید، که فیلتر پیشرفته صرفاً داده‌هایی که در کل یک سطر غیر تکراری هستند را نشان خواهد داد. به عبارت دیگر تنها سطرهایی که کل داده‌هایشان تکراری است، پنهان خواهند شد.

برای اعمال فیلتر پیشرفته بر روی داده‌ها، از منوی DATA به زیر منوی Sort&Filter رفته و بر روی Advanced کلیک کنید. در پنجره باز شده محدوده داده‌ها را انتخاب کرده و دکمه رادیویی Unique records only را بزنید. OK کنید. اگر می‌خواهید داده‌های اصلی بدون تغییر باقی بماند، دکمه رادیویی Copy to another location را بزنید تا اکسل داده‌هایتان را به جای دیگری کپی کرده و سپس فیلتر نماید.

معیار انتخاب ستون های بررسی سطرهای تکراری در اکسل

در برخی جداول به مانند جدول متره آرماتور فونداسیون، تکراری بودن شماره آرماتور ها به خودی خود، بی معنی است، یا تکراری بودن شماره آرماتور با شماره ردیف نیز کاملاً بی معنی است. ولی پیدا کردن سطرهایی که به طور همزمان شماره آرماتور و طول آرماتور تکراری دارند، مهم است.

پیدا کردن سطرهای تکراری می‌تواند بر اساس داده‌های یک ستون، دو ستون یا کل محدوده باشد. اگر کل داده‌های یک محدوده از یک جنس باشند (مثلا نام شهر، نام ماه سال، نام افراد، شماره ملی، شماره پرسنلی و …)، انتخاب کل محدوده معنی دار خواهد بود. ولی در جداولی به مانند جدول ذکر شده، که داده‌های هر ستون از یک جنس هستند، باید بر اساس ستون های خاص سطرهای تکراری را پیدا کرد.

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

ستون کمکی را انتخاب کرده و با قالب بندی شرطی، سطرهای تکراری برای آن ستون را مشخص نمایید. نتیجه به شکل زیر خواهد بود. پیدا کردن سطرهای تکراری در اکسل به این روش نیازمند کار بیشتر بر روی داده‌ها است و برای داده‌هایی با حجم زیاد توصیه نمی‌شود.

سطرهایی که داده‌های تکراری دارند را می‌توان در هم ترکیب کرد. بدین معنی که اگر شماره و طول آرماتور ها یکسان است، می‌توان تعداد آرماتورهای این دو سطر را با هم جمع کرده و داده‌های دو سطر را تنها در یک سطر نوشت. در ردیف 1 آرماتور با شماره 10 و طول 248 سانتیمتر با ردیف 9 تکراری است. تعداد آرماتور ردیف 1 و ردیف 9 را جمع کرده (135=23+112) و کلاً در یک سطر بنویسید.

Row- num: شماره سطر سلول را وارد کنید.

Cohum-num: شماره ستون سلول را وارد کنید.

[abs_num]: این فیلد اختیاری است و در صورت خالی بودن و یا وارد کردن عدد 1 سلول مورد نظر مطلق می شود، با وارد کردن عدد 2 ردیف مورد نظر مطلق و ستون مورد نظر نسبی، با وارد کردن عدد 3 ردیف مورد نظر نسبی و ستون مورد نظر مطلق و با وارد کردن عدد 4 سلول مورد نظر به صورت نسبی نمایش داده می شود.

[a1]: یک آرگومان اختیاری و یک مقدار منطقی (Logical Value) می باشد، اگر این آرگومان خالی بماند و یا True باشد، آدرس سلول به فرمت آشنای A1 یعنی شماره سطر عدد و شماره ستون حرف بیان می شود و اگر این آرگومان False باشد فرمت بیان آدرس سلول به صورت

[sheet_text]: اختیاری و متنی است که نام کاربرگ مورد نظر را مشخص می کند.

نکته: آرگومان اول و دوم آرگومان های اجباری و عدد هستند، آرگومان سوم، چهارم و پنجم آرگومان های اختیاری هستند.

به مثال های زیر توجه کنید:

ADDRESS (8,13,3,FALSE,”Sheet1″)= Sheet1!R[8]C13=

ADDRESS (8,13,3,TRUE,”Sheet1″)= Sheet1!$M8=

تابع TRANSEPOSEدر اکسل

ما می توانیم با استفاده از ویژگی Transpose ساختار اطلاعات کاربرگ را تغییر دهیم. بدین صورت که جای ستون ها و ردیف ها را تغییر دهیم.

Array: تنها آرگومان این تابع آدرس محدوده یا آرایه ای است که می خواهیم Transpose شود.

مثال: برای تبدیل سطر به ستون ابتدا ناحیه سلول های A1 تا A5 را انتخاب کنید. سپس در ناحیه ای که می خواهید فرمول را بنویسید به اندازه معکوس ماتریس مورد نظر، سلول انتخاب کنید و فرمول(Transpose(A1:A5 بنویسید در نهایت به جای Enter از ترکیب Ctrl+Shift+Enter استفاده کنید.