ÁÖÀÎÀº AutoFilter¿Í ¼Ò°è¸¦ °¡Áø Å×À̺íÀ» ´É°¡ÇÑ´Ù
2008³â4¿ù 9ÀÏ 5:23 AM¿¡ ¿¡ ¹èÄ¡ÇÏ´Â
´É°¡ ³¡ ½Ã¸®Áî¿¡¼ ´Ù¸¥ Æ÷½ºÆ®¸¦ Àд °ÍÀ» È®ÀÎÇϽʽÿÀ!
Microsoft ExcelÀÇ ¾ó¸¶¸¦ »ç¿ëÇØ ´ç½ÅÀº Àΰ¡? ³ª´Â ÀÌ·¸°Ô »ý°¢Çß´Ù. ¿ì¸®°¡ ±×°Í ´Ù¸¥ ³ëÄ¡ ³ôÀº ÂÊÀ¸·Î Á·´äÇϰí Àֱ⠶§¹®¿¡ ÁغñÇϽʽÿÀ. ÀÌ´Ù °·ÂÇÑ ±×·¯³ª ¼ö½Ã·Î ÀúȰ¿ëÀûÀÎ °ø±¸ ´É°¡ÇϽʽÿÀ. ÀúÈñÀÇ ¸¹Àº °Í »ç¿ë °èȹ°ú ´ëÀ§ ¿¹»ê¿¡ ±×°ÍÀº ¸ÅÀÏ¿¡, ¸Å¸Å ÀڷḦ ºÐ¼®Çϰí, ¶§¶§·Î Áß¿äÇÑ ÀÏÀ»°ú °°Àº Çϱâ À§ÇÏ¿© »ç¹«½Ç 3¿ù ±¤Áõ ¼ö¿µÀåÀ» ó¸®ÇϽʽÿÀ. ¸î¸î ¿Â¶óÀÎ ±â¼úÀº ±â¿ïÀδ٠Æ÷½ºÆ®´Â ÀÌ¹Ì ¿µ¸®ÇÏ°í ±×·¯³ª »ý¼ÒÇÑ ±â´ÉÀ» °°ÀÌ Æ÷ÇÔÇß´Ù VLOOKUP ±×¸®°í SUMIF. Áö±Ý ´Ù¸¥ ¿øÀ» À§ÇÑ ½Ã°£ÀÇÀÌ´Ù Æ÷¾ÇÇÑ ³¡À» ´É°¡ÇϽʽÿÀ!
³ª´Â ´É°¡ÇÑ´Ù, ¸¸µå´Â ¿¬ÁÖȸ¿¡¼ ÀÌ¿ëµÈ Ư¡À» 2¸¦ ¼³¸íÇϱâ À§ÇÏ¿© ·Á°í Çϰí Àִ٠ǥ ºÐ¼® ½º³À. Ç¥ ÀÚ·á´Â ´ç½ÅÀÌ µ¥ÀÌŸº£À̽º ÃßÃâ¹° ¶Ç´Â CSV ÆÄÀÏ¿¡°Ô¼ ¾ò´Â ¹«½¼¿¡ÀÌ´Ù. AutoFilter ±×¸®°í oft ¹Ù¶óº¸´Â ¼Ò°è ±â´ÉÀº, ¶¥Äá ¹öÅÍ¿Í ÃÊÄÝ·¿ °°ÀÌ, ÇÔ²² »ç¿ëµÇ±â À§ÇÏ¿© Àǹ̵ȴÙ. ±×µéÀº" ºÎºÐ ÁýÇÕ, ÇÕ°è, Æò±Õ, Á¶»ç, ¹× ´õ ¸¹Àº °ÍÀ» Çϱâ À§ÇÏ¿© ½ºÇÁ·¹Æ®½ÃÆ®¸¦ ¾ÏÈ£·Î ÇÏ´Â ½¬¿î ¹æ¹ýÀ» "Á¦°øÇÑ´Ù.
¾Æ·¡¿¡ ´É°¡ Å×À̺íÀº ºÎ¿¡ ÀÇÇÏ¿© ¿ì¸®ÀÇ È¸»çÀÇ ±¸ÀÔÀÇ ÃßÃâ¹°ÀÌ´Ù. ¿ì¸®´Â ÀüÇüÀûÀÎ "¿ìµÎ¸Ó¸®" ÁÙÀ» 11ÀÇ ÀÚ·á ÁÙ¿¡ ¼±ÇàµÇ¾î ´Þ¶ó°í ÇÑ´Ù. ´ç½ÅÀº ÀÌ Å×À̺íÀ» À§ÇØ ±â´ÉÀ» Æ÷ÇÔµÈ I º¼ ¼ö ÀÖ´Ù: ÇÕ°è, Á¶»ç, ºÐ ¹× Æò±Õ. ³ªÁß¿¡ ±×µé¿¡ °üÇÏ¿© ´õ ¸¹Àº °Í.
Áö±Ý, µÎ¸ñÀº µé¾î¿À°í Çʿ䰡 ÀÖ´Ù airtime¸¦ ±¤°íÇÏ´Â ´Ù¸¸ º¼ ¸Å¸Å »ç¶÷µéÀÌ ±¸¸ÅÇÏ°í ¾ó¸¶, ¿ì¸®°¡ ÁöºÒÇϰí ÀÖ´ÂÁö ¾ó¸¶¸¦. AutoFilter´Â ÀúÈñ¿¡°Ô Àüü ÁÙ º¸ÀÌ´Â ¶Ç´Â ¼¼Æ÷ ³»¿ë¿¡ ±Ù°ÅÇÑ º¸ÀÌÁö ¾Ê´Â ÇÏ´Â ÈûÀ» ÁØ´Ù. ¼Ò°è ±â´ÉÀº ÇÕ°è, Á¶»ç¸¦, ÃÖ¼ÒÇѵµ¿Í Æò±Õ ±×·¯¹Ç·Î »õ·Ó°Ô ÇÑ´Ù. »¡¸® AutoFilter¸¦ Ãß°¡ÇÏÀÚ.
AutoFilter¸¦ ´É°¡ÇϽʽÿÀ
AutoFilter¸¦ Ãß°¡Çϱâ À§ÇÏ¿©´Â, ¶õ »óÇ¥ (A1¸¦ Æ÷ÇÔÇÏ´Â ¿ìµÎ¸Ó¸® ÁÙÀ» Æ÷ÇÔÇÏ¿© ¼¼Æ÷ÀÇ Àüü ¹üÀ§¸¦, ¼±Á¤ÇϽʽÿÀ: F12). ±× ÈÄ¿¡ »ç¿ë ÀÚ·á -> ¿©°ú±â -> AutoFilter. Áö±Ý ´ç½ÅÀº Å×ÀÌºí¿¡ ÀÖ´Â °¢ ¶õÀÇ À§ ¿©°ú±â Áö½ÃÀÚ¸¦ º¼ ¼ö ÀÖ´Ù:
ûÃÊÇÑ °ÍÀº À̰ÍÀÌ´Ù: ¾î¶² ¿ Ç¥Á¦µçÁö Ŭ¸¯Çϰŵç, AutoFilter´Â¿¡¼ ¼±Á¤Çϱâ À§ÇÏ¿© ´ç½Å¿¡°Ô ´ç½ÅÀ» À§ÇÑ ¶õ¿¡ ÀÖ´Â °¡Ä¡ÀÇ µå·Ó´Ù¿î ¸®½ºÆ®¸¦ ÁÖ°í, °á°ú¸¦ À§ÇÑ Àüü Å×À̺íÀ» °Å¸¥´Ù:
Å×ÀÌºí ³»¿ë¿¡ ±Ù°ÅÇÑ ¸íºÎ´Â µ¿ÀûÀÌ´Ù, ±×·¡¼ ÁÙÀ» Ãß°¡Çϰųª ±âÁ¸ÇÏ´Â °¡Ä¡ ¹æ¹ýÀ» ¹Ù²Ù¾î¼ µå·Ó´Ù¿î ¸®½ºÆ®´Â ¶ÇÇÑ »õ·Ó°Ô ÇÑ´Ù. 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. The original eleven data rows are still present, although some are invisible based on the selected filter. A2:F12 is still data range.
We quickly see the two ¡°Airtime¡± buys in this dataset, and the average price paid is $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. Subtotal operates only on visible rows of data in a range, so it¡¯s great in combination with 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)
COUNT=Subtotal(102,F2:F12)
MIN=Subtotal(105,F2:F12)
AVERAGE=Subtotal(101,F2:F12)
Now that you have seen the power of AutoFilter and Subtotal, go give it a shot. After all, Fantasy-League baseball is just starting.
Pete Kushmeider, Writer and blogger
Technorati Tags: Excel, Data Analysis
If you enjoyed this post, make sure you subscribe to my RSS feed!
» Filed Under MS Office Tips
Save this page |
Stir it up on Mixx |
Add to Reddit |
Related Posts
- Microsoft Excel Basics Tutorial - Learning how to use Excel
- Excel Basics - How to use the Conditional Sum wizard
- Excel Tutorial - How to make a simple graph or chart in Excel
- MS Excel Tips and Tricks – How to count things in Excel
- Excel Tutorial - Example of how to use the VLOOKUP function
Save this page
Stir it up on Mixx
Add to Reddit
























Excellent article¡¦it was exactly what I needed to learn to solve a challenge I had.
Great work!