فرمول های EXCEL با مثال - دستورالعمل استفاده. تفاوت بین پیوندهای مطلق، نسبی و مختلط

30.10.2019 نکات

برای درک نحوه استفاده از این برنامه، باید فرمول های EXCEL را با مثال در نظر بگیرید.

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

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

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

اگر عملکرد برای کاربر ناشناخته باشد، می تواند دسته بندی "فهرست الفبایی کامل" را تنظیم کند.

برای مثال، با توجه به وظیفه، تابع SUMIFS را پیدا کنید. برای انجام این کار، به دسته توابع ریاضی بروید و توابع مورد نیاز خود را در آنجا پیدا کنید.

تابع VLOOKUP

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

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

محاسبه VLOOKUP را می توان در مثالی مشاهده کرد که لیستی از نام خانوادگی را نشان می دهد. وظیفه این است که نام خانوادگی را با شماره داده شده پیدا کنید.

اعمال تابع VLOOKUP

فرمول نشان می دهد که اولین آرگومان تابع سلول C1 است.

آرگومان دوم A1:B10 محدوده ای برای جستجو است.

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

محاسبه نام خانوادگی با استفاده از تابع VLOOKUP

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

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

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

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

این فقط دو مقدار دارد - "نادرست" یا "درست". اگر هیچ آرگومانی داده نشود، به طور پیش‌فرض روی true می‌شود.

گرد کردن اعداد با توابع

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

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

عدد با استفاده از فرمول ROUNDUP گرد می شود. برای این کار باید سلول را پر کنید.

آرگومان اول 76.375 و دومی 0 است.

گرد کردن عدد با فرمول

در این مورد، عدد به بالا گرد شده است. برای گرد کردن یک مقدار به پایین، تابع ROUNDDOWN را انتخاب کنید.

گرد کردن به یک عدد صحیح رخ می دهد. در مورد ما، تا 77 یا 76.

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

این برنامه بیشتر توسط طراحان، کارآفرینان و همچنین دانشجویان استفاده می شود.

تمام حقیقت در مورد فرمول های Microsoft Excel 2007

فرمول های EXCEL با مثال - دستورالعمل استفاده

3. نتیجه محاسبات در سلول C1 خواهد بود:

4. برای درج اعداد 1 تا 10500 در یک ستون از چه دستوری باید استفاده کرد؟ 1) دستور "پر" در منوی "ویرایش".

5. چه قالب بندی برای سلول های اکسل قابل اجرا است 4) همه گزینه ها صحیح است

6. کدام عملگر در گروه عملگرهای حسابی قرار نمی گیرد؟ 3)&

7- کدام یک از موارد زیر از ویژگی های سلول نیست؟سایز 3

8. یک سلول چه مقدار می تواند بگیرد 4) تمام موارد فوق

9. آرگومان تابع چه چیزی می تواند باشد؟ 4) همه گزینه ها صحیح است

10. تعیین آدرس یک سلول در یک فرمول نامیده می شود 1) پیوند

11. برنامه اکسل برای 2) صفحات گسترده ایجاد کنید

12. فرمول در اکسل با چه کاراکتری شروع می شود 1)=

13. هر نمودار بر اساس چه چیزی ساخته شده است؟ 4) جدول داده ها

14. توالی اجرای عملگرها در فرمول در کدام نوع به درستی نشان داده شده است؟ 3) عملگرهای مرجع و سپس عملگرهای مقایسه

15. حداقل جزء جدول است 1) سلول

16. تابع SUM برای چیست؟ 2) برای بدست آوردن مجموع اعداد نشان داده شده

17. چند نوع آدرس دهی سلول در اکسل وجود دارد 2) دو

18. اگر فرمول حاوی خطا باشد اکسل چه کار می کند؟ 2) پیام نوع خطا را به عنوان مقدار سلول نمایش می دهد

19. پنجره دستوری "Form..." برای چه استفاده می شود؟ 1) برای پر کردن ورودی های جدول

20. کدام یک از پیوندها مطلق است 3) 5 دلار استرالیا

21. به ترتیب مقادیر محدوده ای از سلول ها در یک دنباله خاص گفته می شود 4) مرتب سازی

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

