ترکیب محتویات تعداد زیادی سلول در یک سلول در اکسل

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

برای حل این مشکل 3 روش وجود دارد:

روش 1) با استفاده از اکسل:

فرض کنید لیستی مشابه لیست زیر (A1 تا A11) دارید و قصد دارید متن تمام سلول‌ها را با هم ترکیب کنید.

قبل از حل مشکل فوق ابتدا باید با تابع Transpose آشنا شویم.

تابع Transpose:
کلمه Transpose به معنای ترانهادن است و در اکسل با استفاده از تابع Transpose می‌توان مکان ردیف‌ها و ستون‌ها را تغییر داد. همانطور که در تصویر زیر مشاهده می‌کنید جدول زیر دارای 7 ردیف و 5 ستون می‌باشد.

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

=TRANSPOSE(A1:E7)

برای اینکه عمل ترانهادن برای همه 35 سلول جدول فوق انجام شود باید از فرمول‌نویسی آرایه‌ای استفاده کرد که آموزش داده شده است. بنابراین برای اینکه فرمول فوق بصورت آرایه‌ای عمل کند بعد از وارد کردن فرمول بالا، کلیدهای Ctrl+Shift+Enter را همزمان فشار دهید تا نتیجه زیر حاصل شود.

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

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

=CONCATENATE(TRANSPOSE(A1:A11))

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

=CONCATENATE(TRANSPOSE(A1:A11&" "))

یا

=CONCATENATE(TRANSPOSE(A1:A11&&","))

2- پس از تایپ فرمول، کلید اینتر را فشار ندهید. قسمت (" "&TRANSPOSE(A1:A11 را انتخاب کنید و کلید F9 را فشار دهید. اگر به هر دلیلی نمی‌توانید از F9 استفاده کنید کلیدهای Ctrl و = را همزمان فشار دهید.

نکته: برای نمایش مقدار واقعی یک فرمول، کافیست فرمول مورد نظر را انتخاب کرده و کلید F9 را فشار دهید. در صورتیکه کلید Esc را فشار دهید مجدداً فرمول مربوط به آن سلول نشان داده می‌شود.

3- مشاهده می‌کنید که متن تمام سلول‌های A1 تا A11 ظاهر شده است درحالیکه بین‌ آن‌ها ویرگول و اطرافشان کروشه {} قرار دارد. کروشه‌های دو طرف را پاک کنید.

4- حالا کلید اینتر را فشار دهید و نتیجه را ببینید. تمام.

نکته: اگر سلول‌هایی که قصد دارید با هم ترکیب کنید بصورت ردیفی بودند مثلا A1 تا K1 دیگر نیازی نیست از دستور TRANSPOSE استفاده کنید. فقط پس از فشردن F9 کروشه‌ها را پاک کنید.

منبع: (2, 1) chandoo.org 

روش 2) با استفاده از Notepad و Word:

برای ترکیب سلول‌ها در خارج از اکسل، باید از Word استفاده کرد. اما ابتدا باید کلمات را از جدول خارج کنیم. می‌توان جدول را مستقیما در ورد از بین برد یا اینکه ابتدا در Notepad اینکار را انجام داد و سپس به ورد منتقل کرد. کلمات را اگر از لیست اشاره شده در روش 1 را کپی کنید و سپس در Notepad پیست کنید جدول از بین می‌رود و تبدیل به متن می‌شود بصورت زیر:

سپس می‌توان آن‌ها را به ورد انتقال داد تا سایر عملیات روی‌ آن‌ها انجام شود. البته بدون نیاز به Notepad هم می‌توان جدول را در ورد از بین برد. اگر بعد از کپی کلمات در اکسل، آن‌ها را بصورت معمولی (Ctrl+V) در ورد پیست کنید کلمات همراه با جدول در ورد پیست می‌شوند و نمی‌توان آن‌ها را با هم ترکیب کرد. برای از بین بردن جدول کافیست زمانیکه می‌خواهید لیست را در ورد پیست کنید در هنگام پیست گزینه Keep text only را انتخاب کنید. در اینصورت کلمات بصورت text و بدون جدول در ورد کپی می‌شوند.

در پایان کافیست خطوط را به هم بچسبانید تا کلمات کنار هم قرار بگیرند. ب

روش 3) با استفاده از ++Notepad

