如何自动将数值换算成万元显示？

zhuwei520

第一个问题举例：{SUM(IF(MOD(COLUMN(M4：FD4）,4）=3,M4：FD4））｝表达的什么意思

回答:
假设你的问题为在某个单元格中的复公式输入为{=SUM(IF(MOD(COLUMN(M4：FD4）,4）=3,M4：FD4））｝
那么, 它的意思为: M4到FD4单元格中, 符合下面条件制的, 单元格的内容被求和.

条件: 把M4到FD4单元格中的列号(列号即M-->FD, A列对应1, B列对应2, M则对应13)对4求余zd, 如果结果等于3, 那么此单元格的内容就被求和.
下面做个简化的例子:

mod(column(m4:fd4),4)=3

追问

结果等于3什么意思

追答

就是余数等于3的意思, 例如7除以4, 余数就是3了. 11除以4余数也等于3. 如果余数等于3, 那么当前单元格的内容就要累加到结果中.


第二个问题举例：EXCEL隔行隔列怎么拖公式
EXCEL表中每隔N行或N列需要的公式相同，而且有很多行或列，怎样拖公式？要简单方便快捷哦~

像这样并不完全一样但有规律性结构一样，只是引用的单元格数据不同，要重复很多行很多列，要怎么做？

回答：
假设隔百3行公式相同度
=if(mod(row(a1),3)=0),你的知公式道,"")
=========================================
根据你的补充回,试试这个公式:
C1输入:
=if(mod(column(),3)=0,a1-b1,"")
公式右拉答,下拉

第三个问题举例：
EXCEL中“column”函数和“MOD”函数的用法

=SUM(IF(MOD(COLUMN(H3:BP3)-1,2)=0,H3:BP3))

回答：
这是应该是个数组公式,是要实现有条件加总
那么需要分两个问题来回答你：
1、数组公式：你在EXCEL帮助是搜吧,数组公式可以同时进行多个计算并返回一种或多种结果.数组公式对两组或多组被称为数组参数的数值进行运算.每个数组参数必须有相同数量的行和列,而且数组公式是必须用 Ctrl+Shift+Enter 生成公式.
2、有条件加总：
这个解释起来有点麻烦,慢慢来
●COLUMN(H3:BP3)是取H3:BP3的列数,例如H3就是8
●MOD(COLUMN(H3:BP3)-1,2)就是把列数减1后对2取余,例如H3算出来就是1（8-1对2取余）,那么也就是说如果单元格是偶数列,则结果为1
●IF(MOD(COLUMN(H3:BP3)-1,2)=0,H3:BP3)就是如果单元格是奇数列（MOD(COLUMN(H3:BP3)-1,2)=0是条件,可参见上面）,那么就取单元格中的内容,否则为0
●SUM(IF(MOD(COLUMN(H3:BP3)-1,2)=0,H3:BP3)) 那么就是加总H3:BP3中奇数单元格中的数.
比如从H3开始到K3分别是1,2,2,4,其它都为空或0,那么上述公式计算结果为6（即I3（第9列）的2加K3（第11列）的4）
希望我的解释够明白,有什么问题再问吧.
补充一：
1、已经在我的回答中一步步解释了呀,你看SUM的东西是IF而不是H3:BP3,而IF呢又是又是判断H3:BP3的列号减1余2是1还是0,余2为0的话就取单元格的内容,否则为0,那么当然就是SUM列号为奇数的单元格的数啦
2、假设公式为=IF(A1=0,11),那么就是说A1单元格为0的话,公式结果为11,否则是FALSE,但如果对FALSE进行加减运算的话,EXCEL视之为0

zhuwei520

zhuwei520 发表于 2020-4-11 13:03
第一个问题举例：{SUM(IF(MOD(COLUMN(M4：FD4）,4）=3,M4：FD4））｝表达的什么意思

回答:

要求公式拖动后，公式引用的数据不受跨越列的影响
没能解决，

zhuwei520

粘贴时如何跳过隐藏列？

复制粘贴到单元格时，如果

1. 复制的内容有隐藏列怎么办？

2. 粘贴的区域有隐藏列怎么办？

我用的版本是 Excel 2016，其他版本的界面可能略有不同。

必杀技：

如果条件允许，强烈推荐安装一个叫“方方格子”（FFCell）的插件。这是个三方平台开发的插件，强大到什么程度？需要好几步解决的问题，几乎都能点插件实现。

如果没条件安装的，就看本文吧。

1. 怎么复制有隐藏列的内容？

比如下表，红色箭头部分有若干行隐藏，现在需要把选中的区域复制到右边表格，怎么做？

解决方法：

1. 选择需要复制的区域 --> Alt + ; --> 复制 --> 粘贴到右边

“Alt + ;”表示只选中可见区域

2. 怎么将内容粘贴到有隐藏列的区域？

比如下表反过来操作，把右边的数据复制粘贴到左边有隐藏行的区域。

解决方法：

1. 在 D 列增加一个辅助列，选中 D14 至 D29 --> Alt + ; --> 在 D14 中输入任意内容，比如 a --> Ctrl + Enter

2. 取消隐藏，现在可以看到，需要粘贴的区域旁边的辅助列都加上了“a”

3. 选中 A 到 D 列 --> 按照“辅助列”排序

4. 现在只要将右边的数据复制粘贴到左边就可以了

5. 按照 A 列的序列号排序，就完成了

zhuwei520

excel vba编程 忽略隐藏单元格而进行公式拖动和复制

如果是百处理自动筛选后的数据，有示例：
Dim SX As Range, aR As Range, r As Range, bt As Integer '筛选区域变量
Sub test()
    Set SX = ActiveSheet.AutoFilter.Range.SpecialCells(xlCellTypeVisible)
    For Each aR In SX.Areas '逐个连续度区域
      For Each r In aR.Rows '逐行专
        If bt Then
          '处理自动筛选出的行，用r.Cells(1, 2)引用r行的第2列
        Else '跳过标题行属
          bt = 1
        End If
      Next
    Next
End Sub

zhuwei520

如何将excel中空格部分自动填充上一格数据

在整理EXCEL表格数据时，有时会需要将某一列中的下一行空格中的内容自动等于上一行的内容，以方便数据的整理与筛选。一个一个的复制粘贴实在太麻烦，现在向大家分享一个快捷的方法。

方法/步骤
第一步，选中需要填充空格内容的那一列。并按快捷键ctrl+G,此时会出现“定位条件”。
如何将excel中空格部分自动填充上一格数据
如何将excel中空格部分自动填充上一格数据
第二步，点击“定位条件”，选择“空值”，然后“确定”。EXCEL表中会自动定位到这一列的空白单元格。
如何将excel中空格部分自动填充上一格数据
如何将excel中空格部分自动填充上一格数据
第三步，此时要注意鼠标不要点击EXCEL表中其他的地方，否则就需要重新定位空白单元格。定位后第一个空白单元格会处于编辑状态，直接输入公式“=A2”切记此时不能立刻按“回车键”，否则会自动跳到下一行的单元格。
如何将excel中空格部分自动填充上一格数据
第四步：输入公式后按“ctrl+回车”便可完成。“ctrl+回车”代表公式复制的意思。
如何将excel中空格部分自动填充上一格数据
第五步：记得重新复制这一列的内容，然后单击右键进行”选择性粘贴“，留下数值，去掉公式。
如何将excel中空格部分自动填充上一格数据
END
注意事项：
EXCEL中必须是数值格式方可使用上述方法，文本格式下只会带出公式“=A2”而不会带出A2单元格中的内容。

zhuwei520

关于数据透视表的五种排序

之前筱凰老师在教基础学员制作简单数据透视表的时候，很多人就开始说，这个这么简单我都会，其实难的是排序。我都想说既然这么简单，那为什么你就不会排序呢？

所以今天小编就来和大家说一说，在excel中关于数据透视表排序的那些事，和大家分享一下关于排序的5种方法。

第一种：自动排序

首先我们需要通过数据源来制作一个数据透视表，方法简单不会的可以翻看前面的文章，这里就不仔细提了。

我们将“班级”放在行标签进行筛选，而“总分”放在“数值”进行求和，这里我们一个简单的数据透视表就制作完成了。

我们制作完成后可以看到，数据的排序是杂乱的，如果需要自动排序，在“数据透视表工具”选项卡下的“选项”标签下找到“排序和筛选”功能组。我们将光标定位于数据中，选择升序或者降序的按钮即可。

第二种：手动排序

当自动排序满足不了我们的需求时，我们就需要使用手动排序。但还是有很多人在想：自动排序多方便呀，基本也能满足需求了，什么时候需要使用手动排序呢？

我们可以试一试，当你的筛选字段为年级时，无论按升序还是降序都无法满足我们的需求，它的排序基本都是错误的，这时候我们就需要选中它，手动拖曳到想要的位置即可。

为什么会出现这样的情况，是因为我们的excel中汉字的排序是按照字母顺序排列的，所以我们得不到自己想要的排序。

第三种：笔画排序

既然我们的汉字是按照字母进行排序的，那我们可不可以将其更改为按照笔画顺序进行排序？这样就避免我们去使用手动排序，这当然是可以的。

我们找到“数据透视表工具”选项卡的“选项”选项卡下的“排序”功能按钮。

在“排序”对话框中选择“其它选项”按钮，取消勾选“每次更新报表时自动排序”单选框，选择“方法”中的“笔划排序”。

第四种：横向排序

如果你的排序想要选择方向，那么你就必须知道横向排序应该怎么设置。

将光标定位于数据表内，选择“排序”功能按钮，在“按值排序”对话框中选择“排序方向”，横向就是“从左到右”，“从上到下”是默认状态。

第五种：自定义排序

如果上述的方式都无法满足你的需求，那么你可以像在Word一样自定义排序。

首先选择“文件”选项卡，选择“选项”菜单栏后选择“高级”选项，滑动右边页面到最后，点击“编辑自定义列表”按钮。

我们可以输入自己想要的排序名称，也可以导入单元格区域，点击“添加”即可。

我们将光标定位于数据表内，选择“排序”按钮后，像上述操作一样。选择“其他选项”按钮，取消勾选“每次更新报表时自动排序”单选框，在“主关键字排序次序”中选择我们定义的排序方式。

这样我们就可以选择刚才我们定义好的文字进行排序，效果如下：

就不用前面那些排序方式，一次性解决所有问题，大家下来可以试一试喔！

zhuwei520

工地常用Excel函数公式大全，算量算得飞起

一、数字处理

1、取绝对值

=ABS(数字)

2、取整

=INT(数字)

3、四舍五入

=ROUND(数字,小数位数)

二、判断公式

1、把公式产生的错误值显示为空

公式：C2

=IFERROR(A2/B2,"")

说明：如果是错误值则显示为空，否则正常显示。

2、IF多条件判断返回值

公式：C2

=IF(AND(A2<500,B2="未到期"),"补款","")

说明：两个条件同时成立用AND,任一个成立用OR函数。

三、统计公式

1、统计两个表格重复的内容

公式:B2

=COUNTIF(Sheet15!A:A,A2)

说明：如果返回值大于0说明在另一个表中存在，0则不存在。

2、统计不重复的总人数

公式：C2

=SUMPRODUCT(1/COUNTIF(A2:A8,A2:A8))

说明:用COUNTIF统计出每人的出现次数，用1除的方式把出现次数变成分母，然后相加。

四、求和公式

1、隔列求和

公式：H3

=SUMIF($A$2:$G$2,H$2,A3:G3)

或

=SUMPRODUCT((MOD(COLUMN(B3:G3),2)=0)*B3:G3)

说明：如果标题行没有规则用第2个公式

2、单条件求和

公式：F2

=SUMIF(A:A,E2,C:C)

说明：SUMIF函数的基本用法

3、单条件模糊求和

公式：详见下图

说明：如果需要进行模糊求和，就需要掌握通配符的使用，其中星号是表示任意多个字符，如"*A*"就表示a前和后有任意多个字符，即包含A。

4、多条件模糊求和

公式：C11

=SUMIFS(C2:C7,A2:A7,A11&"*",B2:B7,B11)

说明：在sumifs中可以使用通配符*

5、多表相同位置求和

公式：b2

=SUM(Sheet1:Sheet19!B2)

说明：在表中间删除或添加表后，公式结果会自动更新。

6、按日期和产品求和

公式：F2

=SUMPRODUCT((MONTH($A$2:$A$25)=F$1)*($B$2:$B$25=$E2)*$C$2:$C$25)

说明：SUMPRODUCT可以完成多条件求和

五、查找与引用公式

1、单条件查找公式

公式1：C11

=VLOOKUP(B11,B3:F7,4,FALSE)

说明：查找是VLOOKUP最擅长的，基本用法

2、双向查找公式

公式：

=INDEX(C3:H7,MATCH(B10,B3:B7,0),MATCH(C10,C2:H2,0))

说明：利用MATCH函数查找位置，用INDEX函数取值

3、查找最后一条符合条件的记录。

公式：详见下图

说明：0/(条件)可以把不符合条件的变成错误值，而lookup可以忽略错误值

4、多条件查找

公式:详见下图

说明:公式原理同上一个公式

5、指定区域最后一个非空值查找

公式;详见下图

说明：略

6、按数字区域间取对应的值

公式：详见下图

公式说明：VLOOKUP和LOOKUP函数都可以按区间取值，一定要注意，销售量列的数字一定要升序排列。

六、字符串处理公式

1、多单元格字符串合并

公式：c2

=PHONETIC(A2:A7)

说明：Phonetic函数只能对字符型内容合并，数字不可以。

2、截取除后3位之外的部分

公式：

=LEFT(D1,LEN(D1)-3)

说明：LEN计算出总长度,LEFT从左边截总长度-3个

3、截取-前的部分

公式:B2

=Left(A1,FIND("-",A1)-1)

说明：用FIND函数查找位置，用LEFT截取。

4、截取字符串中任一段的公式

公式:B1

=TRIM(MID(SUBSTITUTE($A1," ",REPT(" ",20)),20,20))

说明:公式是利用强插N个空字符的方式进行截取

5、字符串查找

公式：B2

=IF(COUNT(FIND("河南",A2))=0,"否","是")

说明: FIND查找成功，返回字符的位置，否则返回错误值，而COUNT可以统计出数字的个数，这里可以用来判断查找是否成功。

6、字符串查找一对多

公式：B2

=IF(COUNT(FIND({"辽宁","黑龙江","吉林"},A2))=0,"其他","东北")

说明：设置FIND第一个参数为常量数组，用COUNT函数统计FIND查找结果

七、日期计算公式

1、两日期相隔的年、月、天数计算

A1是开始日期（2011-12-1），B1是结束日期(2013-6-10)。计算：

相隔多少天？=datedif(A1,B1,"d") 结果：557

相隔多少月? =datedif(A1,B1,"m") 结果：18

相隔多少年? =datedif(A1,B1,"Y") 结果：1

不考虑年相隔多少月？=datedif(A1,B1,"Ym") 结果：6

不考虑年相隔多少天？=datedif(A1,B1,"YD")  结果：192

不考虑年月相隔多少天？=datedif(A1,B1,"MD") 结果：9

datedif函数第3个参数说明：

"Y" 时间段中的整年数。

"M" 时间段中的整月数。

"D" 时间段中的天数。

"MD" 天数的差。忽略日期中的月和年。

"YM" 月数的差。忽略日期中的日和年。

"YD" 天数的差。忽略日期中的年。

2、扣除周末天数的工作日天数

公式：C2

=NETWORKDAYS.INTL(IF(B2

zhuwei520

原来取余函数还能这么用

在我们熟知的数学概念中，余数就是两个数相整除之后剩余的数值，它有个很显著的特征，就是余数必定比除数要小。比如，5除以3，剩余2。而在Excel中，取余数的函数是MOD。

下面直接看看MOD函数的效果，如下：

[color=rgb(187, 187, 187) !important]MOD函数取余

l如果拟求19除以7的余数，用MOD函数直接得到结果为5；

l如果刚好被除数是除数的整数倍，像上面的10除以5，那么得到余数结果是0。

如果你觉得MOD函数只是作为取余数的函数，那就把它想得太简单了，这个函数的作用和应用还是很广的。

下面举2个经典的例子来说明一下。

1、利用MOD函数判断数的奇偶性

我们知道，一个正数，当它除以2时，它的余数必定是0或1。正是因为有这个特点，所以我们可以用MOD函数先对某个数除以2求余数：

l如果结果是0，则该数是偶数；

l如果结果是1，则该数是奇数。

如下面，判断21的奇偶性，公式为：=IF(MOD(21,2)=1,”奇数”,”偶数”)。

这里，我们也借助了IF条件函数进行判断。

2、利用MOD函数各行取数

下面这张表，是一张调查问卷的表，从表中的规律，我们可以看出，在第12行开始需要对上面各个类别的各个项目做汇总。当然，如果我们把各个单元格按照相同的项目进行相加，得到的结果也是正确的。如，C12中可以这样写：=C3+C6+C9。但是，如果是有很多很多行呢，也这样写的话，那必然效率是非常低下，而且很容易出错。

从表中我们看出，由于是每隔3行取对应的数进行相加，那么，我们可以利用先对行数除以3求余，把对应的行找出来，再对该区域做汇总求和。

如C12单元格的汇总公式为：

=SUMPRODUCT((MOD(ROW($B$3:$B$11),3)=0)*C$3:C$11)

公式中利用MOD函数对B3：B11的行号进行求余，结果为0的行便是目标行，最后再用SUMPRODUCT对该区域进行汇总求和。

[color=rgb(187, 187, 187) !important]MOD函数隔行取数

相对的，MOD函数还能隔列取数，逻辑一样，就不再赘述。

是不是很巧妙呢，想不到MOD求余函数还能这么用，你学会了吗？

每天5分钟，每天学一点。

老马将持续为您分享Excel的小技巧，请您关注。

zhuwei520

从百元到一亿，你只需要五分钟                               

转载自祝洪中老师

http://985.so/psGE

                                                                                                                                                                                                                                                                                                                                

                                                                                                
                                                                                                   

小伙伴们好啊，使用自定义格式能够改变单元格中数值的显示效果，今天老祝就和大家分享一组关于金额显示的技巧。

百  元

自定义格式代码：0!.0,%

光标放到％前 按Ctrl+J 最后设置单元格自动换行。

在自定义格式代码中，#和0都是显示单元格中原有内容，不同之处是如果使用0，能够在数值位数不够时用0来占位。例如12.52，使用自定义代码0.000 会显示成12.520，如果自定义代码是#.###，则仍显示12.52。

%的作用相当于将原数值放大100倍。

逗号是千位符，即原数值缩小1000倍。

感叹号的作用是强制显示后面一位数值。

本例代码的意思是，将数值缩小1000倍，再放大100倍，这样就相当于是缩小了10倍，然后在最后一位数值前强制显示小数点，最终形成百元显示效果。

千  元

自定义格式代码：0,千元

本例的代码比较好理解，就是原数值缩小1000倍。

整数万元

自定义格式代码： 0,,%

将光标放到％前，按Ctrl+J 最后再设置单元格对齐方式为自动换行。

代码中有两个逗号，意思是将数值缩小1000*1000，即100万倍，再加上％放大100倍，最终缩小10000倍，显示出万元效果。

两位小数的万元

自定义格式代码： 0.00,,万%

光标放到％前 按Ctrl+J 最后设置单元格自动换行

带千位分隔的万元

自定义格式代码： #,###,,.00%

光标放到％前 按Ctrl+J 最后设置自动换行

十万元

自定义格式代码：0!.00,

百万元

自定义格式代码：0.00,,

千万元

自定义格式代码：0,,,.00%

光标放到％前 按Ctrl+J 最后设置单元格自动换行

亿 元

自定义格式代码：0!.00,,亿元

好了，今天的内容就是这样，祝各位一天好心情！

图文制作：祝洪忠