نحوه ایجاد یک فیلد محاسبه شده در اکسل ستون های محاسبه شده در Transact-SQL چگونه یک ستون محاسبه شده ایجاد کنیم

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

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

وظیفه 1.جدول PRODUCT دارای فیلدهای PRICE و RATE_VAT است.

1. یک پرس و جو انتخابی در حالت طراحی برای جدول PRODUCT ایجاد کنید. فیلدهای NAME_TOV، PRICE، RATE_VAT و Price with VAT را در فرم درخواست بکشید (شکل 4.6).
2. برای محاسبه قیمت با احتساب مالیات بر ارزش افزوده، با نوشتن عبارت [PRICE]+[PRICE]*[RATE_VAT] در سلول خالی ردیف فیلد، یک فیلد محاسبه شده ایجاد کنید.
3. برای انتخاب رکوردهایی با مقدار بیشتر از 5000 در فیلد محاسبه شده، > 5000 را در خط Criteria وارد کنید.
4. پس از وارد کردن یک عبارت، سیستم به طور پیش‌فرض نام فیلد محاسبه‌شده Expression 1 را تولید می‌کند که به عنوان سرفصل ستون در جدول با نتایج پرس و جو تبدیل می‌شود. این نام قبل از عبارت [PRICE]+[PRICE]*[VAT_RATE] درج خواهد شد. برای هر فیلد محاسبه شده جدید در پرس و جو، عدد عبارت یک عدد افزایش می یابد. نام فیلد محاسبه شده با علامت دو نقطه از عبارت جدا می شود. برای تغییر نام، نشانگر ماوس را در قسمت محاسبه شده فرم درخواست قرار داده و دکمه سمت راست ماوس را کلیک کنید. از منوی حساس به متن، را انتخاب کنید خواصفیلد (Properties) و در خط Caption نام فیلد جدیدی را وارد کنید - قیمت با احتساب مالیات بر ارزش افزوده 1. حال در جدول با نتایج پرس و جو، این نام در سربرگ ستون محاسبه شده نمایش داده می شود. نام فیلد را نیز می توان مستقیماً در فرم درخواست اصلاح کرد.
5. برای نمایش نتیجه پرس و جو بر روی دکمه کلیک کنید اجرا کردن(دویدن) در گروه نتایج(نتایج). فیلد محاسبه شده جدول و پرس و جو دارای مقادیر یکسانی هستند.
6. قیمت محصول را در یکی از سوابق درخواست تغییر دهید. مقادیر در هر دو فیلد محاسبه شده بلافاصله مجدداً محاسبه می شوند.
7. برای تشکیل یک عبارت پیچیده در یک فیلد محاسبه شده یا شرایط انتخاب، توصیه می شود از سازنده عبارت استفاده کنید. سازنده به شما امکان می دهد نام فیلدهای مورد نیاز در عبارت را از جداول، پرس و جوها، علائم عملیات و توابع انتخاب کنید. عبارت را در قسمت محاسبه شده حذف کنید و از سازنده برای تولید آن استفاده کنید.
8. با کلیک بر روی دکمه Expression Builder تماس بگیرید سازنده(سازنده) در گروه تنظیم پرس و جو(راه اندازی پرسش) طراحی نوار، یا با انتخاب ساختن(ساخت) در منوی حساس به زمینه. نشانگر ماوس باید قبلاً در سلول ورودی عبارت قرار گرفته باشد.
9. در سمت چپ پنجره سازنده بیان(Expression Builder) (شکل 4.7) جدول PRODUCT که پرس و جو بر اساس آن است را انتخاب کنید. لیستی از فیلدهای آن در سمت راست نمایش داده می شود. به صورت متوالی فیلدها و عملگرهای مورد نیاز را با دوبار کلیک کردن در عبارت انتخاب کنید. عبارت در بالای پنجره تشکیل می شود. لطفاً توجه داشته باشید که سازنده نام جدولی را که به آن تعلق دارد قبل از نام فیلد مشخص کرده و آن را با علامت تعجب از نام فیلد جدا کرده است.
10. فرآیند ساخت یک عبارت در یک فیلد محاسبه شده را با کلیک بر روی OK کامل کنید.
11. درخواست را با نام - قیمت با مالیات بر ارزش افزوده ذخیره کنید و آن را ببندید.
12. فایل ذخیره شده را با انتخاب آن در ناحیه پیمایش و انتخاب Open از منوی زمینه اجرا کنید.