2. ستونهای موردنظر خود را انتخاب کنید.

 3. در منوی Data  زیر منوی  Pivot table and Pivot chart report… را کلیک نمایید

 4. در پنجره ظاهر شده کارهای درخواست شده را انجام دهید.

5. متغیرهای مورد نظر خود را در ستون و ردیف معرفی شده قرار دهید.

6. متغیری که قرار است شمارش شده و در جدول ثبت شود را به قسمت وسط جدول انتقال دهید. عمل انتقال متغیرها با Click and                                                                                                                                                                                                                                    محاسبه ی میانگین وانحراف معیار در اکسل                                                                                                                                                           1- ابتدا اعداد مورد نظر خود را در یک ستون در نرم افزار اکسل وارد نمایید.

2. اعداد نوشته شده را انتخاب نمایید.

3. Insert Function را در منوی Formulas کلیک نمایید.

4. Average و یا STDEV را انتخاب نمایید.

5. در خانه ای که می خواهید نتیجه محاسبه در آن قرار بگیرد، کلیک نمایید.

6. در پنجره ظاهر شده محدود اعداد دلخواه خود را انتخاب نمایید.

7. میانگین و یا انحراف معیار در جای مورد نظر ثبت می شود.

• توابع جستجودر excel
  

1-  تابع Find:

با استفاده از این تابع می‌توان در متن مورد جستجو موقعیت آغاز یک کاراکتر (حرف، عدد و کلمه) را پیدا کرد. لازم به ذکر است که فاصله (space) نیز یک کاراکتر محسوب می‌شود.  ساختار (Syntax) این تابع به شکل زیر است:

=FIND(find_text , within_text , start_num)

این تابع دارای 3 آرگومان (پارامتر یا شیء) است:

1-   find_text (آرگومان 1): عبارتی که بایستی جستجو شود که می‌تواند یک حرف یا یک کلمه یا متن باشد.

2-  within_text (آرگومان 2): متن مرجع که در آن به جستجو خواهیم پرداخت. این متن نیز می‌تواند یک حرف یا یک کلمه یا متن طولانی باشد.

-   Start_num (آرگومان 3): نقطه آغاز جستجو است. در واقع مشخص می‌کنیم که در آرگومان 2 از چندمین کاراکتر شروع به جستجو کند که در این صورت کاراکترهای قبلی جستجو نخواهند شد. این آرگومان از یک شروع و تا تعداد کاراکتر موجود در آرگومان 2 می‌تواند باشد.

توجه: درصورتیکه عدد صفر یا عددی بیش از تعداد کاراکتر موجود در آرگومان 2 به آن اختصاص یابد خطای #VALUE! را نشان خواهد داد.

توجه: اگر آرگومان 1 در آرگومان 2 موجود نباشد خطای #VALUE! را خواهیم دید.

2- تابع :Search

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

=Search(find_text , within_text , start_num)

• توابع جایگزینی در اکسل:

1- تابع REPLACE:

از تابع REPLACE برای جایگزین کردن قسمتی از یک رشته‌ی متنی توسط رشته‌ی متنی جدید استفاده می‌گردد، ساختار (Syntax) این تابع به شکل زیر است:

=REPLACE(old_text,start_num,num_chars,new_text)

این تابع دارای 4 آرگومان است:

1-   old_text (آرگومان 1): متن قدیمی که قصد دارید عملیات جایگزینی را در آن انجام دهید.

 -   Start_num (آرگومان 2): نقطه آغاز جایگزینی در متن قدیمی. در واقع مشخص می‌کنیم که در آرگومان 1 از چندمین کاراکتر جایگزینی شروع شود. این آرگومان از یک شروع و تا تعداد کاراکتر موجود در آرگومان 1 می‌تواند باشد.

 2-  num_chars (آرگومان 3): تعداد کاراکتری که در متن قدیمی باید با متن جدید جایگزین شوند.

      new_text (آرگومان 4): متن جدید که باید در متن قدیم جایگزین شود.

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

=REPLACE(“09122048458″,1,1, “+98″)

فرمول بالا عدد صفر ابتدای شماره تماس را با عبارت 98+ جایگزین می‌کند.
 

2- تابع SUBSTITUTE:

تابع SUBSTITUTE همانند تابع REPLACE برای جایگزین کردن بخش از یک رشته‌ی متنی با رشته‌ی متنی جدید مورد استفاده قرار می‌گیرد با این تفاوت که در این تابع دقیقاً مشخص می‌کنیم چه بخشی (دقیقاً کدام کاراکترها) از یک رشته‌ی متنی با رشته‌ی متنی جدید باید جایگزین شود، ساختار این تابع مانند زیر است:

=SUBSTITUTE(text,old_text,new_text,[instance_num])

این تابع دارای 4 آرگومان است:

1-   text (آرگومان 1): متن مرجع که قصد دارید عملیات جایگزینی را در آن انجام دهید.

1-   old_text (آرگومان 2): کاراکترهایی از رشته‌ی متنی مرجع می‌باشد که قرار است با کاراکترهای جدید جایگزین شوند.

      new_text (آرگومان 3): متن جدید که باید در متن قدیم جایگزین شود.

      [instance_num] (آرگومان 4): این آرگومان اختیاری است و زمانی استفاده می‌شود که آرگومان 2 چند مرتبه در متن مرجع تکرار شده باشد و بخواهیم تنها یک مورد از آن‌ها جایگزین شود. در این حالت با استفاده از آرگومان 4 مکان آن را مشخص می‌کنیم.

حال می‌خواهیم مانند مثال تابع REPLACE عدد صفر را با 98+ جایگزین کنیم:

=SUBSTITUTE(“09122048458″,”0″, “+98″)

خروجی این فرمول عبارت “989122+9848458+” خواهد بود، در واقع تمام صفرهای پیدا شده با عبارت “98+” جایگزین می‌شوند. در این حالت باید از آرگومان 4 استفاده کرد.اگر این آرگومان خالی باشد (مانند مثال بالا) به این معنی است که تمام صفرها باید جایگزین شوند و اگر به جای این آرگومان عدد 1 نوشته شود، تنها صفر اول (اولین صفر پیدا شده در رشته) با عبارت “98+” جایگزین می‌شود، مانند زیر:

=SUBSTITUTE(“09122048458″,”0″, “+98″, 1)

در نرم افزار اکسل  مقادیر عددی که در برگه های کاری خود وارد می کنید معمولاً فقط عدد نمی باشند بلکه نشان‌دهنده مقدار دلار، تاریخ، درصد و مقادیر واقعی دیگر می باشند. بصورت پیش فرض، وقتی که عددی را وارد می کنید، به فرمت General نمایش داده می شود که معمولاً به همان صورتی است که آن را تایپ می کنید. ولی بجای تایپ $12.95، می توانید تایپ کنید 12.95 و بعد یک فرمت عددی مانند فرمت Currency را روی آن اعمال کنید. برای مثال، 5.05 می‌تواند معانی گوناگون داشته باشد ولی اگر فرمت Currency آن را اعمال کنید به صورت $5.05 در‌می‌آید و اگر فرمت Percent age را روی آن اعمال کنید بصورت 505% در می آید. در این کار عملی، یاد می گیرید که چگونه با استفاده از دکمه های نوار ابزار و کادر محاوره ای Format Cells، فرمت اعداد را تغییر دهید.

 سلول یا محدوده مورد نظر را انتخاب کنید  

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

 رویCurrency کلیک کنید  

 برای تغییر فرمت به فرمت پولی (که $ را اضافه می کند، عدد را تا دو رقم اعشار گرد می کند و$ را طوری قرار می دهد که تمام علامت‌های دلار در ستون تراز شوند)، روی دکمه Currency Style در نوار ابزارFormatting کلیک کنید.

 روی Percent کلیک کنید  

برای تغییر فرمت به فرمت درصد (که علامت % را اضافه می کند و عدد را ازحالت کسری یا عدد صحیح به مقدار درصد تبدیل می کند)، روی دکمه Percent Style در نوار ابزارFormatting کلیک کنید.(می دانید که درصد یعنی صدم. بنابراین 0.12 بصورت 12% در می آید ولی 12 بصورت 1200% در می آید).

 رویComma کلیک کنید  

 برای بکار بردن فرمت کاما (که در ایالات متحده به این معنی است که عدد تا دو رقم اعشار گرد شود و بصورت سه رقم سه رقم با کاما جدا شود)، روی دکمه Comma Style در نوار ابزار Formatting کلیک کنید. برای کنترل نقطه اعشاری همچنین می‌توانید روی دکمه های Increase Decimal و Decrease Decimalدر نوار ابزار Formatting کلیک کنید تا نقطه اعشاری جابجا شود.

 از کادر محاوره ای Format Cells استفاده کنید  

 برای بکار بردن فرمت هایی که در نوار ابزار Formatting موجود نباشند، از منوی Format، گزینه Cells را انتخاب کنید تا کادر محاوره ای Format Cells باز شود. روی برگه Number کلیک کنید و از لیست Category، یکی از فرمت ها را انتخاب کنید. مقدار سلول انتخاب شده با آن فرمت در قسمت Sample نشان داده می شود.

 گزینه های مربوطه به Format را تنظیم کنید  

