Master Excel Tables with AutoFilter and Subtotal ماجستير مع جداول اكسل autofilter والمجموع الفرعي

Posted on April 9, 2008 at 5:23 am نشر يوم 9 / ، 2008 في 5:23 صباحا

How much of Microsoft Excel are you using? وكم من مايكروسوفت اكسل الذي تستخدمه؟ I thought so. اعتقدت ذلك. Get ready because we’re stepping it up another notch. Excel is a powerful but often underutilized tool. احصل على استعداد لان نقوم انها خطوة حتى آخر الشق. اكسل هي قوية ولكن في كثير من الأحيان أداة ناقصه. Many of us use it every day to plan and track budgets, analyze marketing data, and occasionally to do more important things such as manage the office March Madness pool. الكثير منا استخدام كل يوم الى خطة المسار والميزانيات ، وتحليل البيانات التسويقية ، واحيانا تفعل اشياء اكثر اهمية مثل ادارة مكتب مارس الجنون للسباحه. Several Online Tech Tips posts have already covered clever but unfamiliar functions like نصائح عدة وظائف تقنيه الانترنت قد شملت بالفعل ذكي ولكن غير مالوفه مثل المهام VLOOKUP Vlookup and و SUMIF Sumif . Now it’s time for another round of Outrageous Excel Tips! الآن حان الوقت لجولة اخرى من الفاحشه اكسل نصائح!

I’m going to illustrate two Excel features which, used in concert, make tabular analysis a snap. انا ذاهب الى توضيح السمات التي اثنين اكسل ، واستخدمت في الحفله ، جعل التحليل الجدولي أ الخاطف. Tabular data is what you get from a database extract or CSV file. البيانات المجدوله هو ما تحصل عليه من قاعدة بيانات او استخراج ملف CSV. The AutoFilter and oft-overlooked Subtotal functions, like peanut butter and chocolate, are meant to be used together. They provide an easy way to “code” a spreadsheet to do subsets, sums, averages, counts, and more. فان autofilter والتي غالبا ما يغفل المجموع الفرعي المهام ، مثل زبدة الفول السوداني والشوكولاته ، ويراد لها ان تستخدم معا. أنها توفر طريقة سهلة ل"المدونه" جداول البيانات الى قيام مجموعات فرعية ، المبالغ ، والمتوسطات ، والتهم ، وأكثر.

The Excel table below is an extract of our company’s purchases by department. أكسل الجدول ادناه يعرض مقتطفات من شركتنا عن طريق إدارة المشتريات. We have a typical “header” row, followed by 11 data rows. لدينا النمطيه "رأس" على التوالي ، تليها 11 بيانات الصفوف. You can see the functions I included for this table: Sum, Count, Min and Average. يمكنك ان ترى انني المهام المدرجه لهذا الجدول : وخلاصة القول ، ان تعول ، ودقيقة فى المتوسط. More about them later. المزيد حول لهم في وقت لاحق.

Now, the boss comes in and needs to see just how much advertising airtime the Marketing people are purchasing, and how much we are paying. الآن ، ويأتي في بوس ويحتاج فقط الى انظر كم البث الاعلاني تسويق الناس الشراءيه ، والى اي مدى نحن نولي. AutoFilter gives us the power to make entire rows visible or invisible based on cell contents. Autofilter يعطينا القدرة على جعل كامل الصفوف مرئية او غير مرئية على اساس خلية المحتويات. The Subtotal function updates the Sum, Count, Min and Average accordingly. المجموع الفرعي ظيفة يستكمل المبلغ ، والفرز ، ودقيقة فى المتوسط وفقا لذلك. Let’s quickly add an AutoFilter. دعنا بسرعة يضاف autofilter.

Excel AutoFilter أكسل autofilter

To add AutoFilter, select the entire range of cells, including the header row containing the column labels (A1:F12). اضف الى autofilter ، اختر كامل مجموعة من الخلايا ، بما في صف العنوان يتضمن العمود علامات (A1 : f12). Then use Data -> Filter -> AutoFilter. عندئذ استخدام البيانات --> فلتر --> autofilter. Now you can see the filter indicator above each column in the table: الآن يمكنك ان ترى المرشح المؤشر فوق كل عمود في الجدول :

The neat thing is this: click on any column heading, and AutoFilter gives you a drop-down list of values in the column for you to select from, and filters the entire table for the result: بارعه فإن هذا الشيء هو : اضغط على اي عنوان العمود ، وautofilter يعطيك قائمة منسدله من القيم في العمود لكم لاختيار من ، وفلاتر الجدول كله لما ستسفر عنه :