وظیفه 2.می توانید از توابع داخلی در فیلدهای محاسبه شده و شرایط فیلتر استفاده کنید. Access دارای بیش از 150 عملکرد تعریف شده است.
اجازه دهید لازم باشد تمام فاکتورهایی را که برای آنها ارسال شده در یک ماه مشخص شده انتخاب کنید. در INVOICE، تاریخ ارسال در قسمت DATE_OTG با نوع داده تاریخ/زمان ذخیره می‌شود.

1. برای جدول INVOICE یک عبارت انتخابی در حالت طراحی ایجاد کنید. فیلدهای NOM_NAKL و CODE_SK را به فرم بکشید (شکل 4.8).
2. یک فیلد محاسبه شده در یک سلول ردیف خالی ایجاد کنید رشته(فیلد) با نوشتن یکی از عبارات در آنجا: Format([INVOICE]![DISPOSITION_DATE];"mmmm") - این تابع نام کامل ماه را برمی گرداند.
   یا قالب ([INBOOK]![DATE_DATE];"mm") - این تابع شماره ماه را برمی گرداند.
3. برای انتخاب فاکتورهای صادر شده در یک ماه معین، در قسمت محاسبه شده در خط انتخاب شرط (معیارها) نام ماه، به عنوان مثال مارس (شکل 4.8)، یا شماره ماه، برای مثال 3 را مطابق با پارامتر در تابع Format.
4. با کلیک بر روی دکمه درخواست خود را تکمیل کنید اجرا کردن(دویدن) در گروه نتایج(نتایج) در زبانه روبان کار با درخواست ها | سازنده(ابزار پرس و جو | طراحی).
5. تابع ماه (INVOICE!DATE_OTG) را در قسمت محاسبه شده بنویسید و مطمئن شوید که این تابع شماره ماه استخراج شده از تاریخ را برمی گرداند.
6. برای انتخاب تمام ردیف های مربوط به سه ماهه دوم، در ردیف Criteria عملگر Between 4 And 6 را وارد کنید که تعیین می کند آیا مقدار عبارت در بازه مشخص شده قرار می گیرد یا خیر.
7. عبارت MonthName(Month(INVOICE!DATE_OTG)) را در قسمت محاسبه شده بنویسید و مطمئن شوید که تابع MonthName عدد ماه را به نام کامل خود تبدیل می کند.

برای تقویت این موضوع، فیلم آموزشی را تماشا کنید.

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

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

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

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

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

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

روش 1: یک فیلد محاسبه شده را به منبع داده اضافه کنید

همانطور که در شکل نشان داده شده است، می توانید یک فیلد محاسبه شده را به منبع داده خود اضافه کنید. 2، به PivotTable اجازه می دهد تا از این فیلد به عنوان یک فیلد داده استاندارد استفاده کند. اگر چیدمان اطلاعات در برگه منبع داده اجازه می دهد تا این کار انجام شود، بهترین راه حل چیست. به هر حال، افزودن یک ستون جدید نه تنها نیازمند به روز رسانی جدول محوری، بلکه تغییر دامنه ای است که محور بر اساس آن است. برای این کار بر روی summary کلیک کنید و از منوی مورد نظر بروید تجزیه و تحلیل → منبع داده → منبع داده.

من سعی می کنم داده های اصلی را به یک جدول تبدیل کنم (که در شکل 2 و در فایل اکسل ضمیمه انجام شده است). در این حالت، اولاً سرفصل‌ها برجسته می‌شوند، ثانیاً دکمه‌هایی با فیلتر ظاهر می‌شوند که اغلب برای کاوش داده‌ها مفید است، ثالثاً ردیف‌ها یکی یکی رنگ‌آمیزی می‌شوند و در نهایت مهم‌ترین چیز هنگام افزودن ردیف و ستون ها لازم نیست ناحیه داده را برای جدول محوری تغییر دهید. ناحیه مشخص شده به این صورت یک محدوده مستطیلی نیست، بلکه نام جدول است. در مورد ما - منبع(شکل 3).

برنج. 3. تشکیل خلاصه بر اساس ابزار اکسل ویژه - جدول

اما این روش معایبی نیز دارد: اولاً، اضافه کردن یک ستون به داده های منبع همیشه راحت نیست و ثانیاً، در صورت تغییر ساختار داده های منبع (به عنوان مثال، شما در حال صادر کردن داده ها از 1C هستید، و ثانیاً، امکانات محدود می شود). صادرات جدید یک ستون دیگر دارد.

روش 2: از فرمولی خارج از PivotTable برای ایجاد یک فیلد محاسبه شده استفاده کنید