فرمت های مختلف در کادر محاوره ای Format Cells، گزینه متفاوتی را ارائه می کنند. یکی از گروه ها (Category) را انتخاب کنید و گزینه های مربوط به آن فرمت را تنظیم کنید. شرح گروه انتخاب شده در پایین کادر محاوره ای ظاهر می شود. برای بستن کادر محاوره ای و اعمال کردن فرمت جدید روی Ok کلیک کنید.

* توجه *

کپی کردن فرمت

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

Carrency واقعاً Carrency نیست

اگر چه یکی از دکمه های نوار ابزار، Carrency Style نامیده می شود فرمت Carrency را اعمال نمی کند، بلکه فرمتAccounting را اعمال می کند. برای اعمال کردن فرمت Carrency واقعی (که در آن، علامت بی‌توجه به اینکه ستون چه پهنایی دارد، بلافاصله در سمت چپ عدد قرار می گیرد) از کادر محاوره‌ای Format Cells استفاده کنید.

حذف فرمت دهی در اکسل

برای حذف تمام فرمت دهی ها (از جمله فرمت عددی)، بدون حذف داده ها از یک سلول، آن سلول یا محدوده را انتخاب کنید و بعد از منویEdit، گزینه Clear و بعد گزینه Formats را انتخاب کنید.

تابع، یک فرمول از پیش نوشته شده در برنامه است. آرگومان های (متغییرهای) مورد نیاز به تابع داده می شود و تابع، نتیجه را محاسبه می کند. برای تابعSUM، تنها چیزی که باید مشخص کنید محدوده ای است که می خواهید جمع کنید نوار ابزارStandard، امکان دستیابی سریع به SUM و نیز توابع پر استفاده دیگر مانند AVERAGE (محاسبه معدل)، MIN (کوچکترین عدد)، MAX (بزرگترین عدد) وCount (محاسبه تعداد آیتم ها) را فراهم می کند. در آموزش اکسل پیشرفته امروز   یاد می‌گیریم که چگونه از توابع استفاده کنید.

 یک محل برای نتیجه، انتخاب کنید  

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

 روی AutoSum کلیک کنید  

در نوار ابزار Standard، روی دکمه AutoSum کلیک کنید.

 فرمول SUM درج می شود  

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

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

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

 با استفاده از AutoSum، معدل حساب کنید  

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

 ورود اطلاعات را کامل کنید  

یک نوار چشمک زن، دور محدوده ای که Excel فکر می کند می خواهید در این تابع استفاده کنید را فرا می گیرد. اگر لازم باشد با کشیدن ماوس، محدوده دیگری را انتخاب کنید وبعدEnter را بزنید. نتیجه در سلول مورد نظر نشان داده می شود و فرمولی که برای محاسبه این نتیجه بکار رفت در نوار فرمول ظاهر می شود.

*توجه  *

 جمع کردن کل سلول های یک جدول در اکسل

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

جمع کردن بیش از یک محدوده در excel

برای جمع کردن مقادیر بیش از یک محدوده، روی دکمه AutoSum کلیک کنید، محدوده اول را انتخاب کنید، کلید کاما (,) را بفشارید و بعد محدوده بعدی را انتخاب کنید. بین تمام محدوده های بعدی هم باید کاما تایپ کنید.

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

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

استفاده از این افزونه باعث صرفه جویی بسیار زیاد در وقت و زمان شما خواهد شد.

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

پاک کردن ردیف های خالی از طریق منوی فیلتر ستون در اکسل

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

تبدیل مقادیر زمانی به سال

این آپشن جدید تبدیل داده ها، امکان محاسبه مجموع سالها را طریق ستونی از نوع ” مدت زمان” فراهم می کند. (اکسل مجموع تعداد روزها را بر 365 تقسیم خواهد کرد) برای دسترسی به این امکان کاربر باید از طریق سربرگ Transform یا Add columns  گزینه ی Total years را انتخاب کند.

نگه داشتن تکراری ها

