精华内容
下载资源
问答
  • Excel表格有时候需要自定义格式,因为生成的都是数字格式,无法进行一些操作。 自定义了格式之后,通过DATEDIF是无法计算的。 因为我们自定义的- , 没有被excel识别,函数无法执行。 解决方法: 通过 TEXT 函数将...

    Excel表格有时候需要自定义格式,因为生成的都是数字格式,无法进行一些操作。
    在这里插入图片描述

    自定义了格式之后,通过DATEDIF是无法计算的。

    因为我们自定义的- , 没有被excel识别,函数无法执行。
    在这里插入图片描述

    解决方法:

    通过 TEXT 函数将数值转化为文本格式

    在这里插入图片描述

    规则如下:

    =DATEDIF(TEXT(A1,"#-00-00"),TEXT(B1,"#-00-00"),“Y”)

    在这里插入图片描述

    展开全文
  • 高级是因为利用 Excel 单元格的自定义格式我们可以实现一些看起来非常神奇和有用的效果。下面我们就由浅入深的来介绍一下 Excel 单元格自定义格式的知识和技巧。 1、"G/通用格式" 以常规的数字显示,相当于"分类...

    第一部分

    Excel 中的单元格格式是一个最基本但是又很高级的技能,说它基本是因为我们几乎天天都会用到它,会用它来设置一些简单的格式,比如日期,文本等等;高级是因为利用 Excel 单元格的自定义格式我们可以实现一些看起来非常神奇和有用的效果。下面我们就由浅入深的来介绍一下 Excel 单元格自定义格式的知识和技巧。

    1"G/通用格式"

    以常规的数字显示,相当于"分类"列表中的"常规"选项。

    代码:G/通用格式。10 显示为 10;10.1 显示为 10.1

    clip_image002

    2 "#":数字占位符

    只显有意义的零而不显示无意义的零。小数点后数字如大于"#"的数量,则按"#"的位数四舍五入。

    代码:###.##。12.1 显示为 12.1,  12.1263 显示为 12.13

    clip_image004

    3"0":数字占位符

    如果单元格的内容大于占位符,则显示实际数字,如果小于点位符的数量,则用0补足。

    例如代码:00000。1234567 显示为 1234567, 123 显示为 00123

    clip_image006

    代码:00.000。100.14 显示为 100.140, 1.1 显示为 01.100

    clip_image008

    4"@":文本占位符

    如果只使用单个@, 作用是引用原始文本, 要在输入数字数据之后自动添加文本, 使用自定义格式为:"文本内容"@;要在输入数字数据之前自动添加文本, 使用自定义格式为:@"文本内容"。@符号的位置决定了Excel输入的数字数据相对于添加文本的位置。如果使用多个@, 则可以重复文本。

    代码:  ;;;"集团"@"部"。 财务 显示为 集团财务部

    clip_image010

    代码: ;;;@@@。 财务 显示为 财务财务财务

    clip_image012

    5"*":重复下一次字符,直到充满列宽

    代码:@*-。ABC 显示为 ABC-------------------

    clip_image014

    可就用于仿真密码保护:代码 **;**;**;** 。123 显示为 ************

    clip_image016

    6",":千位分隔符

    代码 #,### 。12000 显示为 12,000;

    clip_image018“,”后省略000或###会省略千位符后面的数字,连续省略两个千位符,会省略百万位数之后的数字。如代码0,,百万,1000000显示为1百万

    clip_image020

    7"\":  用文本格式显示接下来的字符

    "文本":显示双引号里面的文本;"\":用文本格式显示接下来的字符。和 "" 用途相同都是显示输入的文本, 且输入后会自动转变为双引号表达。可用空格打断。

    代码 "人民币"#,##0,,"百万" 与 \人民币 #,##0,,\百万。输入 1234567890 显示为 人民币 1,235百万

    clip_image022

    8"?":数字占位符

    在小数点两边为无意义的零添加空格,以便当按固定宽度时,小数点可对齐,另外还用于对不等到长数字的分数

    代码: ???.???。输入 12.1212 显示 12.121; 输入 12.12 显示 12.12

    clip_image024

    9、颜色:用指定的颜色显示字符

    可有八种颜色可选:红色、黑色、黄色、绿色、白色、蓝色、青色和洋红。

    代码:[蓝色];[红色];[黄色];[绿色]。显示结果为正数为蓝色, 负数显示红色, 零显示黄色, 文本则显示为绿色

    clip_image026

    [颜色N]:是调用调色板中颜色, N 是 0~56 之间的整数。

    代码:[颜色1];[颜色2];[颜色3];[颜色4]。显示结果为正数为调色板上第1种颜色, 负数为调色板上第2种颜色, 零为调色板上第3种颜色, 文本为调色板上第4种颜色。

    clip_image028

    10、条件:可以单元格内容判断后再设置格式

    条件格式化只限于使用三个条件, 其中两个条件是明确的, 另个是"所有的其他"。条件要放到方括号中。必须进行简单的比较。

    代码: [>5]"大于五";[<-5]"小于负五";"零"。输入 12, -12, 1 显示为 大于五, 小于五, 零。

    clip_image030

    11 "!":显示 "

    由于引号是代码常用的符号。在单元格中是无法用 " 来显示出来 "。要想显示出来,须在前加入 !

    代码:#!"!"。10 显示 10""

    clip_image032

    12、时间和日期代码常用日期和时间代码

    "YYYY"或"YY":按四位(1900~9999)或两位(00~99)显示年

    "MM"或"M":以两位(01~12)或一位(1~12)表示月。

    "DD"或"D":以两位(01~31)或一位(1-31)来表示天。

    例:代码:YYYY-MM-DD。2014年8月18日 显示为 2014-08-18

    clip_image034

    第一部分总结:

    ü 数字占位符:

    ? 用空格补足未有的数字 ???.???

    0 用0补足格式 00.000

    # 不产生实际格式没有的数字 ##.###

    ü 特殊记忆:

    ;;;隐藏

    如;;;@@@,隐藏原文本,显示三个原文本

    如;;;**,隐藏原文本,用*重复至填完表格(用于仿真密码跋扈)

    第二部分

    一、另外日期格式的显示方法

    E: 显示四位年份,2003版本有效。

    BBBB: 显示四位佛历年份,即以公元前543年为纪年元年,对1900年以后的日期有效。

    MMM: 显示英文月份的简称。

    MMMM: 显示英文月份的全称。

    DDD: 显示英文星期几的简称。

    DDDD: 显示英文星期几的全称。

    Monday DDDD;Mon DDD;November MMM;Nov MMM

    二、关于特殊数字的显示

    1、中文小写数字: [DBNum1][$-804]G/通用格式

    代码:[DBNum1][$-804]G/通用格式。13 显示为 一十三

    2、中文大写数字:  [DBNum2][$-804]G/通用格式

    代码:[DBNum2][$-804]G/通用格式。13 显示为 壹拾叁

    3、中文小写数字 [DBNum3][$-804]G/通用格式

    代码:[DBNum3][$-804]G/通用格式。13 显示为 1十3

    clip_image036

    三、在自定义数字格式中使用颜色

    要设置格式中某一部分的颜色,只要在该部分对应位置用方括号键入颜色名称或颜色编号即可。Excel中可以使用的颜色名称有 [黑色]、[蓝色]、[青色]、[绿色]、[洋红]、[红色]、[白色]、[黄色] 八种不同的颜色,此外Excel还可以使用[颜色X]的方式来设置颜色,其中X为1-56之间的数字,代表了56种不同的颜色。

    例如:当用户需要将单元格中的负数数字用蓝色来表示,只要使用 #,##0.00;[蓝色]-#,##0.00自定义数字格式,用户在单元格中录入负数时,Excel就会将数字以蓝色显示。

    clip_image038

    四、在自定义数字格式中使用条件格式

    在Excel自定义数字格式中用户可以进行条件格式的设置。当单元格中数字满足指定的条件时,Excel可以自动将条件格式应用于单元格。Excel自定义数字格式中可以使用如下六种标准的比较运算符:=、> 、<、>= 、<= 、<>,分别表示等于、大于、小于、大于等于、小于等于和不等于

    例如:在学生成绩工作表中,当我们想以红色字体显示大于等于90分的成绩,以蓝色字体显示小于60分的成绩时,其余的成绩则以黑色字体显示,这时只需将自定义数字格式设置为 [红色][>=90];[蓝色][<60];[黑色] 即可。

    值得注意的是,当你在以后需要继续使用刚才所创建的成绩条件自定义数字格式时,你会发现在“单元格格式”的“自定义”分类类型中找不到 [红色][>=90];[蓝色][<60];[黑色] 格式,这是因为Excel自动将你所创建的 [红色][>=90];[蓝色][<60] 格式修改成 [红色][>=90]G/通用格式;[蓝色][<60]G/通用格式;[黑色]G/通用格式,你只需选择此格式即可达到同样的使用效果。

    实例:在工人奖金列表中,当我们想以小于50的显示“低”,50至100间显示“中等”,大于100的显示“高”。这时只需将自定义数字格式设置为代码:[<50]"低";[>100]"高";"中等"

    clip_image040

    五、隐藏单元格中的数值

    在Excel工作表中,有时为了表格的美观或者别的因素,我们希望将单元格中的数值隐藏起来,这时我们使用 ;;; (三个分号)的自定义数字格式就可达到此目的。这样单元格中的值只会在编辑栏出现,并且被隐藏单元格中的数值还不会被打印出来,但是该单元格中的数值可以被其他单元格正常引用。

    clip_image042

    特殊说明

    因为参数的特殊性,所以自定义的参数也是有关键字的。如函数 =TEXT(A1,"b0000") 就会显示错误。因为 b 就是保留的关键字,在自定义格式输入 b 系统就会自动填入 bb 。bb 就是佛历年份,即以公元前543年为纪年元年,对1900年以后的日期有效。bbbb 就是四位佛历年份。要解决 =TEXT(A1,"b0000") 的错误问题,需要这样定义函数 =TEXT(A1,"""b""0000")。在自定义格式中定义就是 "b"0000。其它的关键字自己体会如: d、e............

    转载于:https://www.cnblogs.com/shadrach/p/5510889.html

    展开全文
  • 高级是因为利用Excel单元格的自定义格式我们可以实现一些看起来非常神奇和有用的效果。 下面本文就由浅入深的来介绍一下 Excel单元格自定义格式的知识和技巧。 1、“G/通用格式” 以常规的数字显示,相当于"分类"列表...

    Excel单元格自定义格式的知识和技巧


    Excel中的单元格格式是一个最基本但是又很高级的技能,说它基本是因为几乎天天都会用到它,会用它来设置一些简单的格式,比如日期,文本等等;高级是因为利用Excel单元格的自定义格式我们可以实现一些看起来非常神奇和有用的效果。

    下面本文就由浅入深的来介绍一下 Excel单元格自定义格式的知识和技巧。

    格式说明

    1、“G/通用格式”

    以常规的数字显示,相当于"分类"列表中的"常规"选项。

    代码:G/通用格式。10 显示为 10;10.1 显示为 10.1
    在这里插入图片描述
    2、 “#”:数字占位符

    只显有意义的零而不显示无意义的零。小数点后数字如大于"#“的数量,则按”#"的位数四舍五入。

    代码:###.##。12.1 显示为 12.1, 12.1263 显示为 12.13
    在这里插入图片描述
    3、“0”:数字占位符

    如果单元格的内容大于占位符,则显示实际数字,如果小于点位符的数量,则用0补足。

    例如代码:00000。1234567 显示为 1234567, 123 显示为 00123

    Excel单元格自定义格式的知识和技巧

    代码:00.000。100.14 显示为 100.140, 1.1 显示为 01.100

    Excel单元格自定义格式的知识和技巧

    4、"@":文本占位符

    如果只使用单个@, 作用是引用原始文本, 要在输入数字数据之后自动添加文本, 使用自定义格式为:“文本内容”@;要在输入数字数据之前自动添加文本, 使用自定义格式为:@“文本内容”。@符号的位置决定了Excel输入的数字数据相对于添加文本的位置。如果使用多个@, 则可以重复文本。

    代码: ;;;“集团”@“部”。 财务 显示为 集团财务部

    Excel单元格自定义格式的知识和技巧

    代码: ;;;@@@。 财务 显示为 财务财务财务

    Excel单元格自定义格式的知识和技巧

    5、"*":重复下一次字符,直到充满列宽

    代码:@*-。ABC 显示为 ABC-------------------

    Excel单元格自定义格式的知识和技巧

    可就用于仿真密码保护:代码 。123 显示为 ************

    Excel单元格自定义格式的知识和技巧

    6、",":千位分隔符

    代码 #,### 。12000 显示为 12,000

    Excel单元格自定义格式的知识和技巧

    7、"": 用文本格式显示接下来的字符

    “文本”:显示双引号里面的文本;"":用文本格式显示接下来的字符。和 “” 用途相同都是显示输入的文本, 且输入后会自动转变为双引号表达。

    代码 “人民币”#,##0,“百万” 与 \人民币 #,##0,\百万。输入 1234567890 显示为 人民币 1,235百万

    Excel单元格自定义格式的知识和技巧

    8、"?":数字占位符

    在小数点两边为无意义的零添加空格,以便当按固定宽度时,小数点可对齐,另外还用于对不等到长数字的分数

    代码: ???.???。输入 12.1212 显示 12.121; 输入 12.12 显示 12.12

    Excel单元格自定义格式的知识和技巧

    9、颜色:用指定的颜色显示字符

    可有八种颜色可选:红色、黑色、黄色、绿色、白色、蓝色、青色和洋红。

    代码:[蓝色];[红色];[黄色];[绿色]

    显示结果为正数为蓝色, 负数显示红色, 零显示黄色, 文本则显示为绿色
    在这里插入图片描述

    [颜色N]:是调用调色板中颜色, N 是 0~56 之间的整数。

    代码:[颜色1];[颜色2];[颜色3];[颜色4]

    显示结果为正数为调色板上第1种颜色, 负数为调色板上第2种颜色, 零为调色板上第3种颜色, 文本为调色板上第4种颜色。

    Excel单元格自定义格式的知识和技巧

    10、条件:可以单元格内容判断后再设置格式

    条件格式化只限于使用三个条件, 其中两个条件是明确的, 另个是"所有的其他"。条件要放到方括号中。必须进行简单的比较。

    代码: [>5]“大于五”;[<-5]“小于负五”;“零”

    输入 12, -12, 1 显示为 大于五, 小于五, 零。

    Excel单元格自定义格式的知识和技巧

    11、 “!”:显示 "

    由于引号是代码常用的符号。在单元格中是无法用 " 来显示出来 "。要想显示出来,须在前加入!

    代码:#!"!"。10 显示 10""

    Excel单元格自定义格式的知识和技巧

    12、时间和日期代码常用日期和时间代码

    “YYYY"或"YY”:按四位(19009999)或两位(0099)显示年

    “MM"或"M”:以两位(0112)或一位(112)表示月。

    “DD"或"D”:以两位(01~31)或一位(1-31)来表示天。

    例:代码:YYYY-MM-DD

    2014年8月18日 显示为 2014-08-18

    Excel单元格自定义格式的知识和技巧

    示例

    判断单元格值大小,显示不同文字和颜色
    小于0.85蓝色,大于0.85红色,等于时正常黑色

    [蓝色][<0.85]0%"少了";[红色][>0.85]0%"超了";[黑色]"正好"0%
    
    展开全文
  • poi读取excel自定义时间类型时,读取到的是CELL_TYPE_NUMERIC,即数值类型,这个时候如果直接取值的话会发现取到的值和表格中的值不一样,这时应该先判断值是否是时间或者日期类型再进行处理,代码如下:private ...

    poi读取excel自定义时间类型时,读取到的是CELL_TYPE_NUMERIC,即数值类型,
    这个时候如果直接取值的话会发现取到的值和表格中的值不一样,
    这时应该先判断值是否是时间或者日期类型再进行处理,
    代码如下:
    private String parseExcel(Cell cell) {
    String result = new String();
    switch (cell.getCellType()) {
    case HSSFCell.CELL_TYPE_NUMERIC:// 数字类型
    if (HSSFDateUtil.isCellDateFormatted(cell)) {// 处理日期格式、时间格式
    SimpleDateFormat sdf = null;
    if (cell.getCellStyle().getDataFormat() == HSSFDataFormat
    .getBuiltinFormat("h:mm")) {
    sdf = new SimpleDateFormat("HH:mm");
    } else {// 日期
    sdf = new SimpleDateFormat("yyyy-MM-dd");
    }
    Date date = cell.getDateCellValue();
    result = sdf.format(date);
    } else if (cell.getCellStyle().getDataFormat() == 58) {
    // 处理自定义日期格式:m月d日(通过判断单元格的格式id解决,id的值是58)
    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
    double value = cell.getNumericCellValue();
    Date date = org.apache.poi.ss.usermodel.DateUtil
    .getJavaDate(value);
    result = sdf.format(date);
    } else {
    double value = cell.getNumericCellValue();
    CellStyle style = cell.getCellStyle();
    DecimalFormat format = new DecimalFormat();
    String temp = style.getDataFormatString();
    // 单元格设置成常规
    if (temp.equals("General")) {
    format.applyPattern("#");
    }
    result = format.format(value);
    }
    break;
    case HSSFCell.CELL_TYPE_STRING:// String类型
    result = cell.getRichStringCellValue().toString();
    break;
    case HSSFCell.CELL_TYPE_BLANK:
    result = "";
    default:
    result = "";
    break;
    }
    return result;
    }


    Excel数据处理:

    Excel存储日期、时间均以数值类型进行存储,读取时POI先判断是是否是数值类型,再进行判断转化

    1、数值格式(CELL_TYPE_NUMERIC):

    1.纯数值格式:getNumericCellValue() 直接获取数据

    2.日期格式:处理yyyy-MM-dd, d/m/yyyy h:mm, HH:mm 等不含文字的日期格式

    1).判断是否是日期格式:HSSFDateUtil.isCellDateFormatted(cell)

    2).判断是日期或者时间

    cell.getCellStyle().getDataFormat() == HSSFDataFormat.getBuiltinFormat("h:mm")

    OR: cell.getCellStyle().getDataFormat() == HSSFDataFormat.getBuiltinFormat("yyyy-MM-dd")

    3.自定义日期格式:处理yyyy年m月d日,h时mm分,yyyy年m月等含文字的日期格式

    判断cell.getCellStyle().getDataFormat()值,解析数值格式

    yyyy年m月d日----->31

    m月d日---->58

    h时mm分--->32


    *万能处理方案:

    所有日期格式都可以通过getDataFormat()值来判断

    yyyy-MM-dd----- 14

    yyyy年m月d日--- 31

    yyyy年m月------- 57

    m月d日 ---------- 58

    HH:mm----------- 20

    h时mm分 ------- 32


    //1、判断是否是数值格式
    if(cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC){
    short format = cell.getCellStyle().getDataFormat();
    SimpleDateFormat sdf = null;
    if(format == 14 || format == 31 || format == 57 || format == 58){
    //日期
    sdf = new SimpleDateFormat("yyyy-MM-dd");
    }else if (format == 20 || format == 32) {
    //时间
    sdf = new SimpleDateFormat("HH:mm");
    }
    double value = cell.getNumericCellValue();
    Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(value);
    result = sdf.format(date);
    }

    引自
    https://www.cnblogs.com/azhqiang/p/4260596.html
    这哥们写得好,我在网上搜到他写的这篇文,然后自己复制了一下记下来了

    转载于:https://www.cnblogs.com/xkjy/p/11088404.html

    展开全文
  • 下面小编就为大家带来一篇POI对Excel自定义日期格式的读取(实例代码)。小编觉得挺不错的,现在就分享给大家,也给大家做个参考。一起跟随小编过来看看吧
  • POI对Excel自定义日期格式的读取 学习了:http://yl-fighting.iteye.com/blog/1726285 cell.getCellStyle().getDataFormat();根据这个值进行时间、日期格式的判断;POI读取出来的结果也是有些变化的;需要在实际...
  • 原文:http://coolketang.com/staticOffice/5a97f2f7ee920a788082fe5d.html1. 本节课将为您演示,日期和时间的输入,以及如何自定义日期格式。首先点击选择当前工作表中的某个单元格。 2. 然后在单元格中输入一个...
  • 今天碰到一个问题,就是jxl读取日期类型数据时,数据类型变成了Number类型。 当然,还有一个类型为Date类型。 二者区别如下: 2014年12月25日 2014-12-25 现,相对以上两种日期类型同时做处理。代码如下: ...
  • 输入“01”1.单元格内右键“单元格格式”,“数字”内选择“文本”,“OK”。自定义日期格式1.单元格内右键“单元格格式”,“数字”内选择“自定义”,在右侧“类型”内进行自定义,如:yyyy-mm-dd。...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 642
精华内容 256
关键字:

excel日期自定义格式