می توانید یک فیلد محاسبه شده در کنار جدول محوری اضافه کنید. در شکل 4 هر سلول در یک ستون میانگین درآمد در ساعتبا فرمولی ارائه شده است که به جدول محوری ارجاع می دهد. به هر حال، اگر زمانی که می‌خواهید به سلول جدول محوری مراجعه کنید، تابع GET.PIOTTABLE.DATA در فرمول شما ظاهر می‌شود، نمی‌توانید فرمول را بکشید. برای غلبه بر این مشکل، یادداشت را بخوانید.

برنج. 4. وقتی پس از ایجاد یک PivotTable فرمولی را وارد می کنید، اساساً یک فیلد محاسبه شده اضافه می کنید که با به روز رسانی PivotTable تغییر می کند.

اگرچه این روش یک فیلد محاسبه‌شده را اضافه می‌کند که وقتی PivotTable به‌روزرسانی می‌شود، هر تغییری در ساختار PivotTable ممکن است فرمول را بی‌فایده کند. به عنوان مثال، در نتیجه کشیدن فیلد بازار فروش به قسمت FILTERS لیست فیلدها در جدول محوری، ساختار گزارش تغییر می کند، در نتیجه یک پیام خطا در فیلد محاسبه شده ظاهر می شود (شکل 5). اگر فیلد دیگری را به قسمت COLUMNS جدول محوری، ستون اضافه کنید میانگین درآمد در ساعتبازنویسی خواهد شد.

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

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

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

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

برنج. 6. فیلد محاسبه‌شده قبلاً ایجاد شده «معتبر» باقی می‌ماند، حتی اگر ساختار جدول محوری تغییر کند، و در نتیجه میانگین درآمد در ساعت برای هر سرویس و هر بازار فروش نمایش داده می‌شود.

یک فیلد محاسبه شده ایجاد کنید

قبل از اینکه بتوانید یک فیلد محاسبه شده ایجاد کنید، باید یک PivotTable را باز کنید یا ایجاد کنید. مثال ما از جدول محوری نشان داده شده در شکل استفاده می کند. 1، اما بدون ستون D. برای ایجاد یک فیلد محاسبه شده، کادر محاوره ای را فعال کنید درج فیلد محاسبه شده. برای این کار بر روی جدول محوری کلیک کنید تا گروه تب های متنی فعال شود کار با Pivot Tables. به برگه بروید تحلیل و بررسی، به گروه محاسبات، روی دکمه کلیک کنید فیلدها، عناصر و مجموعه هاو دستور فیلد محاسبه شده را از منو انتخاب کنید (شکل 7).

یک کادر محاوره ای روی صفحه ظاهر می شود درج فیلد محاسبه شده(شکل 8). دو فیلد متنی در بالای کادر محاوره ای وجود دارد: نامو فرمول. در این فیلدها باید نام فیلد محاسبه شده را مشخص کرده و با تعیین فیلدهای داده مورد نیاز و عملگرهای ریاضی فرمولی ایجاد کنید. فیلد محاسبه شده یک نام توصیفی داده می شود میانگین درآمد در ساعت. این نام باید برای توصیف دقیق نوع عملیات ریاضی در حال انجام انتخاب شود. فیلد متن پیش فرض فرمولکادر محاوره ای درج فیلد محاسبه شدهحاوی عبارت = 0 است. قبل از وارد کردن فرمول، باید صفر را حذف کنید. یکی از فیلدهای منطقه را انتخاب کنید زمینه های; در مورد ما - حجم فروشو کلیک کنید یک فیلد اضافه کنید. نام فیلد در خط ظاهر می شود فرمول. علامت تقسیم / را وارد کنید و سپس فیلد دوم را انتخاب کنید – مدت فروش (به ساعت). کلیک اضافه کردن، و سپس خوببرای فعال کردن فیلد محاسبه شده جدید.

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

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

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

ایجاد آیتم های محاسبه شده

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

در جدول خلاصه نشان داده شده در شکل. 9، حجم کل فروش برای دوره های گزارش خاص مشخص شده است (فیلد دوره گزارش). تصور کنید که می خواهید میانگین حجم فروش شش دوره گذشته را با میانگین حجم فروش هفت دوره قبل مقایسه کنید. برای دقیق تر، ما باید میانگین دوره های P01-P07 را تعیین کنیم و آن را با میانگین دوره های P08-P13 مقایسه کنیم.

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

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

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

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

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

مزایا و معایب محاسبات PivotTable

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

اولویت عملیاتمانند صفحات گسترده، هر عملگر را می توان در فرمول های اقلام و فیلدهای محاسبه شده استفاده کرد: +، –، *، /، ٪، ^، و غیره. علاوه بر این، مانند صفحات گسترده، محاسبات در جداول محوری بر اساس اولویت عملگر انجام می شود. هنگامی که محاسبه ای را انجام می دهید که چندین عملگر را ترکیب می کند، مانند (2+3) *4/50٪، اکسل عبارت را ارزیابی می کند و محاسبه را به ترتیب خاصی انجام می دهد. دانستن این دستور شما را از بسیاری از اشتباهات نجات می دهد.