در مسیر Home > Remove Duplicates  گزینه ی جدیدی تحت عنوان Keep duplicates در زیر گزینه ی Remove duplicates اضافه شده است. گزینه ی جدید این امکان را به کاربر می دهد که تنها ردیف هایی را که در ستون حاضر دارای مقادیر مشترک و تکراری هستند نگه داشته و سایر ردیف های را حذف کند.

افزودن راهنما برای sample input values در پنجره ی Change Type with Locale در اکسل 

این قابلیت تبدیل جدید؛ به کاربر این امکان را می دهد تا فیلد های موجود در یک ستون را بر حسب تنظیمات محلی تغییر دهد.

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

(Data >New Query > Query Options > Current Workbook > Data Load)

با کلیک راست بالای ستون در Query Editor preview و انتخاب Change Type > Using Locale  پنجره ی Change Type with Locale به نمایش در خواهد آمد که این امکان را به کاربران می دهد که در زمان انتخاب نوع خاصی از داده یا محلی سازی؛ به تعدادی مقادیر نمونه دسترسی داشته باشند.

پشتیبانی از فضای سفید در Query Editor preview

Query Editor preview به کاربران اجازه خواهد داد تا نمایش هر نوع کاراکتر های سفید را در سلول ها مشاهده کنند

هرچند برخورد پیشفرض برنامه با این کاراکتر ها بر روی show whitespace تنظیم شده، اما کاربران همچنان این امکان را خواهند داشت از سربرگ View در نوار Query Editor این تنظیم را تغییر دهند

قابلیت غیرفعال سازی پیشنمایش در پنجره ی Navigator

در منوی Display options کاربر این امکان را خواهد داشت تا پیشنمایش را در پنجره ی Navigator غیرفعال کند

پشتیبانی از Technical name در پنجره ی Navigator

این قابلیت جدید به کاربر اجازه می دهد تا نحوه ی آدرس دهی را از physical name به technical name تغییر دهد.

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

تغییر نام کوئری ها از کوئری موجود در Query Editor

درون Query Editor کاربر می تواند به سرعت با کلیک راست بر روی کوئری ها یا دابل کلید و انتخاب آنها و سپس فشردن کلید F2  کوئری ها را تغییر نام دهد.

پشتیبانی از تنظیم مقدار مجاز وقفه در اجرای یک دستور

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

امکان غیرفعال سازی تنظیمات حریم خصوصی موجود در سطح ماشین (شامل کلید های رجیستری)

از آنجا که Power Query به کاربران اجازه می دهد داده ها را از منابع متعدد در یک ورک بوک ادغام کنند تنظیمی تحت عنوان Privacy Levels وجود دارد که کاربران می توانند برای هر منبع که برای ادغام اطلاعات به آن وصل می شوند یکی از سطوح عمومی، سازمانی و یا خصوصی را تعیین کنند.

از آنجا که سطوح متفاوت privacy کاربران را از انواعی از ترکیبات و ادغام داده های که ناقض این تنظیمات است باز می دارد، تیم آفیس به کابران اجازه می دهند تا یکی از سه حالت زیر را از طریق پنجره ی Query Options انتخاب کنند.

• ترکیب داده های بر پایه ی سطوح Privacy برای هر منبع – این امکان جدید به کاربر اجازه می دهد تا سطوح Privacy موجود را بر روی تمام فایل های.XLSX موجود اعمال کند.
• ترکیب داده ها بر اساس سطوح Privacy هر فایل – عملکرد پیشفرض که با عملکرد موجود در نسخه های قبلی یکیست این گونه است.

• نادیده انگاشتن سطوح Privacy – این قابلیت جدید به کاربر اجازه می دهد تا همیشه بدون توجه به تنظیمات ورک بوک حاضر، از محدودیت Privacy برای هر کدام از فایلهای.XLSX عبور کنند.

علائم ظاهری این خطا در Excel و نحوه بیان آن از طرف شما

* هر بار که Excel را باز می‌کنم یک پیغام می‌دهد و با OK کردن نیز رفع نمی‌شود و بار دیگر نشان داده می‌شود.
* یک فلش آبی رنگ روی صفحه Excel می‌آید و اصلا انتخاب و پاک نمی‌شود.
* نتیجه فرمول SUM عدد صفر می‌شود و این در حالی است که من کلی عدد در محدوده Sum دارم.

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

توضیح Circular Reference در Excel

