• excel中去重计数 在Excel中计数 (Counting in Excel)The most popular function in Excel is SUM, based on my experience. That's probably the first function you learned how to use, and in the old days, it ...


    The most popular function in Excel is SUM, based on my experience. That's probably the first function you learned how to use, and in the old days, it was the only function that you could insert with the appropriately named AutoSum button.

    根据我的经验,Excel中最受欢迎的功能是SUM。 那可能是您学会如何使用的第一个功能,并且在过去,它是唯一可以使用适当命名的AutoSum按钮插入的功能。

    Sometimes we overlook how much counting we have to do every day too. Here are a few scenarios that you might encounter at work (or should I say "en-count-er"?):

    有时我们也忽略了我们每天也要做多少计数。 以下是您在工作中可能遇到的几种情况(或者我应该说“遇到”)?

    • Sales: number of orders for a specific product, from a list in a worksheet

    • Human Resources: count of employees with 10+ years employment

    • Shipping: number of shipments pending, that do not have a zip code


    Fortunately, your favourite spreadsheet can help – there are many different ways to count things in Excel.

    幸运的是,您最喜欢的电子表格可以提供帮助– 在Excel中有许多不同的计数方法

    7 Ways to Count in Excel http://www.contextures.com/xlFunctions04.html

    在Excel中计数的7种方法 (7 Ways To Count in Excel)

    A couple of months ago, I shared a slide show that gives a quick summary of 7 Ways to Count in Excel. It includes the following examples:

    几个月前,我分享了一张幻灯片演示, 简要介绍了Excel中7种计数方法 。 它包括以下示例:

    • Count Numbers

    • Count Data

    • Count Blanks

    • Count With Criteria

    • Count With Criteria in Text String

    • Count With Multiple Criteria

    • Count in Filtered List


    视频:7种计数方法 (Video: 7 Ways to Count)

    The slide show was very well-received, so I've made a video that shows a quick example of each counting technique. It just covers the basics, and I have other counting videos that you can watch, for more information, and other examples.

    幻灯片放映非常受欢迎,因此我制作了一个视频,展示了每种计数技术的快速示例。 它仅介绍了基础知识,另外还有一些您可以观看的计数视频 ,以获取更多信息和其他示例。


    下载样本文件 (Download the Sample File)

    If you want to follow along with the video, and see more counting formulas, you can download the sample workbook from my website. The zipped file is in xlsx format, and does not contain macros. There is a separate sheet for each of the examples in the video, and several other examples too.

    如果您想跟随视频一起观看,并查看更多计数公式,可以从我的网站下载示例工作簿 。 压缩文件为xlsx格式,不包含宏。 视频中的每个示例都有单独的表格,其他几个示例也都有。

    Now, let's get back to counting the number of days until the weekend!


    7 Ways to Count in Excel http://www.contextures.com/xlFunctions04.html

    翻译自: https://contexturesblog.com/archives/2015/05/21/counting-in-excel/


  • excel中去重计数_如何在Excel中计数

    千次阅读 2020-08-10 15:39:19
    excel中去重计数 如何在Excel中计数 (How to Count in Excel)There are lots of different ways to count things in Excel – maybe you need to count the numbers in a column, or all the data, or just the ...


    There are lots of different ways to count things in Excel – maybe you need to count the numbers in a column, or all the data, or just the blank cells. Fortunately, there is a function for each of those:

    在Excel中,有很多不同的方法可以对事物进行计数 –也许您需要对列中的数字,所有数据或仅空白单元格进行计数。 幸运的是,每个功能都有一个功能:

    • COUNT

    • COUNTA



    For example, to count the blank cells in the range A1:A5, use the following formula in cell A7:




    count blank cells www.contextures.com

    更复杂的计数 (More Complicated Counting)

    If you have more complicated things that you need to count, there are other functions to do the job:







    For example, to count only the visible numbers, after filtering and/or manually hiding rows in a list, use a SUBTOTAL formula. This example uses 102 as the second argument, so it counts numbers only, in the visible rows (filtered or manually hidden).

    例如,在过滤和/或手动隐藏列表中的行之后,要仅计算可见数,请使用SUBTOTAL公式。 本示例使用102作为第二个参数,因此它仅对可见行(已过滤或手动隐藏)中的数字进行计数。


    =小计( 102 ,B2:B10)

    In the screen shot below, there are 5 visible numbers in cells B2:B10, and that is the result in cell B15, where the SUBTOTAL function is used.


    The COUNT function, used in cell B12 in the screen shot below, returns 8 – it counts numbers in the hidden rows too.

    在下面的屏幕快照的单元格B12中使用的COUNT函数返回8 –它也对隐藏行中的数字进行计数。

    count visible numbers www.contextures.com

    观看幻灯片放映 (Watch the Slide Show)

    To see a quick overview of 7 ways to count in Excel, you can watch this short slide show. It also contains a video on using the COUNTIFS function. You can see more examples on my Excel Count Functions page, and download the sample file.

    要快速了解Excel中7种计数方式,您可以观看此简短的幻灯片演示。 它还包含有关使用COUNTIFS功能的视频。 您可以在“我的Excel计数功能”页上看到更多示例,并下载示例文件。


    使用COUNTIF函数计算特定项目 (Count Specific Items With COUNTIF Function)

    This video shows how to use the COUNTIF function to count cells that contain a specific string of text, such as "Pen".


    翻译自: https://contexturesblog.com/archives/2015/03/26/how-to-count-in-excel/


  • 符号在excel中的引用 如何在Excel中添加项目符号 (How to Add Bullets in Excel) There's no built-in feature for bullets in Excel, like there is in a Word document. Bullets are a handy feature when you're ...


    There's no built-in feature for bullets in Excel, like there is in a Word document. Bullets are a handy feature when you're making a list, so it would be nice to use them occasionally, in our Excel files. Even though they're not built in, keep reading, to see how to add bullets in Excel.

    与Word文档中一样,Excel中没有项目符号的内置功能。 列出清单时,项目符号是一项方便的功能,因此偶尔在我们的Excel文件中使用它们会很不错。 即使它们不是内置的,请继续阅读以了解如何在Excel中添加项目符号。

    使用快捷方式在Excel中添加项目符号 (Add Bullets in Excel With Shortcut)

    Fortunately, you can add a bullet to a cell with either of the following keyboard shortcuts:


    Alt + 7 or Alt + 0149

    Alt + 7Alt + 0149

    NOTE -- This shortcut only works if you type the numbers on the Number keypad – it doesn't work if you use the numbers at the top of the keyboard (above the letters). If you're using a laptop that doesn't have a separate keypad for numbers, you can use the special keys to activate the "number keypad" section of the keyboard.

    注意-此快捷方式仅在您在数字键盘上键入数字时才有效-如果您使用键盘顶部的数字(字母上方)则不起作用。 如果您使用的笔记本电脑没有单独的数字小键盘,则可以使用特殊键来激活键盘的“数字小键盘”部分。

    After you type the bullet shortcut, you can format the cell to Align Right, so the bullet is close to the text in the adjacent column.


    bullets in excel worksheet

    使用公式在Excel中添加项目符号 (Add Bullets in Excel With a Formula)

    You can add bullets with a formula too -- just use the CHAR function, and refer to either of the numbers that we used in the shortcut keys above. For example, in cell A4, type this formula: =CHAR(7)

    您也可以使用公式添加项目符号-只需使用CHAR函数,然后引用上面快捷键中使用的任意一个数字即可。 例如,在单元格A4中,键入以下公式: = CHAR(7)

    When you press Enter, a bullet will appear in the cell, just like the one from the keyboard shortcut.


    Using a formula gives you more flexibility -- you can make a rule for when the bullet should appear. For example, type this formula in cell A4:

    使用公式可提供更大的灵活性-您可以为何时显示项目符号制定规则。 例如,在单元格A4中键入以下公式:


    = IF(B4 =“”,“”,CHAR(7))

    When you press Enter, no bullet will appear in the cell, because cell B4 is empty. Type something in cell B4, and the bullet appears.

    当您按Enter键时,单元格B4为空,因此该单元格中不会出现任何项目符号。 在单元格B4中键入内容,项目符号出现。

    In the list shown below, there is no bullet in cell A5, because cell B5 is empty.


    bullets in excel with formula

    项目符号和编号命令 (Bullets and Numbering Command)

    There is another way to add bullets to an Excel worksheet. If you're adding notes for the people who will use the file, you can put the text in a textbox that will float over the worksheet, instead of entering your notes in the cells.

    还有另一种将项目符号添加到Excel工作表中的方法。 如果要为使用文件的人员添加注释,则可以将文本放在浮动在工作表上方的文本框中,而不必在单元格中输入注释。


    In a textbox, there are a few more formatting options, including a Bullets command. Last week, Bob Ryan sent me a Student Budget workbook, and he had the instructions in formatted textboxes, including bullet points.

    在文本框中,还有更多的格式设置选项,包括“项目符号”命令。 上周,鲍勃·瑞安(Bob Ryan)给我寄了一份《 学生预算》工作簿 ,他在格式化文本框中(包括项目符号要点)获得了说明。

    To add bullets to your text boxes in Excel 2010 or later, right-click on the text, and click Bullets, in the popup menu. In Excel 2007, the Bullet command is on the popup formatting toolbar, just above the right-click menu.

    要将项目符号添加到Excel 2010或更高版本的文本框中,请右键单击文本,然后在弹出菜单中单击“项目符号”。 在Excel 2007中,“项目符号”命令位于弹出菜单格式工具栏上的右键单击菜单上方。


    Next, select the bullet style that you want -- there are a few options to choose from.


    Or, click the Bullets and Numbering command, to see more options.



    观看Excel项目符号视频 (Watch the Excel Bullets Video)

    To see the steps for adding bullets in a cell, or in a textbox, watch this short Excel video tutorial.



    翻译自: https://contexturesblog.com/archives/2011/10/26/bullets-in-excel-cells-and-textboxes/


  • excel中省市列表 坚持在Excel中的列表 (Stick to the List in Excel)You open lots of Excel files every day, and sometimes open the same file several times. In Excel 2007, if you click the Office Button, ...


    坚持在Excel中的列表 (Stick to the List in Excel)

    You open lots of Excel files every day, and sometimes open the same file several times. In Excel 2007, if you click the Office Button, you can see a list of documents that you've opened recently. Click on file name, to open that file again.

    您每天打开许多Excel文件,有时会多次打开同一文件。 在Excel 2007中,如果单击Office按钮,则可以看到您最近打开的文档列表。 单击文件名,再次打开该文件。


    更改文件数量 (Change the Number of Documents)

    My Recent Documents List was showing 17 files, and I guess that's the default number, since I don't remember changing it. Instead of leaving the default setting, you can show more or fewer files.

    我的最近文档列表显示了17个文件,我想这是默认数字,因为我不记得更改它了。 除了显示默认设置,您还可以显示更多或更少的文件。

    To change the setting:


    1. Click the Office Button, then click the Excel Options button

    2. Click the Advanced category

    3. In the Display section, change the number for Show this number of Recent Documents.

    4. Click OK, to close the Excel Options window.

      单击确定,关闭“ Excel选项”窗口。


    The maximum number of files is 50, but that many might not show unless you have a really tall monitor.


    在Excel 2003中更改设置 (Change the Setting in Excel 2003)

    In Excel 2003, the maximum number of files you can show in the list is 9. To change the setting:

    在Excel 2003中,列表中最多可以显示9个文件。要更改设置:

    1. On the Tools menu, click Options.

    2. On the General tab, change the number for the Recently Used File List.

    3. Click OK, to close the Option dialog box.



    清除最近的文件清单 (Clear the Recent Documents List)

    In either version of Excel, you can clear the list, by changing the number to zero. In Excel 2003, you can also remove the check mark from the Recently Used File List setting.

    在任一版本的Excel中,都可以通过将数字更改为零来清除列表。 在Excel 2003中,还可以从“最近使用的文件列表”设置中删除复选标记。

    After the list is cleared, you can change the setting to a higher number, to start building the list again.



    将项目固定到列表 (Pin Items to the List)

    Normally, the older items in the list drop off the bottom, as new files are opened. In Excel 2007, there's a push pin icon at the right of each file name. To keep a file on the list, click that push pin, to activate it.

    通常,列表中的旧项目会在打开新文件时从底部开始。 在Excel 2007中,每个文件名的右侧都有一个图钉图标。 要将文件保留在列表中,请单击该图钉以将其激活。

    This is another one of the Excel 2007 features that I didn't notice until recently. (Maybe that's why it's call the Recent Documents list!) Now I use it quite often, to "stick" files that I'm working with for a few days.

    这是Excel 2007的另一项功能,直到最近我才注意到。 (也许这就是为什么它被称为“最近的文档”列表的原因!)现在,我经常使用它来“粘贴”我正在使用的文件几天。

    In the screen shot below, the ProjectWorkCurrent.xlsm file is pinned to the list. Instead of the flat grey push pin, there's a vertical green push pin.

    在下面的屏幕快照中,ProjectWorkCurrent.xlsm文件固定在列表中。 除了平的灰色图钉,还有一个垂直的绿色图钉。

    That file will work it's way down the list, if it's not opened for a while, but it will stick to the list, and won't drop off.


    翻译自: https://contexturesblog.com/archives/2009/11/06/stick-to-the-list-in-excel/


  • Logical functions are some of the most popular and useful in Excel. They can test values in other cells and perform actions dependent upon the result of the test. This helps us to automate tasks in ...
  • 符号在excel中的引用 如何在Excel工作表添加表情符号 (How to Add Emojis in Excel Worksheets) In the old days, we typed emoticons in our messages, to show a grin, or wink, etc. Now we use emojis online,...
  • excel中多条件求和 在Excel中求和的7种方法 (7 Ways to Sum in Excel)What was the first function that you learned in ... 如果您还记得很久,您在Excel中学习的第一个功能是什么? I'll bet it was the SUM f...
  • excel 自动加超链接 在Excel中停止自动超链接 (Stop Automatic Hyperlinks in Excel) The Very Scary Fall Giveaway for Excel Nerds is going strong, with lots of frightening Excel horror stories and some ...
  • excel中vlookupThis article is for all excel wizards, conversant with majority of excel features and functions but have not realised the potential of Power Query for data extraction and transformation ...
  • Excel and Visio: Generating Diagrams Automatically 中文字幕 Excel和Visio:自动生成图表 中文字幕Excel and Visio: Generating Diagrams Automatically 了解如何从Excel数据自动创建Visio图表 随着Visio MVP ...
  • 在excel里面过滤文件 在Excel 2007轻松过滤 (Easy Filtering in Excel 2007) Last month you saw a quick way to filter for the selected item in a pivot table, and today you'll see a similar technique for ...
  • Excel 使用高级筛选功能

    万次阅读 2008-03-20 16:49:00
    Excel 是存放大量数据的地方,但它又不是个专业的数据库。然而Excel 用户对数据的操作要求却...例如,AutoFilter 的自定义只支持两个判断式的Or 或And关系,当我们员工表里查找“张三”和“李四”的信息时,还能发挥
  • excel中取消隐藏没用 在Excel中创建秘密隐藏的问题 (Create Secret Hidden Questions in Excel) Everyone loves a good mystery! And now you can create a few in your Excel workbooks. I've been updating some ...
  • excel vb组合框 在Excel 2010修复组合框大小调整 (Fix Combo Box Sizing in Excel 2010)With Excel data validation, you can create drop down lists on a worksheet. However, the font size is very small, ...
  • excel中双纵轴图表Creating a professional looking chart for Excel presentations is extremely easy in Excel 2007. Making charts is a more interesting way to present data than just going through the rows...
  • excel表格计算年龄 在Excel中计算年龄 (Calculating Ages in Excel) Can you remember how old you are? Or are you like me, and have to ask, "What year is it?" and then subtract your birth year? Or do you ...
  • java and excel

    2004-08-11 09:38:00
    现在MS Excel在用户作为数据上传和上报使用的非常普遍,java处理excel是非常简单的要求,但是j2se没有提供相关的类,Apache POI给我们提供了很好的一个工具。通过他来处理excel非常方便,重要的一点...
  • 在Excel中创建彩色的Harvey球 (Create Colored Harvey Balls in Excel) It’s easy to add conditional formatting icons in Excel, by selecting one of the built in options. These were introduced in Excel ...
  • 在Excel中更改默认图表类型 (Change the Default Chart Type in Excel) On July 25th I announced a giveaway for Jon Peltier’s amazing Chart Utility, and thanks for all the entries! The deadline was ...
  • excel计算班级总分排名 在Excel中计算排名 (Calculating Rank in Excel) To do some research on sorting, I hauled one of the big, dusty Excel books off my shelf, to see if there were any scintillating ...
  • excel日期相减去除周末 仅在Excel中允许周末日期 (Allow Weekend Dates Only in Excel) With Excel Data Validation, you can add rules to a data entry sheet, and control what people put in the cells....
  • excel vba到期提醒 在Excel中监视到期日期 (Monitor Expiry Dates in Excel) Do you use Excel to keep track of software subscriptions, or domain registrations, or other things with an expiry date?...
  • excel 删除重复项 在Excel 2007删除重复项 (Remove Duplicates in Excel 2007)In Excel 2003 and earlier versions, you can use an Advanced Filter to remove duplicates. In Excel 2007, there's a new command...
  • excel日期相减去除周末 在Excel中突出显示周末日期 (Highlight Weekend Dates In Excel) Yes, the weekend is over, but another one is just five days away! To make it easier to keep track of Saturdays and ...
  • 在Excel中计算距离

    2020-08-05 04:54:29
    在Excel中计算距离 (Calculate Distance in Excel) How do you calculate distance? In the small town where I grew up, distance was measured in blocks or travel time. For example, my school was about 5 ...
  • 宾果浏览器 在Excel中创建宾果卡 (Create Bingo Cards in Excel) It’s Friday, and things are slow at the office. To liven things up, you could create bingo cards in Excel, and organize a game during the ...
  • excel图片放大预览 在Excel中预览图片问题 (Preview Picture Problems in Excel) In the good old days of Excel, you could change a workbook setting, and see a preview picture of the file contents, before ...
  • excel中的菜单栏自定义The status bar at the bottom ofExcel’s window can tell you all kinds of interesting information,such as whether Num Lock is on or off, the page number, and the average of numbers...
  • excel交叉表查询 在Excel中计算查询表 (Counting Query Tables in Excel) A few years ago, Ron Coderre created his PivotPlay PLUS Add-in that you can download from my Contextures site. This free add-in was...
  • excel按名字拆分文件 在Excel中拆分姓氏和名字 (Split First and Last Names in Excel) If you have a list of names in Excel, with first and last names separated by a comma, you can use an Excel feature to ...



1 2 3 4 5 ... 20
收藏数 40,381
精华内容 16,152