بنابراین، ترتیب عملیات در اکسل به شرح زیر است:

• پردازش عبارات داخل پرانتز؛
• پردازش محدوده (:);
• تقاطع پردازش (مناطق)؛
• اتصالات دسته (;);
• انجام عملیات نفی
• تبدیل مقادیر درصد (به عنوان مثال، 50٪ به 0.50 تبدیل می شود).
• توان (^)؛
• ضرب (*) و تقسیم (/)؛ این عملیات دارای اولویت یکسان هستند.
• جمع (+) و تفریق (–)؛ این عملیات دارای اولویت یکسان هستند.
• ترکیب داده های متنی (&) این عملیات را الحاق نیز می نامند.
• انجام عملیات مقایسه (=<>, <=, >=).

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

بیایید به یک مثال ساده نگاه کنیم. همانطور که می دانید عبارت (2+3)*4 نتیجه 20 را برمی گرداند. اگر پرانتز را بردارید و عبارت 2+3*4 را بگذارید، اکسل محاسبه می کند: 3*4 = 12 + 2 = 14.

بیایید به مثال دیگری نگاه کنیم. اگر عبارت 10^2 را به عنوان فرمول وارد کنید، i.e. اگر مربع عدد 10 را مشخص کنید، برنامه مقدار 100 را به عنوان پاسخ برمی گرداند. با این حال، اکسل دوباره مقدار 100 را برمی گرداند. دلیل آن این است که برنامه عملیات نفی را قبل از قدرت انجام می دهد، i.e. مقدار 10 ابتدا به 10- تبدیل می شود و نتیجه -10*-10 در واقع 100 است. استفاده از پرانتز در فرمول -(10^2) تضمین می کند که برنامه ابتدا قدرت و سپس نفی را انجام می دهد و مقدار مورد انتظار را برمی گرداند. نتیجه - 100.

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

توابع کاربرگ.شما می توانید از هر تابع کاربرگ که از ارجاعات سلولی یا اشیاء نامگذاری شده به عنوان آرگومان استفاده نمی کند استفاده کنید. در واقع، شما می توانید از هر تابع کاربرگ که لزوماً به ارجاعات سلولی یا اشیاء نامگذاری شده نیاز ندارد، استفاده کنید. توابعی مانند COUNT، AVERAGE، IF، AND، NO و OR در این دسته قرار می گیرند.

ثابت هامی توانید از هر ثابتی در محاسبات انجام شده در جداول محوری استفاده کنید. ثابت ها مقادیر ثابتی هستند که در طول زمان تغییر نمی کنند. به عنوان مثال، در فرمول زیر، عدد 5 یک ثابت است: [واحد فروخته شده] *5. حتی اگر معنی واحدهای فروخته شدهممکن است بر اساس داده های موجود تغییر کند، عدد 5 همیشه یکسان خواهد بود.

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

قوانین ویژه برای فیلدهای محاسبه شدهمحاسبات در فیلدهای محاسبه شده همیشه بر روی مجموع انجام می شود، نه اقلام داده فردی. به زبان ساده، اکسل همیشه فیلدهای داده، مجموع فرعی و کل را قبل از ارزیابی یک فیلد محاسبه شده محاسبه می کند. این بدان معنی است که فیلد محاسبه شده شما همیشه به کل داده های منبع اعمال می شود. مثال نشان داده شده در شکل. 14 نشان می دهد که چگونه این می تواند بر تجزیه و تحلیل داده ها تأثیر بگذارد.

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

در هر سه ماهه، باید حجم فروش نهایی هر محصول را با ضرب تعداد واحدهای فروخته شده در قیمت واحد آن محصول بدست آورید. اگر ابتدا به داده های سه ماهه اول نگاه کنید، بلافاصله مشکل را شناسایی خواهید کرد. به جای محاسبه مجموع 220+150+220+594 که 1184 می شود، مجموع تعداد واحدها در مجموع قیمت کالاها ضرب می شود و در نتیجه مقدار نادرستی به وجود می آید. متأسفانه این مشکل قابل حل نیست، اما می توان آن را حل کرد. جمع‌های فرعی و خرده‌جمع‌ها را از PivotTable حذف کنید و سپس مجموع جدیدی را در پایین خارج از PivotTable محاسبه کنید.

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

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

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

