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

فرمول سلول D3

=ADDRESS(B3،C3)

ترکیب تابع

 ADDRESS(row_num , column_num , [abs_num ], [a1 ], [sheet_text])

پارامتر های تابع 

row_num: اجباری - شماره سطر را نشان می دهد.

column_num: اجباری - شماره ستون را نشان می دهد.

[abs_num]: اختیاری – برای تعیین آدرس دهی سلول بکار می رود. در اکسل دو نوع آدرس دهی وجود دارد مطلق و نسبی.

بسته به اینکه سطر یا ستون از کدام نوع باشد در کل 4 حالت پیش می آید که به شکل زیر عدد وارد می کنیم:

برای مطلق - 1 یا بدون عدد

برای سطر مطلق ، ستون نسبی - 2

برای ستون مطلق ، سطر نسبی - 3

برای نسبی - 4

[a1]: اختیاری - به شما اجازه می دهد سبک آدرس سلول ها را انتخاب کنید. به صورت پیش فرض اکسل ستون ها را با حروف لاتین و سطرها را با شماره نامگذاری می کند . مثلا اولین خانه ستون اول در حالت اول با نام A1 و در حالت دوم با نام R1C1 (یعنی سطر 1 و ستون 1) نامگذاری می شود . این پارامتر برای تعیین این حالت بکار می رود. اگر مقدار TRUE باشد (یا خالی بگذاریم) سبک A1 و اگر FALSE تایپ کنیم سبک R1C1 خواهد بود.

[sheet_text]: اختیاری - نام Worksheet را می خواهد که به عنوان مرجع خارجی , سلول مورد نظر در آن قرار دارد. اگر این کادر خالی باشد نام خاصی در نظر گرفته نمی شود. حتی می توان آدرس را از فایل دیگری انتخاب کرد.

به عنوان مثال , فرمول زیر مقدار سلول Sheet2!$A$1 را باز می گرداند.

=ADDRESS(1,1,,,"Sheet2")

توجه

 ممکن است نماد های جداکننده سیستم شما با توجه به تنظیمات regional settings متفاوت باشد.

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

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

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

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

 و , بهترین راه این است که نکته ها , ترفندها و مثال های کاربردی را که در کتاب جداول محوری ذکر کرده ام و می تواند به شما کمک کند مطالعه کنید.

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

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

1-هنگامی که شروع به تایپ تابع می کنید , ویژگی  AutoComplete , نام کامل تابع را نمایش می دهد کلید TAB را از صفحه کلید فشار دهید تا نام تابع را تکمیل کنید.

2-هنگامی که شما نام تابع را به همراه اولین پرانتز باز وارد کنید، (به عنوان مثال: )VLOOKUP=) کلیدهای CTRL+A را فشار دهید تا کادر محاوره ای Function Arguments باز شود.

3-کادر محاوره ای Function Arguments با ارائه توضیحات برای هر یک از پارامترها , به شما کمک می کند که نوشتن تابع را تکمیل کنید.

4-اگر نیاز به اطلاعات بیشتری در مورد تابع دارید ، روی لینک "Help on this function " در گوشه پایین سمت چپ کادر محاوره ایکلیک کنید.

5-اگر کادر توصیف تابع (popup) در مسیر شما قرار گیرد، می توانید آن را به سادگی به موقعیت جدیدی بکشید.

 

اگر این مطلب را مفید یافتید، لطفا آن را با دوستان و همکاران خود به اشتراک بگذارید.

می توانید از تابع CHAR برای قرار دادن نماد تیک (یا نماد ضربدر) در سلول استفاده کنید. فرمول زیر نماد تیک را باز می گرداند.

=CHAR(252)

البته بایستی فونت سلول را به Wingdings تغییر دهید. چرا؟

فرمول CHAR (252) کاراکتر   ANSI , (ü) را به عنوان نتیجه باز می گرداند و پس از آنکه فونت را به Wingdings تغییر دهید ، به نماد تیک تبدیل می شود.

