چگونه از اکسل استفاده کنیم: ۱۴ ترفند و ابزار مهم که همه باید بدانند(راهنمای کامل و سریع)

[yasr_overall_rating]

در این مقاله از سلسله مقالات آموزش اکسل به بررسی ۱۴ ترفند و ابزار اساسی در اکسل می پردازیم.

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

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

در ادامه چند ترفند و ابزار اساسی برای کار با اکسل را خواهید آموخت.

چگونه از اکسل استفاده کنیم

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

• ایجاد و ساخت یک صفحه گسترده جدید از ابتدا.
• اجرای محاسبات پایه در یک صفحه گسترده، مانند جمع کردن، کم کردن، ضرب کردن و تقسیم در یک صفحه گسترده.
• نوشتن و قالب بندی متن ستون و عناوین.
• ویژگی auto-fill در اکسل.
• افزودن یا حذف ستونها، ردیفها و صفحات گسترده. در ادامه همین مقاله، ما به چگونگی اضافه کردن چیزهایی مانند چند ستون و ردیف می رسیم.
• ثابت نگه داشتن عناوین ستون و ردیف، زمانی که شما در یک صفحه گسترده اسکرول یا Paste می کنید.

۱۴ ترفند و نکته مهم در اکسل

۱)جداول پاشنه ای(Pivot Tables)

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

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

برای ایجاد جدول پاشنه ای، به Data> Pivot Table بروید. اکسل به طور خودکار جدول پاشنه ای شما را پر می کند، اما همیشه می توانید داده ها را تغییر دهید. شما چهار گزینه برای انتخاب دارید:

۱) Report Filter:  اجازه می دهد تا شما فقط به ردیف های خاصی در مجموعه داده های خود نگاه کنید. 

۲) Column Lables: اینها در واقع در نقش هدرهای شما در مجموعه داده ها می باشند.

۳) Row Lables: اینها می توانند ردیف های شما در مجموعه داده باشند. هر دو برچسب ردیف و ستون می توانند از ستونهای شما حاوی اطلاعات باشند (برای مثال، نام شخص یا محصول و یا هر چیز دیگر را می توان به قسمت برچسب ردیف یا ستون کشید – این بستگی به شما دارد که چگونه می خواهید اطلاعات را ببینید.)

۴) Value: این بخش به شما اجازه می دهد که اطلاعات خود را به صورت متفاوتی بررسی کنید. به جای فقط مقادیر عددی، می توانید sum، count، average، max، min، تعداد اعداد، یا دستکاری های دیگر را با داده های خود انجام دهید. در حقیقت، به طور پیشفرض زمانی که یک field را به Value می کشید، همیشه مقدار آن را به خود می گیرد.

از آنجا که می خواهیم تعداد دانش آموزان را در هر خانه شمارش کنیم، به Pivot Table می رویم و ستون مربوط به خانه را مطابق زیر به Row Labels و Values می کشیم. این کار تعداد دانش آموزان هر خانه را جمع و خلاصه می کند.

۲) اضافه کردن بیش از یک سطر یا ستون در اکسل

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

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

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

۳) فیلترها ( Filters)

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

فیلترها به شما این امکان را می دهند که داده های خود را فقط به چند سطر کاهش دهید(آن داده هایی که دنبالشان هستید) و به راحتی با آنها کار کنید. در اکسل یک فیلتر می تواند به هر ستون از داده های شما اضافه شود – و از آنجا، می توانید سلول هایی را که می خواهید در یک بار مشاهده کنید را انتخاب کنید.

به مثال زیر نگاه کنید. با کلیک بر روی تب Data و انتخاب “فیلتر” یک فیلتر اضافه می کنیم. با کلیک بر روی پیکان در کنار سرصفحه ستون شما می توانید انتخاب کنید که آیا می خواهید داده های خود را به ترتیب صعودی یا نزولی مرتب سازید و همچنین ردیف های خاصی که می خواهید نشان دهید را هم مشخص کنید.

در مثال ما فقط می خواهیم دانش آموزان را در Gryffindor ببینم. با انتخاب فیلتر Gryffindor ، ردیف های دیگر ناپدید می شوند.