ویرایش و حذف محاسبات در جداول محوری.اگر تنظیمات محاسبه را تغییر دادید یا دیگر نیازی به فیلد محاسبه شده یا عضو محاسبه شده ندارید، می توانید کادر محاوره ای مربوطه را برای ویرایش یا حذف محاسبه باز کنید. کادر محاوره ای را فعال کنید درج فیلد محاسبه شدهیا درج یک آیتم محاسبه شده(به توضیحات شکل 7 و 10 مراجعه کنید) و لیست کشویی را در آن انتخاب کنید نام(شکل 15).

ترتیب انجام محاسبات را در فیلدهای محاسبه شده تغییر دهید.اگر مقدار یک سلول در PivotTable به نتیجه محاسبه چندین مورد محاسبه شده بستگی دارد، می توانید ترتیب انجام عملیات در فیلدهای محاسبه شده را تغییر دهید. به عبارت دیگر، می توانید ترتیب انجام محاسبات فردی را مشخص کنید. برای باز کردن کادر محاوره ای ، نشانگر را در هر نقطه از جدول محوری قرار دهید و روی نماد کلیک کنید فیلدها، عناصر و مجموعه ها. در منوی کشویی، دستور Calculation order را انتخاب کنید (شکل 16).

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

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

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

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

یادداشت بر اساس کتاب Jelen، Alexander نوشته شده است. . فصل 5.

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

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

در حالت دوم، فرم بر اساس جدول ساخته شده است. یک فیلد جدید به شکل ایجاد می شود که در آن عبارتی به عنوان منبع داده مشخص می شود، برای مثال = [Unit Price]*Quantity. هنگام ارجاع به فیلدها در عبارات، در صورتی که نام فیلد از بیش از یک کلمه تشکیل شده باشد، استفاده از کروشه الزامی است. وارد کردن یک عبارت به طور مستقیم در یک کنترل (فیلد) در صورتی مجاز است که کوتاه باشد و طول فیلد اجازه دهد. در غیر این صورت، عبارت وارد سلول ویژگی می شود داده هاپنجره خواص.برای انجام این کار می توانید از بیان ساز استفاده کنید. اگر عبارت در سلول ویژگی جا نمی شود، باید کلیدهای + را فشار داده و پنجره را باز کنید ناحیه ورودی. در صورت لزوم، باید فرمت فیلد را هنگام نمایش روی صفحه تنظیم کنید.

6. ایجاد فرم دکمه ای

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

در کادر محاوره ای که می پرسد آیا می خواهید یک فرم دکمه ایجاد کنید، کلیک کنید آره.

پنجره ای روی صفحه ظاهر می شود مدیر فرم دکمه. این پنجره حاوی لیستی از صفحات فرم دکمه است. در ابتدای کار، پنجره یک صفحه است - فرم دکمه اصلی. در پنجره، روی دکمه کلیک کنید تغییر دادن.

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

روی دکمه کلیک کنید ايجاد كردن. یک پنجره ظاهر می شود تغییر عنصر فرم دکمه.

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

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

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

برنامه را تنظیم کنید فرم دکمه اصلیهنگام باز کردن پایگاه داده باز می شود. برای این کار از دستور منو استفاده کنید ابزارها/گزینه های راه اندازی.

مثال:

برنج. 28. فرم دکمه سطح بالا

آزمایشگاه 3 ایجاد پرس و جو

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

ورزش:

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

توالی کار

1.برای انتخاب یک جدول با استفاده از Query Builder یک پرس و جو ایجاد کنید

که در پنجره پایگاه دادهروی نشانک کلیک کنید درخواست ها.

روی دکمه کلیک کنید ايجاد كردن.

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

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

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

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

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

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

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

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

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

نتایج پرس و جو را با کلیک بر روی دکمه مشاهده کنید راه اندازی(دکمه با علامت تعجب) یا دکمه چشم اندازدر نوار ابزار

در شرایطی که نیاز به تغییر خاصیت query دارید، روی قسمت خالی بالای پنجره دوبار کلیک کنید سازنده درخواست -یک پنجره باز خواهد شد درخواست خواص.

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

مثال:

اطلاعات مربوط به محصولات را در مقادیر بیشتر از 100، اما کمتر از 300 و قیمت کمتر از 100 روبل انتخاب کنید.

برنج. 10. سازنده کوئری با شرط

برنج. 11. نتیجه پرس و جو با شرط

2. پیوستن داخلی در یک میدان

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

در پنجره اضافه کردن جدولچندین جدول مرتبط را که پرس و جو بر روی آنها ساخته می شود، به ترتیب انتخاب کنید. در بالا Query Builderجداول مرتبط نمایش داده می شود.

فرم درخواست را پر کنید.

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

مثال:

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

برنج. 12. طراح پرس و جو چند جدولی