26. محدوده سلول های A1:D3 صفحه گسترده MS EXCEL انتخاب شده است. محدوده شامل 4) 12 سلول

27. محدوده معیارها در MS Excel استفاده می شود که 1) اعمال فیلتر پیشرفته

2). 1, 2, 4

29. برای حل معادله با یک مجهول در MS Escel می توانید از گزینه استفاده کنید 3) انتخاب پارامتر

واژه پرداز

1. اگر در گفتگوی «گزینه‌های صفحه» مقیاس صفحه را «بیش از 1 صفحه عرض و 1 صفحه بالا نباشد» تنظیم کنید، در هنگام چاپ، اگر صفحه بزرگتر از این اندازه باشد، ...

یک). صفحه به این ابعاد بریده می شود

2). صفحه به این اندازه کاهش می یابد

3). صفحه چاپ نمی شود

چهار). صفحه به این اندازه بزرگ خواهد شد

2. مایکروسافت ورد عبارت است از: 3) ویرایشگر متن

3. Microsoft Word را باز کنید: 3) شروع - برنامه ها - مایکروسافت ورد

4. در یک ویرایشگر متن، پارامترهای اصلی هنگام تنظیم یک فونت هستند 1) نوع، اندازه، سبک

5. در فرآیند قالب بندی متن تغییر می کند 2) گزینه های پاراگراف

6. در یک ویرایشگر متن، پارامترهای اصلی هنگام تنظیم پارامترهای پاراگراف هستند 2) تورفتگی، فاصله

7. در یک ویرایشگر متن، پیش نیاز انجام عملیات کپی است 4) انتخاب یک قطعه از متن

8. در یک ویرایشگر متن، هنگام تنظیم پارامترهای صفحه، 3) زمینه ها، جهت گیری

9. در فرآیند ویرایش متن تغییر می کند 3) دنباله ای از شخصیت ها، کلمات، پاراگراف ها

10. حداقل شی مورد استفاده در یک ویرایشگر متن است 4) آشنایی (نماد)

11. در یک ویرایشگر متن، عملیات کپی پس از آن امکان پذیر می شود 4) انتخاب یک قطعه از متن

12. برای فعال کردن حالت سفارشی سازی منو در ویرایشگر متن MS Word، باید دستور را اجرا کنید. 4) تنظیمات سرویس

2) 2 - ب

4)2, 4, 5

17. گروه کنترل نوار ابزار "بازبینی" شامل عناصر برای 1) ایجاد، مشاهده و حذف یادداشت ها

18. هنگام وارد کردن کاراکترها از صفحه کلید با فشردن کلید 4)درج، بین حالت های درج و جایگزینی حرکت کنید.

19. ایجاد سند: 1) فایل - ایجاد کنید

20. سند را باز کنید 4) شروع - اسناد

22. اسناد معمولاً نگهداری می شوند: 2) در پوشه "My Documents""

23. یک حالت نمایش سند را انتخاب کنید که به طور خاص برای تایپ استفاده می شود: 1) معمولی

24. الگوریتم چاپ سند صحیح را انتخاب کنید: 3) پیش نمایش ایجاد کنید، فایل - چاپ - چاپگر را انتخاب کنید - تعداد کپی ها را مشخص کنید - Ok

25. به کدام لیست "مشخص شده" می گویند: 2). هر خط با یک نشانگر شروع می شود - یک کاراکتر خاص

26. کدام نوار ابزار برای کار با جداول طراحی شده است: 2) جداول و حاشیه

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

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

در زیر مثالی از فرمولی است که 2 را در 3 ضرب می کند و 5 را به نتیجه اضافه می کند تا 11 به دست آید.

فرمول زیر از تابع استفاده می کند PMTبرای محاسبه پرداخت وام مسکن (1073.64 دلار) با نرخ 5٪ (5٪ تقسیم بر 12 ماه برابر است با بهره ماهانه) برای یک دوره 30 ساله (360 ماه) با وام 200000 دلاری:

PMT(0.05/12;360;200000)

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

=A1+A2+A3مجموع مقادیر سلول های A1، A2 و A3 را محاسبه می کند.

=ROOT(A1)از تابع استفاده می کند ROOTبرای برگرداندن جذر عدد در سلول A1.