برای توضیح این خطا یک مثال ساده آورده می‌شود تا دقیقا بفهمید که چه بر سر Excel  می‌آید. فرض کنید که در یک اداره رفته‌اید و باید نامه‌ای امضا شود، آقای کارمند الف می‌گوید ” نامه شما ابتدا باید به  تایید آقای جیم برسید ” و وقتی که به اتاق آقای “جیم” می‌روید، می‌گوید “این نامه ابتدا باید توسط آقای الف کارشناسی و تایید شود”.
و نتیجه این کار پاس‌کاری شما یا قرار گرفتن در یک “دور باطل” یا به قول ما کامپیوتری‌ها Loop خواهد بود که در Excel به این حالت Circular Reference می‌گویند.

Circular Reference در Excel

 در خانه B2 فرمولی را بنویسید که اشاره به خانه E2 داشته باشد (مهم نیست چه فرمولی، اما در آن حتما E2 آمده باشد) سپس در خانه E2 فرمولی را بنویسید که اشاره به خانه B2 داشته باشد.
بعد زدن کلید Enter در سلول دوم Excel بلافاصله پیغام زیر را به شما نشان می‌دهد و اگر کلید OK را بزنید Help نرم افزار Excel باز شده و Circular Reference را توضیح می‌دهد.

البته حالت‌های دیگری نیز می‌توان مثال زد، مثلا شما در سلول B2 فرمولی مانند زیر را بنویسید. چون خود سلول در محدوده تابع SUM است در نتیجه Circular Reference ایجاد خواهد شد.

آوردن فلش آبی رنگ در اکسل

بعد اینکه این حالت را ایجاد کردید، اگر خانه E2 را ویرایش کنید (روی E2 کلید F2 را بزنید و سپس Enter) این فلش به صورت خودکار نمایش داده می‌شود و بیانگر ایجاد  Circular Reference در Excel است.

روش دیگری برای تشخیص Circular Reference

هموار در Status Bar شما می‌توانید آدرس اولین سلولی که دچارCircular Reference  شده است را ببنید. که به محض رفع کردن خطای این سلول، آدرس سلولهای بعدی را نشان می‌دهد.

از ریبون (به نوار ابزار Excel 2007 ریبون می‌گویند) گزینه Formula و سپس مانند شکل زیر اقدام نمایید:

نتیجه فرمولهایی که به سلول‌های Circular Reference اشاره دارند، همواره صفر است

تا اینجا با مفهوم Circular Reference در Excel آشنا شده‌اید و حال به نحوه رفع این خطای آشنا خواهیم شد.

روش رفع خطا در اکسل                                                                                                                

دو راه کلی برای رفع این خطا وجود دارد که راه اول پاک کردن یا اصلاح فرمولی است که باعث CR شده است که سلولی که باعث CR شده است را ویرایش کرده و فرمول آنرا طوری اصلاح می‌کنیم که باعث Loop نشود.
راه حل دیگر فعال کردن گزینه Iteration در Excel Options است. در این وضعیت ما CR را کنترل می‌کنیم و نه حذف. بدین ترتیب که به Excel می‌گوییم که اگر در جایی دچار Loop شد، این Loop را 100 بار اجرا کن (این حلقه را 100  بار ادامه بده) و سپس کار را تمام کن.
برای فعال کردن وضعیت Iteration در Excel Options گروه Formula و سپس Enable iterative Calculation را در حالت فعال قرار می‌دهیم و تعداد دفعات اجرای را تعیین می‌کنیم.

استفاده از این تکنیک برای ساختن Loop در اکسل اصلا توصیه نمی‌شود زیرا شما باید Calculation رادر حالت Manual قرار دهید و … و در ضمن ابزارهای کنترلی برای خارج شدن از Loop به سختی ساخته می‌شوند و این در حالی است که در VBA این کار به سادگی با دستوراتی مانند DO-LOOP  /  FOR-NEXT قابل اجراست و اگر شما در کتابهای مرجع Excel و یا اینترنت جستجو کنید خواهید دید که از این قابلیت سخنی به میان نرفته است و دلیل آن همان است که ذکر شد.

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

10٫4 به صورت عدد 10 روند می شود.
10٫6 به صورت عدد 11 روند می شود.
10٫5 به صورت عدد 11 روند می شود

=Round(12.3456,1)

Result: 12.3

آرگومان دوم عدد 1 است. این عدد با یک رقم اعشار روند می شود.

=Round(12.3456,2)

Result: 12.34

