excel实现域名提取 + 分类计数 + 排序公司app有个用户反馈功能，可以针对不同的功能（如视频下载等）向后台反馈评

公司app有个用户反馈功能，可以针对不同的功能（如视频下载等）向后台反馈评论和星数评级，同时会上报用户正在访问的url、包名等信息。

需求是这样的，要把针对视频下载的反馈数据，从数据库中导出来（xmls类型），分析出一级域名，统计不同域名下反馈的次数，并从高到低排序。

任务并不算复杂，但对于excel小白的我，也着实是费了些时间。

首先把任务拆分，共3个步骤：

url提取一级域名
按照域名计数汇总
降序排序

补充说一下，导出来的xmls数据结构类似这样：

	A	B	C	D
1	包名	url	星数	日期
2	包名	url	星数	日期
3	包名	url	星数	日期
4	包名	url	星数	日期

公式中会用到几个函数，需要提前了解一下。

用到的函数

MID( text, startIndex, number )

在text中截取截取一段字符，从startIndex开始，截取number个

text可以是字符串（如"abc"），也可以是单元格（如D2），表示取单元格（D2）中的文本

startIndex，索引是从1开始的，不是0

举个栗子：

	B
3	abcdefg

MID( B3, 2, 3) 获取到：bcd

COUNT 计数

COUNTIF( 范围, 条件 )

如 COUNTIF( B1:B9, ">=80") 在B1到B9范围里，找到成绩大于等于80的学生个数。

COUNTIFS( 范围1, 条件1, 范围2, 条件2 ... )

如：COUNTIFS( D1:D12, "男", C1:C12, ">=90")，找到12个学生中，成绩大于90分的男生个数。

IF( 条件, 符合条件的返回值, 不符合条件的返回值 )

如：IF( B4 > 60, "合格", "不合格")，成绩大于60分合格，否则不合格。

LEFT( text, number )

text中从左到右提取number个字符

如LEFT( A1, 3 )，从A1中左边开始提取3个字符

SUBSTITUTE( text, old-text, new-text, number )

text中的old-text替换成new-text，共替换number词

FIND( string, text, startIndex )

text中，从startIndex开始找string的索引。startIndex缺省1

	A
1	shengxia

FIND( "e", A1, 1) => 3

SEARCH( string, text, startIndex )

和search基本一样，忽略大小写，可以使用*等特殊字符。

url提取一级域名

这里分两步做，首先提取域名，之后提取一级域名

提取域名

如:https://abc.def.com/pathname/filename.html，提取abc.def.com

所用公式：

=MID(
  C4:C7295, 

  SEARCH("/", C4:C7295, SEARCH("/", C4:C7295 )+1)+1,

  SEARCH(
    "/", 
    C4:C7295 & "/", SEARCH("/",  C4:C7295, SEARCH("/",  C4:C7295, SEARCH("/", C4:C7295)+1))+1
  )-
  SEARCH("/", C4:C7295, SEARCH("/", C4:C7295)+1)
  -1
)

这里"C4:C7295"根据实际url所在列行填写

公式讲解：

/* 提取 */
=MID(

  /* 在C单元格中提取 */
  C4:C7295, 

  /* 从https?://后面开始 */
  SEARCH("/", C4:C7295, SEARCH("/", C4:C7295 )+1)+1,
//                      |<--- 第1个/索引 --->|
//|<---------------- 第2个/索引 ---------------->|

  /* https?://到下一个/之间长度，提取这么多 */
  SEARCH("/", C4:C7295 & "/", // 4.第3个/索引
    SEARCH("/",  C4:C7295, // 3.第2个/后面
      SEARCH("/",  C4:C7295, // 2.第2个/
        SEARCH("/", C4:C7295)+1 // 1.第1个/后面
      )
    )+1
  )-
  SEARCH("/", C4:C7295, SEARCH("/", C4:C7295)+1) //第2个/索引
  -1
)

实际操作

选中J4:J7295
上方公式区域输入公式
ctrl + shift + enter 生成数据

提取一级域名

如：abc.def.com，提取def;

abc.com，提取abc;

abc.def.ghi.jkl，全部提取

所用公式

=IF(
  LEN( J4:J7295 )-LEN(SUBSTITUTE( J4:J7295,".",""))=1,

  MID(
    J4:J7295,
    1,
    FIND(".", J4:J7295) - 1
  ),

  IF(
    LEN( J4:J7295 )-LEN(SUBSTITUTE( J4:J7295,".",""))=2,

    MID(
      J4:J7295,
      FIND(".", J4:J7295) + 1,
      FIND(".", J4:J7295, (FIND(".", J4:J7295)+1)) - FIND(".", J4:J7295) -1
    ),

    J4:J7295
  )
)

公式讲解

=IF(
  /* 如果只有1个. */
  LEN( J4:J7295 )-LEN(SUBSTITUTE( J4:J7295,".",""))=1,
//|<-- 总长度-->|  |<------ 去掉.后的总长度 ------->|

  /* 提取.前面的部分 */
  MID( // 提取
    J4:J7295, // abc.com
    1, // 从第1个开始
    FIND(".", J4:J7295) - 1 // .的索引-1个
  ),

  IF(
    /* 如果有2个. */
    LEN( J4:J7295 )-LEN(SUBSTITUTE( J4:J7295,".",""))=2,

    /* 提取2个点之间的 */
    MID(
      J4:J7295,
      FIND(".", J4:J7295) + 1,
      FIND(".", J4:J7295, (FIND(".", J4:J7295)+1)) - FIND(".", J4:J7295) -1
    ),

    /* 多于2个点，全部提取 */
    J4:J7295
  )
)

实际操作

选中K4:K7295
公式区域输入公式
ctrl + shift +enter

按照域名计数汇总

选中L4:L7295，公式区域输入1，ctrl+shift+enter批量导入数量
新建一张表，K域名列和L数量列分别复制 > 按值粘贴到新表
新表中创建数据透视表

全部选中
插入 > 数据透视表，放置到新工作表，确定
“域名”拖入到“行标签”，“数量”拖入到“数值”

参考

降序排序

点击数据透视表中，行标签右边的小三角 > 其他排序选项 > 降序（按数值）