در این مقاله نحوه تجزیه و تحلیل داده ها با استفاده از جدول محوری بحث خواهد شد. برای آموزش می توانید از جدول موجود در این لینک ( جدول ساده.xlsx ) استفاده کنید.
اولین چیزی که ممکن است نیاز به تجزیه و تحلیل با استفاده از جدول محوری داشته باشید این است که جمع های فرعی را جمع آوری کنید. در مثال ما، این ممکن است نیاز به محاسبه حجم فروش برای همه فروشگاه ها برای هر تاریخ باشد.
برای انجام این کار، روی سرصفحه هر ردیف از جدول محوری کلیک کنید (در مثال ما، این فیلدها هستند تاریخ، محل فروشو برند گوشی) و در برگه های باز شده کار با جداول محوریبه برگه بروید گزینه ها. باید دکمه روی آن را فشار دهید. گزینه های فیلددر یک گروه زمینه فعال.
در پنجره ای که باز می شود، اولین تب تب خواهد بود.
عدم وجود چنین نشانک به این معنی است که شما یک هدر ردیف را انتخاب نکرده اید، یعنی مکان نما روی سلولی با مقدار عددی قرار می گیرد.
نشانک جمع های فرعی و فیلترهامی توانید شرطی را برای نمایش جمع های فرعی انتخاب کنید. شرایط زیر ارائه می شود:
با تنظیم جمع‌بندی خودکار، جدول زیر را به دست می‌آوریم که شامل جمع‌های فرعی برای هر شرط است:
در صورت تنظیم جمع های فرعی با دستور گزینه های فیلد، نتایج قابل مشاهده نمی دهد، تنظیمات را برای نمایش جمع های فرعی با استفاده از دستور بررسی کنید جمع های فرعیگروه ها چیدمانزبانه ها سازنده.
فرض کنید می‌خواهیم جمع‌های فرعی را فقط برای تاریخ‌ها نمایش دهیم و جمع‌های فرعی را برای نقاط فروش پنهان کنیم. برای انجام این کار، روی هر قسمت از جدول با نام فروشگاه کلیک کنید و منوی زمینه را فراخوانی کنید. در آن، باید تیک شرط را بردارید جمع فرعی: نقطه فروش. همانطور که می بینیم، جمع های فرعی فقط برای تاریخ ها باقی می مانند:
اغلب لازم است که داده ها را در یک PivotTable مرتب کنیم تا آن ها را بهتر درک کنیم. برای انجام این کار، کافی است فیلدی را که می خواهید بر اساس آن مرتب کنید انتخاب کنید، به تب بروید عمومی، در یک گروه ویرایشروی دکمه کلیک کنید مرتب سازی و فیلتر کردنو شرایط مرتب سازی مورد نیاز خود را تنظیم کنید.
یک ویژگی بسیار مفید برای تجزیه و تحلیل اطلاعات در جدول محوری، امکان گروه بندی داده ها است. به عنوان مثال، ما باید فروش خود را بر اساس هفته های ماه گروه بندی کنیم. برای انجام این کار، تاریخ های درج شده در هفته اول (15.05-21.05) را انتخاب کنید:
لطفاً توجه داشته باشید که برای سهولت در انتخاب، با استفاده از دکمه + در سمت چپ سلول با نام فروشگاه، داده‌های فروشگاه‌های جداگانه را جمع کرده‌ایم.
بعد، باید دستور را اجرا کنید گروه بندی بر اساس انتخابگروه ها گروهزبانه ها گزینه ها. یک ستون جدید در جدول ظاهر می شود که در آن فیلد گروه 1فیلدهایی را که انتخاب کرده ایم ادغام می کند.
تنها چیزی که باقی می ماند این است که نام گروه را با ویرایش سلول تغییر دهید:
برای حذف گروه، فقط از دستور استفاده کنید لغو گروه کردناز همان گروه، پس از انتخاب فیلدی که باید گروه بندی شود. لطفاً توجه داشته باشید که نمی‌توانید فیلدی را که در شرایط ساخت جدول محوری گنجانده‌ایم - برای مثال، فیلد، لغو گروه‌بندی کنید. نقطه فروشیا تاریخ.
بیایید روش دیگری را برای نمایش داده ها در نظر بگیریم که به ما در تجزیه و تحلیل اطلاعات جدول کمک می کند. به عنوان مثال، ما باید حجم درآمد را نه به صورت پولی، بلکه به عنوان درصدی از کل درآمد کل دوره فروش بدانیم.
برای انجام این کار، هر سلولی را در ستون درآمد جدول محوری ما انتخاب کنید. پس از آن باید دستور را اجرا کنید گزینه های فیلددر یک گروه زمینه فعالزبانه ها گزینه ها.
در کادر محاوره ای که باز می شود، به تب بروید محاسبات اضافیو از منوی کشویی انتخاب کنید درصد کل بر اساس ستون. پس از کلیک بر روی دکمه OK، جدول ما به شکل زیر در می آید:
اگر داده های جدول به صورت درصد نمایش داده نمی شوند، تنظیمات قالب شماره سلول ها را بررسی کنید (این کار را می توان بلافاصله در کادر محاوره ای انجام داد. گزینه های فیلدبا کلیک بر روی دکمه قالب عددی، یا با فراخوانی پنجره مربوطه از منوی زمینه).
برای تجزیه و تحلیل لیستی از داده ها (جدول داده ها) در اکسل 2007 که دارای ردیف ها یا رکوردهای زیادی هستند، اغلب از ابزارهایی مانند PivotTables استفاده می شود. PivotTables مشاهده، دستکاری و خلاصه کردن داده ها در لیست های Excel 2007 را بسیار آسان تر می کند.
برای اینکه اکسل با داده های وارد شده به عنوان جدول داده کار کند و نه به عنوان یک مجموعه ساده از داده ها، باید به صورت جدول قالب بندی شود. برای این کار روی دکمه Table در تب Insert کلیک کنید. در کادر محاوره ای Create Table (شکل 1)، محدوده مورد نظری که جدول در آن قرار می گیرد را مشخص کنید و کادر Table with headers را انتخاب کنید.
برنج. یکی
قالب بندی در این محدوده اعمال خواهد شد، یعنی. سبک سریع جدول، که به طور پیش فرض تنظیم شده است، اعمال می شود، در حالی که ابزارهای متنی تحت نام کلی "کار با جداول" فعال می شوند، که در برگه متنی طراحی گنجانده شده است. همچنین می توانید از ابزار Format as Table در تب Home برای قالب بندی جدول استفاده کنید.
برنج. 2.
برای تهیه گزارش روی این جدول، توصیه می شود از ابزار قدرتمند "Pivot Table" استفاده کنید. برای اعمال این ابزار در لیست داده ها یا جداول داده ها، یکی از سلول های جدول داده ها باید فعال شود، به عنوان مثال، سلول موجود در جدول "Stock in stock". سپس روی دکمه "Pivot Table" که در تب "Insert" در گروه "Table" قرار دارد کلیک کنید (شکل 3).
برنج. 3.
در کادر محاوره ای Create PivotTable که باز می شود، باید یک جدول یا محدوده را انتخاب کنید (هایلایت کنید) و محل قرار دادن گزارش را مشخص کنید (ترجیحاً در یک صفحه جدید)، سپس روی OK کلیک کنید. یک جادوگر ویژه جدول محوری باز خواهد شد (شکل 4).
برنج. چهار
تصویر گزارش (PivotTable1) در سمت چپ کاربرگ نمایش داده می شود و ابزارهای ایجاد جدول محوری در سمت راست برگه قرار دارند: چهار ناحیه خالی و لیستی از فیلدها. برای ایجاد گزارش، در سمت راست، فیلدهای مورد نیاز را به قسمت‌های مربوطه جدول محوری بکشید: «فیلتر گزارش»، «نام ستون»، «نام ردیف» و «مقادیر».
به عنوان مثال، اگر فیلدهای: شماره انبار، نام، قیمت (UAH) را انتخاب کنید و آنها را به قسمت های مربوطه بکشید: "نام ستون"، "نام ردیف" و "مقادیر"، سپس آنها در این قسمت ها نمایش داده می شوند. سمت راست. در این حالت، یک جدول محوری یا گزارش در سمت چپ کاربرگ ساخته خواهد شد (شکل 5).
برنج. 5.
لازم به ذکر است که در ناحیه "مقادیر" برخی از محاسبات ریاضی به عنوان مثال جمع (Sum by the Price) انجام می شود. برای تغییر نوع محاسبات، در قسمت "مقادیر" روی فیلد "مبلغ بر اساس قیمت" کلیک چپ کرده و در منوی باز شده دستور "تنظیمات فیلد ارزش" و سپس در کادر محاوره ای "تنظیمات فیلد ارزش" را انتخاب کنید. ، عملکرد مورد نیاز را انتخاب کرده و روی دکمه OK کلیک کنید.
برای تغییر ساختار جدول محوری، در سمت راست صفحه، فیلدها را به قسمت دیگری از جدول محوری بکشید یا آنها را حذف کنید. لازم به ذکر است که برای حذف یک فیلد باید آن را به خارج از جدول بکشید.
ابزارهای قدرتمند برای تجزیه و تحلیل داده ها در Excel 2007 عبارتند از:
ابزارهای تجزیه و تحلیل What-if در تب Data در گروه Data Tools و Find Solutions در زبانه Data در گروه Analysis قرار می گیرند.
انتخاب پارامترها راه حلی برای مسئله انتخاب یک پارامتر برای تابعی از یک آرگومان فراهم می کند. مدیر سناریو برای ایجاد سناریوها طراحی شده است (ابتدا چندین سناریو برای موارد مختلف اضافه کنید)، و سپس سناریوها را برای پیش بینی روند و ایجاد گزارش در مورد سناریو بررسی کنید.
برنامه "جستجوی راه حل" برای حل سیستم های پیچیده معادلات، مسائل بهینه سازی خطی و غیر خطی در نظر گرفته شده است. افزودنی Solver بر اساس روش های تکرار شونده است.
اکسل تنها یک ویرایشگر صفحه گسترده نیست، بلکه یک ابزار قدرتمند برای محاسبات مختلف ریاضی و آماری است. این برنامه دارای تعداد زیادی توابع است که برای این کارها طراحی شده است. با این حال، همه این ویژگی ها به طور پیش فرض فعال نیستند. مجموعه ابزارها به چنین عملکردهای پنهانی تعلق دارند. "تحلیل داده ها". بیایید دریابیم که چگونه می توان آن را فعال کرد.
برای استفاده از ویژگی های ارائه شده توسط عملکرد "تحلیل داده ها"، باید گروه ابزار را فعال کنید "بسته تحلیل"با دنبال کردن مراحل خاصی در Microsoft Excel Preferences. الگوریتم این اقدامات برای نسخه های 2010، 2013 و 2016 برنامه تقریباً یکسان است و تنها تفاوت های جزئی با نسخه 2007 دارد.
پس از انجام این مراحل، تابع مشخص شده فعال شده و جعبه ابزار آن بر روی نوار اکسل موجود خواهد بود.
اکنون می توانیم هر یک از ابزارهای گروه را راه اندازی کنیم "تحلیل داده ها".
کار در هر تابع الگوریتم اقدامات خاص خود را دارد. استفاده از برخی از ابزارهای گروه "تحلیل داده ها"در درس های جداگانه شرح داده شده است.
همانطور که می بینید، اگرچه جعبه ابزار "بسته تحلیل"و به طور پیش فرض فعال نیست، فرآیند فعال کردن آن بسیار ساده است. در عین حال، بدون دانستن یک الگوریتم واضح از اقدامات، بعید است که کاربر بتواند به سرعت این تابع آماری بسیار مفید را فعال کند.
وظیفه شماره 1
تجزیه و تحلیل داده های آماری در MS Excel
هدف کار: یادگیری نحوه پردازش داده های آماری با استفاده از توابع داخلی MS Excel. ویژگی های آنالیز پک و ابزارهای آن را بررسی کنید: تولید اعداد تصادفی»، "نمودار میله ای" ، " آمار توصیفی"به عنوان مثال اندازه گیری سرعت پردازش.
مطابق با دستورالعمل کار آزمایشگاهی "اندازه گیری سرعت وسایل نقلیه" (در رشته "تحقیق و طراحی جاده ها")، داده های اندازه گیری تجربی را با استفاده از روش های آمار ریاضی در برنامه Excel پردازش کنید. برای چی:
1. محاسبه مشخصات آماری با استفاده از توابع داخلی: - حداقل مقدار سرعت حرکت Vmin.
حداکثر مقدار سرعت حرکت Vmax؛ - مقدار متوسط سرعت حرکت Vav؛
انحراف معیار؛
انحراف استاندارد میانگین Sp.
ضریب دانشجو (برای تعیین فاصله اطمینان) t; - فاصله اطمینان برای Р = 0.95.
2. دریافت مشخصات آماری با استفاده از ابزار "آمار توصیفی"از بسته اضافی" تجزیه و تحلیل داده ها ".
3. یک هیستوگرام از توزیع سرعت بسازید.
4. یک منحنی تجمعی (منحنی فرکانس تجمعی) بسازید.
5. یک منحنی نظری برای توزیع سرعت حرکت بسازید.
برای به دست آوردن مقدار کافی از داده های اولیه (نتایج اندازه گیری سرعت)، از یک آزمایش شبیه سازی با استفاده از " تولید اعداد تصادفی» افزونه های «تحلیل داده ها».
هنگام اجرای p.p. 3 و 4، فاصله سرعت ("جیب" - در اصطلاحات اکسل) را انتخاب کنید، که به شما امکان می دهد متقارن ترین هیستوگرام را دریافت کنید که قانون توزیع نرمال را نشان می دهد.
یک نمونه اجرا در فایل پیوست BasicsPC1-Student.xls آورده شده است.
رهنمودها
فرض کنید که ما یک سری از 10 آزمایش را انجام داده ایم و مقداری X را اندازه گیری کرده ایم. جدول 1. نمای تقریبی برگه "پردازش آزمایش"
ورودی‌های ستون‌های D و E نکاتی هستند که به شما کمک می‌کنند بفهمید چه ویژگی‌هایی را محاسبه خواهیم کرد. ستون F فعلا باید خالی باشد، فرمول های ما در آن قرار می گیرند.
بیایید پردازش نتایج را با محاسبه تعداد آزمایش n شروع کنیم.
برای تعیین تعداد مقادیر از تابع خاصی به نام COUNT استفاده می شود. برای وارد کردن یک فرمول با توابع، از Function Wizard استفاده کنید، که با دستور "Insert Function" از طریق منوی "Insert" - "Function" یا دکمه روی نوار ابزار با برچسب f x راه اندازی می شود.
بیایید روی سلول F6، جایی که نتیجه باید باشد، کلیک می کنیم و Function Wizard را شروع می کنیم.
مرحله اول کار (شکل 1) برای انتخاب عملکرد مورد نظر است.
برای پردازش داده های تجربی از توابع آماری استفاده می شود. بنابراین، اول از همه، در لیست دسته بندی ها، دسته " آماری" را انتخاب کنید. لیستی از توابع آماری در پنجره دوم ظاهر می شود.
لیست توابع بر اساس حروف الفبا مرتب شده است، که یافتن تابع COUNT مورد نیاز را آسان می کند ("تعداد اعداد در لیست آرگومان ها را شمارش می کند").
با مشخص شدن این عملکرد، دکمه Ok را فشار دهید و به مرحله 2 بروید.
مرحله دوم (شکل 2) برای تنظیم آرگومان های تابع استفاده می شود.
تابع COUNT باید مشخص کند که کدام اعداد را باید شمارش کند یا این اعداد در کدام سلول ها قرار دارند. دو مرحله بعدی پردازش یک سری آزمایش به طور مشابه انجام می شود.
در سلول F7، با استفاده از تابع AVERAGE، مقدار میانگین نمونه، در سلول F8، انحراف معیار نمونه، با استفاده از تابع STDEV محاسبه می شود. .
همان محدوده سلولی به عنوان آرگومان برای این توابع عمل می کند.
برای محاسبه فاصله اطمینان باید ضریب Student را تعیین کرد. این بستگی به احتمال خطا (با قابلیت اطمینان 95٪، احتمال خطا 5٪) و به تعداد درجات آزادی n-1 دارد.
برای یافتن ضریب دانشجو از تابع آماری اکسل STUDISTRIP ("معکوس توزیع دانش آموز") استفاده می شود. یکی از ویژگی های این تابع این است که اولین آرگومان، عدد 5% (یا 0.05) از صفحه کلید وارد پنجره مربوطه می شود. برای دوم، آدرس سلولی را که مقدار n در آن قرار دارد را مشخص کنید، سپس "-1" را در پنجره اضافه کنید. ورودی "F6-1" را دریافت می کنیم.
برای یافتن فاصله اطمینان از فرمول ضرب معمول استفاده می شود. البته به جای حروف باید آدرس سلولی وجود داشته باشد که ضریب Student و انحراف معیار میانگین در آن قرار دارد. به عنوان یک قاعده، مقدار فاصله اطمینان به یک رقم قابل توجه گرد می شود، همان ترتیب محیط باید برای میانگین باشد. بنابراین، نتیجه نهایی را می توان به صورت زیر نوشت: با پایایی 95 درصد، X = 0.05 ± 14.80. در نتیجه، ما خطای نسبی را در تعیین X: = CI / X cf محاسبه می کنیم (فرمول: "=F11/F7"). مقدار خطای نسبی معمولاً به صورت درصد بیان می شود، ما 0.3٪ داریم.
وظایف 2 و 3 با استفاده از افزونه "Analysis Package" (از منوی Tools .Data Analysis Histogram) انجام می شود.
برای نصب افزونه، منوی Tools Add-on ها را فراخوانی کنید و از لیست پیشنهادی افزونه های موجود برای نصب، "Analysis Package" را انتخاب کنید (شکل 1 را ببینید). نصب افزونه ها
اکسل در computer.doc).