آرگومان دوم عدد2 است.این عدد با دو رقم اعشاری روند می شود. توجه داشته باشید که عدد اعشاری.3456 تبدیل به.35 می شود. این امر به این دلیل است که قسمت.456 مربوط به مقدار اعشاری. با توجه به نزدیکترین رقم با رقم اعشاری دوم که.05 است. روند می شود.

روند کردن در یک جهت – توابع (Roundup – RoundDwon)

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

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

=Roundup(150.255,0)

Result:151

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

=Roundup(150.255,1)

Result: 150.3

آرگومان دوم عدد 1 است. نتیجه به این صورت یک رقم اعشاری روند می شود. توجه داشته باشید که مقدار اعشاری کامل.255 به مقدار.3 روند بسته می شود. این امر در تابع استاندارد Round هم اتفاق می افتد.

=RoundDown(155.798,2)

Result: 155.79

آرگومان دوم عدد 2 است. نتیجه به صورت عددی با یک رقم اعشاری روند بسته می شود. توجه داشته باشید که قسمت اعشاری.798 تبدیل به.79 می شود. تابع استاندارد Round این رقم اعشاری را به صورت.8 روند می کند.

روند کردن با عدد زوج یا عدد فرد بعدی – توابع (Even – Odd)

توابع Even و ODD اعداد را با دور کردن آنها از 0 روند می کنند. تابع Even یک عدد را به بالاترین عدد صحیح زوج بعدی روند خواهد کرد و تابع ODD یک عدد را به بالاترین عدد صحیح فرد بعدی روند خواهد کرد.

=Even(3)

Result: 4

با دور شدن از 0 روند کردن را به طرف عدد صحیح زوج بعدی. انجام می دهد.

=Even(4)

Result: 4

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

=Even(4.01)

Result: 6

با دور شدن از 0. روند کردن را به طرف عدد صحیح زوج بعدی انجام می دهد.

=ODD(4)

Result: 5

با دور شدن از 0. روند کردن را به طرف عدد صحیح فرد بعدی انجام می دهد.

حذف تمام ارقام اعشاری به وسیله تابعINT   در excel

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

=INT(25.27)

Result: 25

حذف برخی از ارقام اعشاری به وسیله تابع TRUNC

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

=TRUNC(212.65,2)

Result: 212.65

=TRUNC(212.65,1)

Result: 212.6

روند تا دو رقم اعشار

Example: 1.2649 to 1.26
Round(A1,2)

روند به بالا تا دو رقم اعشار

Example: 1.2649 to 1.27
Roundup(A1,2)

روند به نزدیکترین عدد صحیح

Example: 1.2649 to 1
=Round(A1,0)

روند به نزدیکترین مضرب 10

Example: 544.234 to 550
Roundup(A1,-1)

روند به نزدیکترین مضرب عدد 2

Example: 43 to 44
MRound(A1,2)

جدا کردن مقدار صحیح یک عدد اعشاری

Example: -23.34 to -23
TRUNC(A1,0)

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

Example: 19.14 to 19.25

MRound(A1,0.25)

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

ضرب اعداد به وسیله تابع Product

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

=Product(A1,A2)

همچنین شما می توانید همان عمل ضرب را با استفاده از علامت ریاضی (*) نیز انجام دهید.

=A1*A2

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

PRODUCT(number1, [number2], …)

Number 1: اولین عدد یا محدوده برای انجام عملیات ضرب.

Number 2: عدد یا محدوده های اضافی برای انجام عملیات ضرب. می توانید تا 255 آرگومان در این تابع استفاده کنید.

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

=PRODUCT(A1:A3)

=PRODUCT(A1,A2,A3)

=PRODUCT(A1:A3,B1:B2)

=PRODUCT(A1,B2,C2)

=A1*A2*A3

استفاده از تابع Sum Product برای ضرب و جمع زدن مقادیر هم موقعیت در دامنه های مختلف درEXCEL

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

SumProduct(َArray1,Array2,Array3,…..)

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

SumProduct(B2:C4,E2:F4,H2:I4)

نتیجه از طریق موارد زیر محاسبه خواهد شد:

1- ضرب A در H در N و ذخیره نتیجه.

2- ضرب D در K در Q و ذخیره نتیجه.

3- ضرب E در L در O و ذخیره نتیجه.

4- و ……….

5- و در آخر جمع زدن نتایج برای به دست آوردن پاسخ نهایی.