برنج. 13. نتیجه یک پرس و جو چند جدولی

3. اتصالات خارجی

مراحل 1-3 بخش را دنبال کنید پیوستن داخلی در یک فیلد.

اگر جداول قبلاً در آن ثبت نشده باشد، رابطه ای بین جداول برقرار کنید طرح DB.

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

دستورات را از منو انتخاب کنید چشم اندازگزینه گزینه های ادغامیک کادر محاوره ای ظاهر می شود گزینه های ادغام.

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

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

روی دکمه کلیک کنید عملیات گروهی، امضا کردن  در نوار ابزار Query Builder. در فرم درخواست بالای خط مرتب سازیخط ظاهر خواهد شد عملیات گروهی.

در ردیف جایگزین کنید عملیات گروهینصب و راه اندازی گروه بندیبرای مورد نیاز عملیات گروهیبا انتخاب آن از جعبه ترکیبی: مجموع, میانگین, شمردن، مکه در, حداکثر, StDev, Var, اولین, آخر, اصطلاح(اصطلاح)،جایی که(وضعیت)،گروه توسط(گروه بندی).

اصطلاحبه شما امکان می دهد به جای نام فیلد در یک سلول یک عبارت وارد کنید , به عنوان مثال، برای محاسبه محدوده مقادیر برای یک ستون خاص ایکس:حداکثر([ ایکس]) – حداقل([ ایکس]).

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

3. درخواست را تکمیل کنید.

مثال:

تعداد کل محصولات هر سازنده را با ذکر نام آن انتخاب کنید.

برنج. 14. سازنده پرس و جو با گروه بندی رکوردها

برنج. 15. نتیجه یک پرس و جو با گروه بندی رکوردها

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

Query Builder .

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

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

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

درخواست را اجرا کنید.

مثال:

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

برنج. 16. سازنده پرس و جو با یک فیلد محاسبه شده

برنج. 17. نتیجه یک پرس و جو با یک فیلد محاسبه شده

6.ایجاد یک کوئری پارامتریک

یک پرسش انتخابی در حالت ایجاد کنید سازنده پرس و جو

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

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

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

5. درخواست اجرا را اجرا کنید.

مثال:

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

برنج. 18. سازنده کوئری پارامتریک

برنج. 19. درج تاریخ انقضای محصول

برنج. 20. نتیجه یک پرس و جو پارامتریک

7. ایجاد پرس و جوهای متقاطع

1. ایجاد با طراح درخواست جدیدو جداول لازم را شامل شود.

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

3. یک دستور را از منو انتخاب کنید پرس و جو / صلیب.سرفصل طراحتغییر خواهد کرد پرس و جو 1: در هر نمونه بر Query 1: Cross Query . فرم درخواست خط را نمایش می دهد جدول صلیب .

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

5. برای فیلدهایی که ستون های crosstab خواهند بود، بسته به پرس و جو، اقدامات زیر امکان پذیر است:

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

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

7. درخواست اجرا را اجرا کنید.

مثال:

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

برنج. 21. Cross Query Builder

برنج. 22. نتیجه پرس و جو متقابل

8.برای ایجاد جدول پرس و جو کنید

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

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

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

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

روی دکمه کلیک کنید خوب. پرس و جو انتخاب به یک کوئری ایجاد جدول تبدیل می شود.

پرس و جو را با دوبار کلیک کردن روی نام پرس و جو در پنجره DB اجرا کنید. قبل از اجرای پرس و جو، پیامی ظاهر می شود که به شما هشدار می دهد که تغییراتی در جدول جدید ایجاد می شود (حتی اگر هنوز ایجاد نشده باشد).

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

تب Tables را در پنجره DB باز کنید. یک جدول جدید باید در لیست ظاهر شود.

9.درخواست به روز رسانی سوابق

یک کوئری جدید ایجاد کنید و کپی ایجاد شده از جدول را در آن قرار دهید.

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

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

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

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

مثال:

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

برنج. 23. سازنده درخواست به روز رسانی

10.درخواست حذف سوابق

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

یک کوئری جدید ایجاد کنید و جدول مناسب را در آن قرار دهید.

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

برای نمایش رکوردهایی که قرار است حذف شوند، یک پرس و جو انتخاب کنید.

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

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

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

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

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

مثال:

برنج. 24. سازنده درخواست را حذف کنید

11. درخواست اضافه کردن سوابق

یک پرس و جو ضمیمه اغلب برای کپی داده ها از یک جدول به جدول دیگر استفاده می شود.

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

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

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

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

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

درخواست اجرا را اجرا کنید. ببینید جدول با اضافه شدن ردیف های جدید چگونه تغییر کرده است.