می توانید فرمول های CHAR مشابه (با کد های مختلف) را برای دریافت فرمت های دیگر نماد تیک یا ضربدر استفاده کنید.

مزیت استفاده از تابع CHAR این است که با استفاده از ترکیب این تابع با توابع دیگر می توانید نماد تیک یا ضربدر را به عنوان نتیجه باز گردانید.

به عنوان مثال ، فرض کنید مجموعه داده ای بصورت زیر در اختیار دارید.

می توانید با استفاده از فرمول زیر برای میزان فروش بیش از 5000 نماد تیک و میزان فروش کمتر از 5000 نماد ضربدر را بازگردانید.

=IF(A2>5000,CHAR(252),CHAR(251))

 به یاد داشته باشید ، فونت ستون B (به غیر از سلول عنوان) را به Wingdings تغییر دهید.

استفاده از این طرح کمک می کند که گزارش های تهیه شده بصری تر باشند. همچنین این طرح برای گزارش های چاپی مناسب است.

اگر می خواهید فرمول را حذف کنید و تنها نمادها را نگه دارید , یا می خواهید نمادها به مقدار سلول وابسته نباشد ، جدول را کپی کرده و آن را به عنوان مقدار در سلول دیگریPaste کنید(راست کلیک کرده و از مجموعه Paste Special روی Values & Source Formating کلیک کنید).

نکته: از آنجا که فونت سلول ها را به Wingdings تغییر می دهید , نمی توانید چیز دیگری به جز نمادها در سلول قرار دهید.

اطلاعات فروش یک فروشگاه , به فرم زیر در فایل اکسل ثبت می شود .

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

بله , استفاده از Pivot Table (جدول محوری) بهترین گزینه است. تنها مشکل این است که , هر بار که داده های اولیه را تغییر می دهید ، جدول محوری را باید refresh (بازسازی) کنید.

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

در این پست , ایجاد و تنظیمات جدول محوری را برای تهیه این گزارش نشان خواهم داد. همچنین در ادامه نحوه تهیه این گزارش با استفاده از فرمول های آرایه ای را نیز بیان خواهم کرد. کارهایی که با جداول محوری انجام می شود بدون آن و بوسیله فرمول نویسی هم می توان انجام داد , اما استفاده از جداول محوری انجام آنها را ساده تر و آسان تر می کند.

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

سلولی از داده های اولیه را انتخاب کنید. از زبانه Insert دکمه‌ Table را کلیک کنید. از کلید های میانبر CTRL+T  نیز می توانید استفاده کنید.

در کادر محاوره ای ظاهر شده , اکسل محدوده کامل داده ها را به صورت خودکار درج می کند ، مطمئن شوید که آدرس درج شده درست است. روی دكمه OK كليك كنيد تا جدول داده ها به جدول اکسل تبدیل شود.

ایجاد جدول محوری

روی یکی از سلول های جدول اکسل کلیک کنید.

زبانه Insert نوار ریبون را کلیک کنید.

دکمه Pivot table را انتخاب کنید.

ادامه مراحل را از تصویر متحرک زیر دنبال کنید:

همانطور که اشاره شد , بعد از هر تغییر در داده های اولیه بایستی جدول محوری را باز سازی کنید. برای این کار , روی یکی از سلول‌های جدول محوری راست كليك کرده و در لیست باز شده گزينه‌‌ Refresh را انتخاب كنيد.

شکل نهایی گزارش

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

و , بهترین راه این است که نکته ها , ترفندها و مثال های کاربردی را که در کتاب جداول محوری ذکر کرده ام و می تواند به شما کمک کند مطالعه کنید.

تهیه گزارش با استفاده از فرمول آرایه ای

فرمول آرایه ای سلول A2

=INDEX(Data!$B$2:$C$21, MATCH(0, COUNTIFS($B$1:B1, Data!$C$2:$C$21, $A$1:A1, Data!$B$2:$B$21), 0), COLUMN(A1))

چگونه یک فرمول آرایه ای ایجاد کنیم

سلول A2 را انتخاب کنید.

فرمول را Paste کنید.