=TODAY()تاریخ فعلی را برمی گرداند.

=UPPER("سلام")متن "hello" را با استفاده از تابع به "HELLO" تبدیل می کند سرمایه، پایتخت.

= اگر (A1>0)سلول A1 را تجزیه می کند و بررسی می کند که آیا مقدار موجود در آن بزرگتر از صفر است یا خیر.

عناصر فرمول

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

1. توابع. تابع PI () مقدار pi را برمی گرداند: 3.142...

3. ثابت ها. اعداد یا مقادیر متنی که مستقیماً در یک فرمول وارد می شوند، مانند 2.

4. اپراتورها. عملگر ^ ("cap") برای افزایش یک عدد به توان و عملگر * ("ستاره") برای ضرب استفاده می شود.

استفاده از ثابت ها در فرمول ها

ثابت یک مقدار آماده (محاسبه نشده) است که همیشه بدون تغییر باقی می ماند. به عنوان مثال، تاریخ 10/09/2008، عدد 210 و متن "سود برای سه ماهه" ثابت هستند. بیان یا مقدار آن ثابت نیستند. اگر یک فرمول در یک سلول دارای ثابت باشد اما هیچ ارجاعی به سلول های دیگر نداشته باشد (مثلاً به نظر می رسد =30+70+110)، مقدار آن سلول تنها پس از تغییر فرمول تغییر می کند.

استفاده از عملگرها در فرمول ها

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

انواع اپراتور

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

عملگرهای حسابی

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

اپراتورهای مقایسه

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

عملگر متن الحاق

آمپرسند ( & ) برای الحاق (پیوستن) یک یا چند خط متن به یک خط استفاده می شود.

اپراتورهای مرجع

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

نحوه انجام اقدامات در فرمول ها در Excel Online

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

ترتیب محاسبه

فرمول ها مقادیر را به ترتیب خاصی محاسبه می کنند. یک فرمول همیشه با علامت تساوی شروع می شود ( = ). Excel Online علائمی را که از علامت تساوی پیروی می کنند در یک فرمول تفسیر می کند. پس از علامت مساوی، می توانید عناصر (عملوندها)، مانند ثابت ها یا مراجع سلولی را ارزیابی کنید. آنها توسط عملگرهای محاسباتی از هم جدا می شوند. Excel Online فرمول را از چپ به راست با توجه به ترتیب خاص هر اپراتور در فرمول ارزیابی می کند.

تقدم اپراتور

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

با استفاده از پرانتز

برای تغییر ترتیب ارزیابی یک فرمول، قسمتی از فرمول را که باید ابتدا اجرا شود داخل پرانتز قرار دهید. به عنوان مثال، فرمول زیر به عدد 11 منجر می شود زیرا اکسل آنلاین ضرب را قبل از جمع انجام می دهد. در این فرمول عدد 2 در 3 ضرب می شود و سپس عدد 5 به نتیجه اضافه می شود.

اگر نحو را با استفاده از پرانتز تغییر دهید، Excel Online 5 و 2 را اضافه می کند و سپس نتیجه را در 3 ضرب می کند. نتیجه این اقدامات عدد 21 خواهد بود.

در مثال زیر، پرانتزهای اطراف قسمت اول فرمول به Excel Online می‌گویند که B4+25 را محاسبه کرده و نتیجه را بر مجموع مقادیر سلول‌های D5، E5 و F5 تقسیم کند.

=(B4+25)/SUM(D5:F5)

استفاده از توابع و توابع تو در تو در فرمول ها

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

نحو تابع

مثال تابع زیر گرد، گرد کردن عدد در سلول A10، نحو تابع را نشان می دهد.

1. ساختار. ساختار تابع با علامت مساوی (=) شروع می شود، به دنبال آن نام تابع، یک پرانتز باز، آرگومان های تابع که با کاما از هم جدا شده اند، و یک پرانتز بسته می شود.

2. نام تابع. برای نمایش لیستی از توابع موجود، روی هر سلولی کلیک کنید و SHIFT+F3 را فشار دهید.