۴) حذف تکرار ها در اکسل

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

برای حذف داده های تکراری، ردیف یا ستونی را که می خواهید داده های تکراری را از آن حذف کنید هایلایت نمایید. سپس، به تب Data بروید و ” Remove Duplicates” (در زیر Tools) را انتخاب کنید. یک پاپ آپ برای تایید اینکه چه اطلاعاتی را می خواهید با آن کار کنید ظاهر می شود. ” Remove Duplicates” را انتخاب کنید.

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

۵) Transpose

هنگامی که تعداد کمی ردیف حاوی داده را در صفحه گسترده خود دارید، ممکن است تصمیم بگیرید که اطلاعات موجود در یکی از این سطرها را به ستون ها منتقل کنید(یا برعکس). این کار و کپی هر یک از هدرهای جداگانه زمان زیادی لازم دارد – اما ویژگی transpose این امکان را به شما می دهد که به راحتی داده های خود را از سطر به ستون منتقل کنید.
با هایلایت ستونی که می خواهید به سطرها Transpose کنید شروع نمایید. روی آن راست کلیک کرده و سپس “کپی” را انتخاب کنید. بعد، سلولی را در صفحه گسترده خود انتخاب کنید که در آن می خواهید ردیف یا ستون اول خود را شروع کنید. بر روی سلول کلیک راست کرده و سپس “Paste Special” را انتخاب کنید. یک ماژول ظاهر خواهد شد – در پایین، شما یک گزینه را برای Transpose خواهید دید. کادر را انتخاب کنید و OK را انتخاب کنید. ستون شما اکنون به یک سطر منتقل می شود و یا برعکس.

۶) ابزار Text to Column اکسل

چه می شود اگر بخواهیم اطلاعاتی را که در یک سلول قرار دارد را به دو سلول مختلف تقسیم کنیم؟
مثلا، شاید بخواهید نام شرکتی را از طریق آدرس ایمیل آنها استخراج کنید. یا شاید بخواهید نام کامل یک فرد را به شکل نام و نام خانوادگی برای استفاده در بازاریابی ایمیلی خود، تفکیک کنید.

با تشکر از اکسل، هر دو این کارها امکان پذیر هستند. اول، ستونی را که می خواهید تقسیم کنید هایلایت کنید. بعد، به تب Data بروید و “Text to Columns” را انتخاب کنید. یک ماژول با اطلاعات اضافی ظاهر می شود.

در ابتدا، شما باید “Delimited” یا “Fixed Width” را انتخاب کنید.

“Delimited” به این معنی است که شما می خواهید این ستون را بر اساس کارکترهایی مانند کاما، فضاها یا زبانه ها جدا کنید.
Fixed Width به این معنی است که شما می خواهید مکان دقیق را در تمام ستون هایی که می خواهید تقسیم رخ دهد، انتخاب کنید.
در مثال زیر، بیایید “Delimited” را انتخاب کنیم تا بتوانیم نام کامل را به نام و نام خانوادگی تقسیم کنیم.

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

اگر از پیش نمایش راضی هستید، “Next” را فشار دهید. این صفحه به شما این امکان را می دهد که فرمت های پیشرفته را انتخاب کنید. وقتی که انجام شد، روی “Finish” کلیک کنید.

فرمول ها در اکسل

۷) محاسبات ساده در اکسل

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

برای اضافه کردن، از علامت + استفاده کنید.
برای تفریق، از علامت – استفاده کنید.
برای ضرب، از نشانه * استفاده کنید.
برای تقسیم کردن، از علامت / استفاده کنید.
شما همچنین می توانید از پرانتز استفاده کنید تا اطمینان حاصل شود که محاسبات مشخص در ابتدا انجام می شود(یعنی اولویت را مشخص کنید). در مثال زیر (۱۰ + ۱۰ * ۱۰)، ابتدا دو عدد ۱۰ سمت چپی در هم ضرب می شوند و سپس حاصل آنها با عدد ۱۰ سوم جمع می گردد. با این حال، اگر ما آن را (۱۰ + ۱۰) * ۱۰، بنویسیم عدد ۱۰ سمت چپ در حاصل جمع دو عدد ۱۰ سمت راستی ضرب می شود.