اگر در یک درخواست برای اضافهزمینه هایی مانند پیشخوان، پس می توانید به دو روش با چنین زمینه هایی کار کنید:

فیلدهای نوع را شامل نمی شود پیشخواندر زمینه هایی که باید اضافه شوند؛ سپس در جدولی که داده ها اضافه می شوند، شماره گذاری دنباله داده ها ادامه می یابد، i.e. اگر جدولی از کارکنان با اعداد از 1 تا 100 وجود دارد و به آن اضافه کنید ورودی های جدید، سپس شماره گذاری آنها از شماره 101 شروع می شود.

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

روش 2: روی مقادیر فیلد مورد نظر در ناحیه ΣValues ​​کلیک راست کرده و انتخاب کنید:

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

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

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

 محاسبات اضافی، سپس گزینه محاسبه مورد نظر را انتخاب کنید.

استفاده مجدد از یک فیلد اصلی در گزارش PivotTable

می توانید با استفاده از همان فیلد منبع، چندین محاسبات انجام دهید. برای انجام این کار، باید فیلد اصلی را از لیست فیلدهای جدول محوری به محدوده مقادیر Σ Values ​​[Σ اضافه کنید.

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

فیلدهای محاسبه شده را به یک PivotTable اضافه کنید

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

کاربرگ پایگاه داده 1 کاربرگ پایگاه داده 1 یک پوشه "پایگاه های داده" برای ذخیره تمام داده های دوره ایجاد کنید. یک پرس و جو ایجاد می کند که عناوین و توضیحات را نمایش می دهد. کادر محاوره ای Manage Database ظاهر می شود. با استفاده از این پنجره، کاربر پایگاه داده و تمامی عناصر آن را مدیریت می کند.

صفحه گسترده چیست؟ صفحه گسترده برنامه ای برای انجام محاسبات ریاضی است. با کمک آن می توانیم محاسبات خوانا را نیز در فرم انجام دهیم. برای استفاده از اقدامات خاص. پر زحمت ترین بخش، آماده سازی داده ها است. مسئله 1 گروه ریاضی 2 این مراحل را به ترتیب دنبال کنید. میز را اجرا کنید.

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

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

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

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

مایکروسافت اکسل 2010. سطح 2. ویژگی های پیشرفته

بنابراین، مزایای افزودن فیلدهای محاسبه شده خود به یک PivotTable واضح است:

 خطاهای احتمالی در فرمول ها و مراجع سلولی را حذف کنید.

 امکان افزودن و حذف داده ها از جدول محوری بدون تغییر محاسبات اصلی.

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

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

 امکان محاسبه مجدد خودکار داده ها هنگام تغییر یا به روز رسانی جدول محوری.

 هنگامی که تعاریف عناصر تغییر می کند، انعطاف پذیری را برای تغییر محاسبات فراهم می کند.

 توانایی مدیریت موثر محاسبات.

یک فیلد محاسبه شده ایجاد کنید:

1. یک سلول جدول محوری را انتخاب کنید.

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

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

2. در تب Options، در گروه Calculations، لیست Fields را گسترش دهید.

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

3. در پنجره درج فیلد محاسبه شده:

 در قسمت Name نام فیلد جدید را وارد کنید.

 در قسمت Formula یک فرمول ایجاد کنید، با علامت = شروع کنید و انتخاب کنید

فیلدهای موجود در لیست فیلدها (برای درج، می توانید روی یک فیلد دوبار کلیک کنید یا یک فیلد را انتخاب کنید، سپس روی افزودن فیلد کلیک کنید).

اگر بخواهیم مشخصات خود را چاپ کنیم، فیلد Salesperson را از قسمت Lines گرفته و به قسمت Column منتقل می کنیم. این باعث می شود که جدول در کل صفحه پخش شود. در این پنجره می توانیم فرمت اعداد نمایش داده شده در تمام سلول های فیلد را نیز مشخص کنیم. به سادگی از دکمه "Number Format" استفاده کنید و برای مثال ارز مناسب را انتخاب کنید.

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

 روی Add کلیک کنید.

تنظیم ظاهر یک فیلد محاسبه شده، مانند هر فیلد دیگری، در پنجره رخ می دهد

پارامترهای فیلد مقدار

ویرایش فیلد محاسبه شده

1. در زبانه Options، در گروه Calculations، لیست Fields را گسترش دهید.

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

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

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

وقتی باز می کنید منوی زمینه، قسمت Rows و گزینه Group پنجره متفاوتی نسبت به زمانی که تاریخ ها را گروه بندی می کردید نشان می دهد. اعداد در محدوده‌هایی دسته‌بندی می‌شوند که با شروع، پایان و حجم بازه تعریف می‌کنیم. در این حالت ما آن را روی 500 تنظیم می کنیم، بنابراین گروه اول شامل تمام تراکنش های بین 0 تا 500 زلوتی خواهد بود.

در واقع چنین امکانی وجود دارد و به یکی از سه راه تحقق می یابد.

می توانید دانش خود را در مورد اصول جدول محوری افزایش دهید.

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

راه دومایجاد محاسبات در کنار جدول محوری، باز هم بهترین روش نیست. اگر جدول محوری تغییر کند (و ارزش دارد از جداول محوری دقیقاً زمانی استفاده کنیم که انتظار تغییرات در جدول را داریم).

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

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

جدولی با یک فیلد محاسباتی جدید دریافت خواهید کرد. میز آماده است!

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

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

آخرین اخبار

• اکسل پیشرفته: چرا استفاده از تابع V را متوقف کردم...

  قبلاً در مورد این واقعیت نوشتم که تابع VLOOKUP (VLOOKUP) احتمالاً مفیدترین تابع بعد از عملیات ساده حسابی است ...

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

  اگر اغلب با فایل‌های بزرگی کار می‌کنید که محاسبات زیادی را انجام می‌دهند که به یکدیگر بستگی دارند، ...

• چگونه به سرعت نمودار آبشار بسازیم

  اکسل نمودارهای استاندارد Waterfall را ندارد. بنابراین برای ایجاد این نوع نمودارها معمولاً از gis ...

• پنجره کنترل برای ردیابی نتایج

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

• نمودار گلوله ای برای مقایسه شاخص های برنامه ریزی شده و واقعی ...

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

• Tatsuo Horiuchi یک هنرمند 73 ساله است که در اکسل طراحی می کند.

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

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

  آیا می دانید که برای بالا بردن یک عدد به توان در اکسل وجود دارد شخصیت خاص^ (در شش به انگلیسی...

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

برای افزودن فیلد محاسبه‌شده «مبلغ» به گزارش:

1. گزارش Items in Warehouse را در حالت Design باز کنید.
2. در جعبه ابزار، روی دکمه کلیک کنید سنگ نوشته(برچسب) و متن جدید را در سمت راست «واحد موجود در انبار» در قسمت سرصفحه قرار دهید. کلمه "Sum" را به عنوان متن برچسب وارد کنید.
3. برای ایجاد یک فیلد Sum محاسبه شده، روی دکمه کلیک کنید رشته(Text Box) را در جعبه ابزار قرار دهید و یک کادر متنی در سمت راست کادر متنی UnitsInStock در قسمت داده اضافه کنید. برچسب فیلد متن جدید را بردارید.
4. داخل قسمت متن کلیک کنید و عبارت زیر را به عنوان مقدار آن وارد کنید:

   = [InStock]* [قیمت] (= * ).

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

اظهار نظر

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

برنج. 6.27.

1. مراحل 3 و 4 را برای ایجاد یک فیلد متنی در بخش نظرات گروه Category تکرار کنید، اما عبارت زیر را به عنوان عبارت فرعی وارد کنید: =Sum([InStock] * [Price]) (=Sum( * )). روی دکمه کلیک کنید پررنگ(بولد) در نوار قالب برای تنظیم متن در قسمت جدید به صورت پررنگ. کادر محاوره ای را باز کنید خواص(Properties) و تب موجود در آن را باز کنید دیگر(دیگر)، در میدان نام(Name) یک نام برای کادر متنی Sum وارد کنید. سپس تب را گسترش دهید چیدمان(فرمت) و در جعبه ترکیبی خواص فرمت فیلد(فرمت) یک مقدار را انتخاب کنید پولی(واحد پول).
2. از آنجایی که عرض ناحیه داده گزارش ما افزایش یافته است، عرض حاشیه صفحه را کاهش دهید تا همه فیلدها در یک صفحه قرار گیرند. برای انجام این کار، دستور را انتخاب کنید فایل، تنظیم صفحه(File, Page Setup) و پهنای حاشیه چپ و راست پیش فرض را به 10 میلی متر تغییر دهید (به «مشاهده و چاپ گزارش‌ها» در این فصل مراجعه کنید.)
3. روی دکمه کلیک کنید چشم انداز(مشاهده) در نوار ابزار طراح گزارش(طراحی گزارش) برای ارزیابی نتایج کار شما. گزارش به شکلی خواهد بود که در شکل نشان داده شده است. 6.27. به داده های قبلی، ستون سمت راست با مقادیر بهای تمام شده هر محصول در مقدار موجود در انبار و جمع های فرعی برای بهای تمام شده کالا برای هر دسته اضافه شد.