3. استدلال. انواع مختلفی از آرگومان ها وجود دارد: اعداد، متن، بولین ها (TRUE و FALSE)، آرایه ها، مقادیر خطا (مانند #N/A)، یا مراجع سلولی. آرگومان استفاده شده باید یک مقدار معتبر برای آرگومان داده شده برگرداند. ثابت ها، فرمول ها و توابع دیگر نیز به عنوان آرگومان استفاده می شوند.

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

وارد کردن توابع

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

برای سهولت در ایجاد و ویرایش فرمول ها و به حداقل رساندن اشتباهات تایپی و نحوی، از Formula Completion استفاده کنید. پس از تایپ علامت مساوی (=) و حروف اولیه یا یک نشانگر نمایش در Excel Online، یک لیست کشویی پویا از توابع، آرگومان‌ها و نام‌های موجود که با آن حروف یا ماشه مطابقت دارند در زیر سلول ظاهر می‌شود. پس از آن، عنصر از لیست کشویی را می توان در فرمول درج کرد.

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

1. توابع AVERAGE و SUM در تابع IF تودرتو هستند.

انواع ارزش محاسبه شده معتبرتابع تو در تو که به عنوان آرگومان استفاده می شود باید نوع داده مربوطه خود را برگرداند. برای مثال، اگر آرگومان باید یک مقدار بولی باشد، یعنی TRUE یا FALSE باشد، تابع تودرتو نیز باید یک مقدار بولی (TRUE یا FALSE) برگرداند. در غیر این صورت، TE102825393 خطای "#VALUE!" را می دهد.

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

استفاده از مراجع در فرمول ها

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

سبک پیوند A1

سبک پیوند پیش فرضبه طور پیش فرض، اکسل آنلاین از سبک پیوند A1 استفاده می کند که به ستون ها با حروف (از A تا XFD، تا 16384 ستون در کل) و ردیف ها بر اساس اعداد (از 1 تا 1048576) اشاره می کند. به این حروف و اعداد سرفصل سطر و ستون می گویند. برای اشاره به یک سلول، حرف ستون و سپس شماره ردیف را وارد کنید. به عنوان مثال، مرجع B2 به سلول واقع در تقاطع ستون B و ردیف 2 اشاره می کند.

تفاوت بین پیوندهای مطلق، نسبی و مختلط

پیوندهای نسبی. پیوند نسبی در یک فرمول، مانند A1، بر اساس موقعیت نسبی سلول حاوی فرمول و سلولی است که پیوند به آن اشاره می کند. هنگامی که موقعیت سلول حاوی فرمول را تغییر می دهید، مرجع نیز تغییر می کند. هنگام کپی یا پر کردن فرمول در امتداد ردیف ها و ستون ها، پیوند به طور خودکار تنظیم می شود. به طور پیش فرض، فرمول های جدید از مراجع نسبی استفاده می کنند. به عنوان مثال، هنگامی که یک مرجع نسبی را از سلول B2 به سلول B3 کپی یا پر می کنید، به طور خودکار از =A1 به =A2 تغییر می کند.

لینک های مطلق. یک مرجع سلول مطلق در فرمولی، مانند $A$1، همیشه به یک سلول در یک مکان خاص اشاره دارد. تغییر موقعیت یک سلول حاوی فرمول، مرجع مطلق را تغییر نمی دهد. هنگام کپی کردن یا پر کردن فرمول توسط سطرها و ستون ها، مرجع مطلق تنظیم نمی شود. به طور پیش فرض در فرمول های جدید از مراجع نسبی استفاده می شود و برای استفاده از مراجع مطلق باید گزینه مربوطه را فعال کنید. به عنوان مثال، وقتی یک مرجع مطلق را از سلول B2 به سلول B3 کپی یا پر می کنید، در هر دو سلول یکسان می ماند: =$A$1.

پیوندهای مختلطیک مرجع مختلط شامل یک ستون مطلق و یک ردیف نسبی و یک ردیف مطلق و یک ستون نسبی است. مرجع ستون مطلق به شکل $A 1، $B 1 و غیره است. مرجع ردیف مطلق به شکل $1، B $1 و غیره تغییر خواهد کرد. اگر فرمولی را در ردیف یا ستون کپی یا پر کنید، مرجع نسبی به طور خودکار تنظیم می شود و مرجع مطلق تغییر نمی کند. به عنوان مثال، هنگام کپی و پر کردن یک مرجع ترکیبی از سلول A2 به سلول B3، از = A $1 به = B $1 تنظیم می شود.

سبک پیوند سه بعدی

روشی مناسب برای پیوند دادن به چندین برگهپیوندهای سه بعدی برای تجزیه و تحلیل داده‌های یک سلول یا محدوده سلول‌ها در چندین کاربرگ در یک کتاب کار استفاده می‌شوند. یک مرجع سه بعدی حاوی یک مرجع سلول یا محدوده است که قبل از آن نام برگه ها وجود دارد. اکسل آنلاین از همه کاربرگ های بین نام شروع و پایان در پیوند استفاده می کند. به عنوان مثال، فرمول =SUM(Sheet2:Sheet13!B5) تمام مقادیر موجود در سلول B5 را در همه برگه‌های محدوده از صفحه 2 تا صفحه 13، شامل می‌شود.

با ارجاعات سه بعدی، می‌توانید به سلول‌ها در برگه‌های دیگر پیوند دهید، نام‌ها را تعریف کنید و فرمول‌هایی را با استفاده از توابع زیر ایجاد کنید: SUM، AVERAGE، AVERAGE، COUNT، COUNT، MAX، MAX، MIN، MIN، PRODUCT، STDEV.H، STDEV. V، STDEV، STDEV، VAR، VAR.B، VAR، و VAR.

وقتی برگه‌ها را جابه‌جا می‌کنید، کپی می‌کنید، جای‌گذاری می‌کنید یا حذف می‌کنید چه اتفاقی می‌افتد. مثال‌های زیر توضیح می‌دهند که چگونه پیوندهای سه‌بعدی هنگام جابجایی، کپی، چسباندن و حذف برگه‌هایی که به آنها اشاره می‌کنند تغییر می‌کنند. مثال‌ها از فرمول =SUM(Sheet2:Sheet6!A2:A5) برای جمع‌آوری مقادیر سلول‌های A2 تا A5 در برگه‌های دو تا شش استفاده می‌کنند.

چسباندن یا کپی کردناگر برگه‌ها را بین برگه‌های 2 و 6 قرار دهید، Excel Online محتویات سلول‌های A2 تا A5 در برگه‌های اضافه شده را به جمع اضافه می‌کند.

حذفاگر برگه‌ها را بین برگه‌های 2 و 6 حذف کنید، Excel Online از مقادیر آنها در محاسبات استفاده نمی‌کند.

در حال حرکتاگر برگه‌های بین Sheet 2 و Sheet 6 به گونه‌ای جابه‌جا شوند که در مقابل Sheet 2 یا پشت Sheet 6 قرار گیرند، Excel Online محتویات سلول‌ها را از برگه‌های منتقل‌شده از مجموع کم می‌کند.

ورق پایانی متحرکاگر برگه 2 یا 6 را به مکان دیگری در کتاب کار منتقل کنید، Excel Online مقدار را طوری تنظیم می کند که تغییر در محدوده برگه ها را منعکس کند.

حذف یک برگهاگر برگه 2 یا 6 را حذف کنید، Excel Online مقدار را طوری تنظیم می کند که تغییر در محدوده برگه ها را منعکس کند.

سبک پیوند R1C1

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

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

استفاده از نام ها در فرمول ها

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

انواع نام ها

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

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

نام جدولنام یک جدول Excel Online که مجموعه ای از داده های موضوعی خاص است که در رکوردها (ردیف ها) و فیلدها (ستون ها) ذخیره می شود. Excel Online هر بار که یک جدول Excel Online را وارد می‌کنید یک نام جدول Excel Online پیش‌فرض برای «Table1»، «Table2» و غیره ایجاد می‌کند، اما می‌توانید این نام‌ها را تغییر دهید تا معنی‌دارتر شوند.

ایجاد و وارد کردن نام

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

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

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

کلیدهایک نام، به عنوان مثال، به عنوان آرگومان یک فرمول وارد کنید.

تکمیل فرمول. از لیست کشویی Formula Auto Complete استفاده کنید که به طور خودکار نام های معتبر را نمایش می دهد.

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

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

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

با وارد کردن فرمول "=(SUM(B2:D2*B3:D3))" به عنوان یک فرمول آرایه ابتدا مقدار "Stock" و "Price" برای هر صرافی و سپس مجموع همه نتایج محاسبه می شود.

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

به عنوان مثال، با توجه به یک سری از سه ارزش فروش (در ستون B) به مدت سه ماه (در ستون A)، تابع روندادامه سری خطی حجم فروش را تعیین می کند. برای نمایش تمام نتایج یک فرمول، فرمول در سه خانه در ستون C (C1:C3) وارد می شود.

فرمول "=TREND(B1:B3;A1:A3)" که به عنوان یک فرمول آرایه وارد شده است، سه مقدار (22196، 17079 و 11962) محاسبه شده از سه حجم فروش در سه ماه را برمی گرداند.

استفاده از ثابت های آرایه

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

ثابت های آرایه می توانند شامل اعداد، متن، مقادیر بولی مانند TRUE یا FALSE یا مقادیر خطا مانند "#N/A" باشند. یک آرایه ثابت می تواند مقادیر مختلفی از انواع مختلف داشته باشد، به عنوان مثال (1،3،4؛ TRUE، FALSE، TRUE). اعداد در ثابت های آرایه می توانند اعداد صحیح، اعشاری یا نمایی باشند. متن باید در دو گیومه مانند "سه شنبه" قرار گیرد.

هنگام قالب‌بندی ثابت‌های آرایه، مطمئن شوید که الزامات زیر برآورده شده‌اند.

ثابت ها در بریس های مجعد محصور شده اند ( { } ).

ستون ها با کاما از هم جدا می شوند ( , ). به عنوان مثال، برای نشان دادن مقادیر 10، 20، 30، و 40، (10،20،30،40) را وارد کنید. این ثابت آرایه یک ماتریس 1 در 4 است و مربوط به ارجاع به یک سطر و چهار ستون است.

مقادیر سلول ها از ردیف های مختلف با نقطه ویرگول از هم جدا می شوند ( ; ). به عنوان مثال، برای نمایش مقادیر 10، 20، 30، 40، و 50، 60، 70، 80 در سلول های پشته ای، می توانید یک آرایه ثابت 2 در 4 ایجاد کنید: (10،20،30،40;50، 60،70،80).

فرمول ها

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

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

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

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

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

برنج. 5.3. جعبه گفتگو بزرگ و جمع شد

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

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

به عنوان مثال، در سلول B2 پیوندی به سلول A3 وجود دارد. در نماد نسبی می توان گفت که پیوند به سلولی اشاره می کند که یک ستون به سمت چپ و یک ردیف زیر سلول داده شده قرار دارد. اگر فرمول در سلول دیگری کپی شود، این مرجع نسبی حفظ خواهد شد. به عنوان مثال، هنگام کپی کردن یک فرمول در سلول EA27، مرجع همچنان به سلول سمت چپ و پایین اشاره می کند، در این مورد سلول DZ28.

در آدرس دهی مطلقآدرس‌های پیوند هنگام کپی تغییر نمی‌کنند، بنابراین سلولی که پیوند به آن اشاره می‌کند به عنوان یک سلول غیرجدولی در نظر گرفته می‌شود. برای تغییر روش آدرس دهی هنگام ویرایش فرمول، مرجع سلول را انتخاب کرده و کلید F4 را فشار دهید. عناصر شماره سلولی که از آدرس دهی مطلق استفاده می کنند، قبل از کاراکتر $ قرار می گیرند. به عنوان مثال، اگر کلید F4 را پشت سر هم فشار دهید، شماره سلول A1 به صورت A1، $A$ 1، A$ 1 و $A1 نوشته می شود. در دو مورد آخر، یکی از اجزای عدد سلول مطلق و دیگری نسبی تلقی می شود.

کپی کردن محتویات سلول

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

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

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

با استفاده از کلیپ بوردانتقال اطلاعات از طریق کلیپ بورد در برنامه وجود دارد برتری داشتن ویژگی های خاص مرتبط با پیچیدگی کنترل این عملیات. ابتدا باید محدوده کپی شده (برش) را انتخاب کنید و دستوری برای قرار دادن آن در کلیپ بورد بدهید: Edit Copy یا Edit Cut. چسباندن داده ها در یک کاربرگ فقط بلافاصله پس از قرار دادن آن در کلیپ بورد امکان پذیر است. تلاشانجام هر عملیات دیگری فرآیند کپی یا انتقال را که از قبل شروع شده است لغو می کند. با این حال، هیچ داده ای از دست نمی رود زیرا داده های "برش" تنها در زمان درج از محل اصلی خود حذف می شوند.

نقطه درج با مشخص کردن سلول مربوط به گوشه سمت چپ بالای محدوده قرار داده شده در کلیپ بورد یا با انتخاب محدوده ای که دقیقاً به اندازه اندازه کپی شده (انتقال داده شده) است تعیین می شود. چسباندن با دستور Edit Paste انجام می شود. برای کنترل روش paste می توانید از دستور Edit Paste Special استفاده کنید. در این مورد، قوانین چسباندن داده ها از کلیپ بورد در کادر محاوره ای باز شده تنظیم می شود.

اتوماسیون ورودی

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

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

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

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

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

به عنوان مثال، اجازه دهید سلول A1 حاوی عدد 1 باشد. نشانگر ماوس را روی دسته پر کنید، دکمه سمت راست ماوس را فشار دهید و دسته پر را بکشید تا کادر سلول های A1، B1 و C1 را بپوشاند و دکمه ماوس را رها کنید. اگر اکنون در منوی باز شده آیتم Copycells را انتخاب کنید، تمام سلول ها دارای عدد 1 خواهند بود. اگر آیتم Fill را انتخاب کنید، سلول ها شامل اعداد 1، 2 و 3 می شوند.

برای فرمول بندی دقیق شرایط پر کردن سلول ها، باید دستور Edit Fill Progression را بدهید. در کادر گفتگوی Progression که باز می شود، نوع پیشرفت، اندازه مرحله و مقدار حد را انتخاب کنید. پس از کلیک بر روی دکمه OK، برنامه برتری داشتن طبق 1 قانون مجموعه به طور خودکار سلول ها را پر می کند.

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

به عنوان مثال، فرض کنید که مقادیر ستون سوم کاربرگ (ستون C) به عنوان مجموع مقادیر سلول های مربوطه ستون A و B محاسبه می شود. فرمول =A1+B1 را در سلول C1 وارد کنید. . حال بیایید این فرمول را با استفاده از روش تکمیل خودکار در تمام سلول های ستون سوم جدول کپی کنیم. با آدرس دهی نسبی، فرمول برای تمام سلول های آن ستون صحیح خواهد بود.

AT
جدول 5.1 قوانین به روز رسانی پیوندها را هنگام تکمیل خودکار در امتداد یک ردیف یا در طول یک ستون نشان می دهد.

جدول 5.1. قوانین به روز رسانی پیوندها در تکمیل خودکار

استفاده از توابع استاندارد

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

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

با استفاده از Function Wizardبا انتخاب بیشتر توابع، Function Wizard راه اندازی می شود تا به شما در انتخاب عملکرد مورد نظر کمک کند. در لیست کشویی Category، دسته ای را که تابع به آن تعلق دارد، انتخاب کنید (اگر تعیین دسته مشکل است، از آیتم فهرست کامل حروف الفبا استفاده کنید)، و در انتخاب یک لیست تابع - یک تابع خاص از این دسته. پس از کلیک بر روی دکمه OK، نام تابع به همراه پرانتزهایی که لیست پارامترها را محدود می کند، در نوار فرمول وارد می شود. مکان نما متن بین این براکت ها قرار می گیرد. همچنین می توانید با کلیک بر روی دکمه Insert Function در نوار فرمول، Function Wizard را فراخوانی کنید.

ولی
آرگومان های تابعبه محض انتخاب نام تابع، کادر محاوره ای Function Arguments روی صفحه ظاهر می شود (در نسخه های قبلی بیش ازلاین هستپنجره به عنوان مشاهده شد پالت فرمول).این پنجره، به ویژه، حاوی مقداری است که اگر فوراً وارد فرمول شوید، به دست می آید (شکل 5.4).

برنج. 5.4. جعبه گفتگوی نوار فرمول و آرگومان های تابع

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