The lists are dynamic based on table contents, so adding a row or changing an existing value means the drop-down list is updated also. القوائم الديناميه استنادا الى محتويات الجدول ، وذلك على التوالي اضافة او تغيير قائم تعني القيمه القائمة المنسدله يتم تحديثها ايضا. You can show all values (default), select just one value, or create a custom filter based on boolean logic. يمكنك عرض كل القيم (افتراضي) ، واختيار قيمه واحدة فقط ، او يخلق العرف مرشح على اساس منطقي المنطق. In this example we need to select the “Airtime” value. في هذا المثال نحتاج الى اختيار "البث" القيمه.

The result is: والنتيجة هي :

AutoFilter acts by making rows visible and invisible. Autofilter الافعال بجعل الصفوف مرئية وغير مرئية. The original eleven data rows are still present, although some are invisible based on the selected filter. الاصلي احد عشر الصفوف البيانات لا تزال موجودة ، على الرغم من ان بعض خفية على أساس اختيار مرشح. A2:F12 is still data range. A2 : f12 لا تزال طائفة البيانات.

We quickly see the two “Airtime” buys in this dataset, and the average price paid is $5000. ونحن نرى اثنين بسرعة "البث" تشتري في هذه البيانات ، وكان متوسط السعر الذي يدفعه هو 5000 دولار. Use the drop down filters to select all values again, or select a different value, and do it on any number of columns. استخدام اسقاط مرشحات لاختيار كل القيم مرة اخرى ، او اختيار قيمة مختلفة ، وذلك على أي عدد من الاعمده. Depending on your table, some columns will make sense for filtering and others may not. حسب الجدول الخاص بك ، بعض اعمدة لن يكون له معنى لترشيح وآخرون لا يجوز.

Excel Subtotals أكسل المجاميع الفرعية

In this example, the values for Sum, Count, Min, and Average update based on the filter automatically because of the powerful Subtotalfunction Subtotalfunction. في هذا المثال ، من اجل القيم وخلاصة القول ان العد ، ومين ، وعلى اساس متوسط تحديث الفلتر تلقائيا بسبب الاقوياء subtotalfunction subtotalfunction. Subtotal operates only on visible rows of data in a range, so it’s great in combination with AutoFilters. المجموع الفرعي تعمل فقط على مرئي صفوف البيانات في طائفة ، لذلك فانه في مجموعة كبيرة مع autofilters. Subtotal is also simple to set up. The syntax for Subtotal is: المجموع الفرعي هو ايضا بسيطة الى اقامة. النحو من أجل المجموع الفرعي هو :

=Subtotal(function#, range) = المجموع الفرعي (وظيفة # ، طائفة)

Okay, you do have to know the “function #” which is a pain. Are you listening MicroSoft? حسنا ، لانك قد تعرف ب "وظيفة #" الذي هو الألم. هل انت الاستماع مايكروسوفت؟ Here is the list of commonly used functions with Subtotal. هنا قائمة يشيع استخدامها المهام مع المجموع. Others can be accessed by searching Excel Help: ويمكن الوصول الى الاخرين عن طريق البحث في اكسل مساعدة :

Function # الوظيفة #	Function Name اسم الوظيفة
101	AVERAGE متوسط
102	COUNT العد
104	MAX ماكس
105	MIN دقيقة
109	SUM المبلغ

In the example the Sum, Count, Min and Averages are obtained by these respective formulae: في المثال المبلغ ، والفرز ، ومين المتوسطات التي حصل عليها كل من هذه الصيغ :

SUM =Subtotal(109,F2:F12) المبلغ المجموع = (109 ، والطور الثاني : f12)

COUNT =Subtotal(102,F2:F12) العد = المجموع الفرعي (102 ، والطور الثاني : f12)

MIN =Subtotal(105,F2:F12) مين المجموع = (105 ، والطور الثاني : f12)

AVERAGE =Subtotal(101,F2:F12) المتوسط = المجموع الفرعي (101 ، والطور الثاني : f12)

Now that you have seen the power of AutoFilter and Subtotal , go give it a shot. الآن بعد ان رأيتم قوة autofilter والمجموع ، يذهب اعطائها طلقة. After all, Fantasy-League baseball is just starting. على كل حال ، فانتازيا - جامعة البيسبول هي مجرد البدء.

Pete Kushmeider, Writer and blogger بيت kushmeider ، الكاتب وBlogger

Technorati Tags: Technorati البطاقات : Excel ابرع , ، Data Analysis تحليل البيانات