نکته : اگر می خواهید میانگین یک مجموعه ای از اعداد را حساب کنید، شما می توانید از فرمول = AVERAGE (محدوده سلول) استفاده کنید. اگر می خواهید روی ستونی از اعداد جمع بزنید، شما می توانید از فرمول = SUM (محدوده سلول) استفاده کنید.

۸) فرمول Conditional Formatting در اکسل

Conditional formatting به شما اجازه می دهد که رنگ سلول را براساس اطلاعات داخل سلول تغییر دهید. به عنوان مثال، اگر می خواهید اعداد مشخصی را که بالاتر از میانگین داده های شما باشد مشخص کنید و اعدادی را که جزء ۱۰٪ بالای داده ها قرار دارد را در صفحه گسترده خود پرچم گذاری کنید، می توانید این کار را انجام دهید. اگر می خواهید کدهایی را که بین سطرهای مختلف مشترک هستند را رنگ کنید، می توانید این کار را انجام دهید.
این امکان به شما کمک می کند تا به سرعت اطلاعاتی را که برای شما مهم هستند را مشاهده کنید.

برای شروع، گروهی از سلول هایی را که می خواهید در آنها از conditional formatting استفاده کنید، برجسته کنید. سپس از منوی صفحه اصلی « conditional formatting» را انتخاب کنید و منطق خود را از منوی کشویی انتخاب کنید. (اگر چیز جدیدی مد نظر دارید که در بین گزینه های موجود وجود ندارد، می توانید قانون خود را نیز ایجاد کنید). در این حالت یک پنجره ظاهر خواهد شد که باعث می شود اطلاعات بیشتری در مورد قانون قالب بندی شما ارائه گردد. وقتی که انجام شد، “OK” را انتخاب کنید، و در این حالت باید نتایج شما به طور خودکار ظاهر گردند.

۹) عبارت IF در اکسل

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

به عنوان مثال، در شکل زیر، می خواهیم ده امتیاز را به همه کسانی که در خانه Gryffindor قرار دارند، بدهیم. به جای تایپ دستی عدد ۱۰ در کنار نام هر دانش آموز Gryffindor، می توانیم از فرمول IF THEN Excel استفاده کنیم که اگر دانش آموز در Gryffindor باشد، باید ده امتیاز بدست آورد.
فرمول ما این است که:

(IF(logical_test,value_if_true, value of false

مثال پایین فرمول آن اینگونه است: 

(“IF(D2=”Gryffindor”,”۱۰”,”۰=

به طور کلی، فرمول IF اینگونه است که: IF (تست منطقی، مقدار درست، مقدار نادرست). بیایید در هرکدام از این متغییرها یک کنکاشی بکنیم.

بخش Logical Test : این بخش در واقع بخش شرط عبارت “IF” است. در این مورد، منطق ما عبارت D2=”Gryffindor”است، زیرا ما می خواهیم اطمینان حاصل کنیم که سلول مربوط به دانش آموز “Gryffindor” است. حتما حواستان باشد که Gryffindor را در علامت نقل قول قرار دهید.
بخش Value_if_True : مقدار این بخش چیزی هست که می خواهیم اگر شرط درست بود، نمایش داده شود. در این مثال، ما می خواهیم که سلول عدد “۱۰” را نشان دهد تا نشان دهد که دانش آموز ۱۰ امتیاز به دست آورده است. فقط توجه کنید که اگر شما می خواهید نتیجه به جای یک عدد متن باشد، از علامت نقل قول استفاده کنید.
بخش Value_if_False : این مقدار در واقع چیزی است که ما می خوهیم اگر شرط نادرست بود نمایش داده شود. در این مثال، ما می خواهیم برای هر دانش آموزی که در Gryffindor نیست، سلول “۰” را برای نمایش ۰ امتیاز نشان دهد. فقط اگر شما می خواهید نتیجه به جای یک عدد متن باشد، از علامت نقل قول حتما استفاده کنید.

توجه مهم: در مثال بالا، ما ۱۰ امتیاز برای هر کس در Gryffindor بود اعطا کردیم. اگر بعدا بخواهیم مجموع امتیازات را جمع کنیم، نمی توانیم این کار را انجام بدهیم چون ما در این مثال عدد ۱۰ را در نقل قول قرار دادیم، بنابراین اکسل آنها را به عنوان متن می شناسد و نه یک عدد.

۱۰) علامت دلار در اکسل

آیا تا به حال علامت دلار را در فرمول اکسل دیده اید؟ هنگامی که علامت دلار در یک فرمول استفاده می شود، به معنی یک دلار آمریکا نیست؛ در عوض، این عبارت این اطمینان را می دهد که سطر و ستون مذکور مقدارش ثابت باقی می ماند حتی اگر شما فرمول را به سطرهای مجاور منتقل کنید. شاید این موضوع برایتان کمی مبهم به نظر بیاید، در زیر بیشتر توضیح داده ایم.
شما می بینید که، آدرس دهی سلولی در اکسل به صورت نسبی است – یعنی هنگامی که شما به سلول A5 از سلول C5، به عنوان مثال اشاره می کنید یعنی اینکه سلولی که ستون آن (C منهای A) و در همان سطر (۵) قرار دارد- به این فرمول نسبی گفته می شود. هنگامی که یک فرمول نسبی را از یک سلول به یک سلول دیگر کپی می کنید، مقادیر موجود در فرمول بر اساس جایی که قرار می گیرد تنظیم می شود. اما گاهی می خواهیم این مقادیر را بدون توجه به اینکه کجا قرار می دهیم، ثابت نگه داریم. برای این کار کافی است فرمول داخل سلول را به چیزی که به آن فرمول مطلق می گوییم تبدیل کنیم.

برای تغییر فرمول نسبی ( A5 + C5=) به یک فرمول مطلق، ما می توانیم مقادیر ردیف و ستون را با نشانه های محصور کنیم یعنی ( ۵$A$5+$C$=). (برای اطلاعات بیشتر در این زمینه به صفحه پشتیبانی مایکروسافت آفیس در اینجا مراجعه کنید.)

توابع در اکسل

۱۱) تابع VLOOKUP در اکسل

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

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

خیلی مواقع پیش می آید که نیاز است شما مجموعه داده هایی مانند این را ترکیب کنید – و در اینچنین مواقعی، تابع VLOOKUP مشکل ما را حل می کند.
هر چند قبل از استفاده از این فرمول، باید کاملا مطمئن شوید که حداقل یک ستون دارید که در هر دو مکان به صورت کاملا یکسان ظاهر می شوند. در داده های خود خوب کاوش کنید تا مطمئن شوید داده هایی که می خواهید با هم یکی کنید کاملا یکسان باشند – حتی فضای اضافی هم وجود نداشته باشد.

فرمول اینگونه است:
([VLOOKUP(lookup value, table array, column number, [range lookup=
فرمول کلی بالا در مورد مثال ما در اینجا بر حسب متغییرها اینگونه نوشته می شود:
(VLOOKUP(C2,Sheet2!A:B,2,FALSE=
در این فرمول، چندین متغیر وجود دارد. در صورتی که بخواهید داده هایی را از برگه ۲ به برگه ۱ منتقل کنید این فرمول به شما کمک می کند(در مثال نوعی ما) به توضیحات زیر توجه کنید:

Lookup value: مقدار این متغیر در واقع داده یکسانی است که شما در هردو برگه دارید. برای مقدار دهی به این متغیر مقدار نخست از دادهی یکسان در برگه شماره ۱ را قرار دهید. در مثال ما این مقدار در سلول C2 قرار دارد که ایمیل نخستین فرد است.
Table Array: محدوده ستونها در برگه ۲ که می خواهید اطلاعات آن را استخراج کنید، که این محدوده شامل ستون داده ای هست که با برگه ۱ دارای داده یکسان است( در مورد ما ستون مربوط به ایمی ها در برگه ۱ و ستونی از داده که می خواهیم به برگه ۱ منتقل کنیم . در مثال ما مقدار این نتغیر “Sheet2!A:B” است. که A به معنی ستون A در برگه ۲ است که دارای داده یکسان با ستون برگه ۱ است(ایمیل ها) و B به معنی ستون B است و شامل اطلاعاتی است که فقط در برگه ۲ موجود است و شما می خواهید به برگه ۱ آنها را منتقل کنید.
Column number: این عدد به اکسل می گوید که داده جدیدی که می خواهی به برگه ۱ منتقل کنی در کدام ستون قرار دارد. در مثال ما این ستون همان ستونی است که با عبارت “house” برچسب خورده است. در برگه شماره ۲ همانگونه که در شکل پایین مشاهده می کنید ستون “house” دومین ستون در این برگه می باشد، لذا مقدار این متغیر باید ۲ باشد.
Range Lookup: از FALSE استفاده کنید تا اطمینان حاصل شود که تنها مقادیر دقیق و درست به برگه ۱ منتقل می شوند.
مثال زیر، برگه ۱ و برگه ۲ لیستی را نشان می دهند که اطلاعات مختلفی در مورد افراد یکسان دارند و موضوع مشترک بین هر دو آدرس ایمیل آنها است. ما می خواهیم دو مجموعه داده ها را به گونه ای با هم مخلوط کنیم که اطلاعات ستون “house” از برگه ۲ به برگه ۱ منتقل شود.

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

۱۲) INDEX MATCH

مانند VLOOKUP، توابع INDEX و MATCH هم اطلاعات را از مجموعه داده های دیگر به یک مکان مورد نظر می کشند. در اینجا یک تفاوت اصلی وجود دارد:

VLOOKUP یک فرمول بسیار ساده تر است. اگر با مجموعه داده های بزرگ که نیازمند هزاران جستجو هستند کار می کنید، استفاده از تابع INDEX MATCH به طور قابل توجهی زمان بارگذاری در Excel را کاهش می دهد.
فرمول های INDEX MATCH از راست به چپ کار می کنند، در حالی که فرمول های VLOOKUP تنها به عنوان یک جستجوی چپ به راست عمل می کنند. به عبارت دیگر، اگر شما نیاز به انجام یک جستجو باشید که در آن ستون جستجو در سمت راست ستون نتایج قرار دارد، شما باید این ستون را برای انجام VLOOKUP تنظیم مجدد کنید. این کار می تواند برای مجموعه داده های بزرگ خسته کننده باشد و یا منجر به خطا بشود.
بنابراین اگر بخواهیم اطلاعات را از Sheet 1 و Sheet 2 در Sheet 1 ترکیب کنم، اما مقادیر ستونها در Sheets 1 و ۲ مشابه نیستند، برای انجام VLOOKUP، ما نیاز به تغییر در میان ستونهایمان داریم. در این حالت، ما بجای Vlookup یک INDEX MATCH انجام می دهیم.

بگذارید یک مثال بزنیم. فرض کنید Sheet 1 حاوی لیستی از نامهای افراد و آدرسهای ایمیل آنها است و Sheet 2 شامل لیستی از آدرسهای ایمیل و Patronus است که هر دانش آموز دارد. (توضیح اینکه در داستان هری پاتر، هر دانش آموز یک نگهبان حیوان به نام Patronus همراه با خود دارد.) اطلاعاتی که در هر دو صفحه مشترک هستند ستون حاوی آدرس ایمیل است، اما این ستون های آدرس ایمیل درستون های مختلف در هر برگه قرار دارد. ما از فرمول INDEX MATCH به جای VLOOKUP استفاده می کنیم، بنابراین مجبور نیستیم ستونی را تغییر دهیم.

فرمول INDEX MATCH چیست؟
فرمول INDEX MATCH در واقع فرمول MATCH در داخل فرمول INDEX است. در اینجا ما برای مشخص شدن فرمول MATCH را با استفاده از رنگ مختلف نمایش داده ایم.

فرمول:     (INDEX(table array,MATCH formula=
در این مثال فرمول بالا می شود:                   ((INDEX(table array,MATCH (lookup_value, lookup_array=

این فرمول با توجه به متغیرهای مثال ما به این شکل می شوند:                         (((INDEX(Sheet2!A:A,(MATCH(Sheet1!C:C,Sheet2!C:C,0=

توضیح متغیرها هم اینگونه است:

Table Array: محدوده ستون ها در برگه ۲ را نشان می دهد که حاوی داده های جدیدی است که می خواهیم به برگه ۱ منتقل کنیم. در مثال ما، “A” به معنای ستون A است که حاوی اطلاعات Patronus برای هر فرد است.
Lookup Value: این ستونی در برگه ۱ است که دارای مقادیر یکسان در هردو برگه می باشد. در مثال زیر، این به معنی ستون “ایمیل” در Sheet 1 است که ستون C است. بنابراین می شود: Sheet1!C:C
Lookup Array : این ستونی در Sheet 2 است که دارای مقادیر یکسان در هر دو صفحه گسترده است. در مثال زیر، این به ستون “ایمیل” در Sheet 2 اشاره دارد که ستون C است. بنابراین می شود: Sheet2!C:C

فرمول INDEX MATCH را در سلول بالاترین ستون خالی Patronus در صفحه ۱ تایپ کنید، جایی که می خواهید اطلاعات ترکیبی نمایش داده شوند.

۱۳) تابع COUNTIF در اکسل

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

به عنوان مثال، فرض کنید می خواهیم تعداد دفعاتی را که کلمه ” Gryffindor” در مجموعه داده های ما ظاهر می شود را بشماریم.

فرمول اینگونه است:   (COUNTIF(range,Criteria=

در مثال فعلی ما فرمول به این صورت می شود:  (“COUNTIF(D:D,” Gryffindor=

توضیح متغیرهای این فرمول اینگونه است:
Range: مقادیر این متغیر دامنه ای است که می خواهیم فرمول آن محدوده از داده ها را پوشش دهد. در این مورد، از آنجا که ما فقط روی یک ستون تمرکز می کنیم، از D: D استفاده می کنیم تا نشان دهیم که ستون اول و آخرین هر دو D است. اگر قرار باشد به ستون های C و D نگاه کنیم، از “C: D ” استفاده می کنیم.
Criteria: هر عدد یا تکه ای از متن که می خواهید اکسل جستجو کند را شامل می شود. فقط اگر شما می خواهید نتیجه به جای یک عدد متن باشد، فقط از علامت نقل قول استفاده کنید. در این مثال ما دنبال ” Gryffindor” می گردیم.
خیلی ساده با تایپ فرمول COUNTIF در هر سلول و با فشار دادن «Enter» اینکه مثلا چند بار کلمه «Gryffindor» در مجموعه وجود دارد نمایش داده می شود.

۱۴) ترکیب سلول ها در اکسل با استفاده از ” & “

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

فرمول با متغیرهایی مثال ما به این شکل است: A2&” “&B2=
اجازه دهید به همراه یک مثال به بررسی این فرمول بپردازیم.
فرض کنید که می خواهیم نام و نام خانوادگی را با هم یکی کرده و در یک سلول قرار دهیم. برای انجام این کار ابتدا ماوس را در سلول خالی ای قرار می دهیم که می خواهیم نام و نام خانوادگی در آن قرار بگیرد، سپس یک سلول را که نام کوچک در آن قرار دارد را هایلایت می کنیم و بعد یک علامت “&” تایپ می کنیم و سپس یک سلول با نام خانوادگی را هایلایت می کنیم .

اما کار هنوز تمام نشده است – اگر شما فقط عبارت =A2&B2 را تایپ کنید آنگاه هیچ فاصله ای بین نام و نام خانوادگی ایجاد نمی شود. برای وارد کردن این فاصله ضروری از تابع =A2&” “&B2 استفاده کنید. علامن نقل و قول اطراف فضای فاصله به اکسل این را می گوید که بین نام و نام خانوادگی فاصله بگذارد.

برای اینکه این را برای چندین سطر درست کنید، به سادگی گوشه ای از آن سلول اول را به سمت پایین بکشید، همانطور که در مثال زیر نشان داده شده است.

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