کلیدهای Ctrl+Shift را همزمان نگه داشته و کلید Enter را فشار دهید.

چگونه فرمول آرایه ای را کپی کنیم

سلول A2 را انتخاب و کپی کنید (Ctrl+c).

سلول B2 را انتخاب کنید.

کلید Paste را کلیک کنید (Ctrl+V).

محدوده A2: B2 را انتخاب کنید.

کلید Copy را کلیک کنید (Ctrl+c).

محدوده A3: B15 را انتخاب کنید.

کلید Paste را کلیک کنید.

فرمول سلول C2

=COUNTIFS(Data!$C$2:$C$21, B2, Data!$B$2:$B$21, A2)

نحوه کپی فرمول در سلول C2

سلول C2 را انتخاب کنید.

فرمول را Paste کنید.

کلید Copy را کلیک کنید (Ctrl+c).

محدوده C3: C15 را انتخاب کنید.

کلید Paste را کلیک کنید (Ctrl+V).

فرمول سلول D2

=SUMIFS(Data!$E$2:$E$21, Data!$C$2:$C$21, B2, Data!$B$2:$B$21, A2)

نحوه کپی فرمول در سلول D2

سلولD2 را انتخاب کنید.

فرمول را Paste کنید.

کلیدCopy را کلیک کنید (Ctrl+c).

محدوده D3: D15 را انتخاب کنید.

کلید Paste را کلیک کنید (Ctrl+V).

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

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

یکی از ویژگیهای Clipboard (حافظه موقت) اکسل این است که که اجازه می دهد متن/ فرمول را کپی و ذخیره کنید تا بعدا بتوانید آن را به سرعت بازیابی و در محلی که می خواهید استفاده کنید.

چگونه:

ابتدا باید این ویژگی را (کپی کردن در حافظه موقت) فعال کنید.

1. روی زبانه Home در نوار ریبون کلیک کنید.

2. روی فلش , در پایین سمت راست گروه Clipboard کلیک کنید (در تصویر نشان داده شده است). پنجره حافظه موقت در سمت چپ ظاهر می شود.

3. روی دکمه Option (در پایین سمت چپ پنجره) کلیک و گزینه Show Office Clipboard when Ctrl+C pressed Twice  را انتخاب کنید.

4. قسمت Clipboard را ببندید.

5. حال اگر می خواهید محتویات سلول را کپی کنید ، به حالت ویرایش (با فشار دادن F2) وارد شوید و متن (یا فرمول) را انتخاب کنید.

6. با انتخاب متن ، کلیدهای CTRL+ C + C را فشار دهید (کلید کنترل را نگه دارید و کلید C را دوبار فشار دهید) تا متن / فرمول در حافظه موقت کپی شود.

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

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

چگونه:

1 - عنوان ستون ها را انتخاب کنید (فروردین ، اردیبهشت...).

2 - از زبانه Home در گروه Alignment از لیست Orientation , گزینه Angle Counterclockwise را انتخاب کنید .

3 - در حالی که عنوان ستون ها انتخاب شده است ، در زبانه Home از گروه Font گزینه All Borders را از لیست کادر ها انتخاب کنید.

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

حالا عرض ستون ها را با دبل کلیک در مرز سر ستون های کاربرگ تنظیم کنید.

4 - نکته: شما می توانید به تمام این گزینه ها , از کادر محاوره ای Format Cells نیز دسترسی داشته باشید.

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

چگونه:

1- سلولی که می خواهید با انتخاب آن , پیام نمایش داده شود را انتخاب کنید. از زبانه Data در گروه Data Tools گزینه Data Validation را انتخاب کنید.

2- در کادر محاوره ای Data Validation ، زبانه Message Input را انتخاب کنید.

3- اطمینان حاصل کنید که گزینه selected Show input message when cell is انتخاب شده است.

4- در زبانه Message Input ، عنوان (حداکثر تا 32 کاراکتر) و پیام (حداکثر تا 256 کاراکتر) را وارد کنید.

Ok -5 را کلیک کنید.

---