精华内容
下载资源
问答
  • 转发本文并私信我"python",即可获得Python资料以及各种心得(持续更新的)系列文章:前言经常听别人说 ...上一篇文章从 Excel 筛选为核心,介绍 pandas 中的实现,但是,Excel 中还有一个高级筛选的功能,普通的筛...

    053Z363K-0.jpg

    转发本文并私信我"python",即可获得Python资料以及各种心得(持续更新的)

    系列文章:

    前言

    经常听别人说 Python 在数据领域有多厉害,结果学了很长时间,连数据处理都麻烦得要死。后来才发现,原来不是 Python 数据处理厉害,而是他有数据分析神器—— pandas 。

    上一篇文章从 Excel 筛选为核心,介绍 pandas 中的实现,但是,Excel 中还有一个高级筛选的功能,普通的筛选与其对比,就只能算是"低级筛选"功能了。接下来,通过简单到复杂的需求,看看这是怎么样的一个功能。

    数据

    本文示例数据如下:

    053Z34301-1.jpg某学校的一份考试成绩表(8科成绩)

    人名已做脱敏处理

    加载数据

    pandas 需要加载 Excel 数据,非常容易,如下:

    053Z34b6-2.jpg大部分参数上一篇文章已经有说明,这里说说重点

    由于工作表中有多余的列数据,我们只需要前10列,因此指定 usecols 参数。他接受一个列表。list(range(0,10)),其实相当于[0,1,2…………,9]的一个列表

    条件过滤

    先来一个超级简单例子,来看看怎么操作 Excel 的高级筛选。

    "姓名是 A1 的记录",Excel 高级筛选操作步骤如下:

    053Z31P0-3.jpg

    053Z34443-4.jpg功能区 "数据" 页,在"筛选大图标" 右下有一个 "高级" ,点击出来高级筛选功能窗口

    主要看上图2的红框,选择我们的数据源区域,记得要包含标题

    上图2的蓝框是条件区域,条件区域的选择如图

    点击确定,即可筛选出姓名 A1 的记录

    看看条件区域的设定:

    053Z32V9-5.jpg格式为,标题+条件值(上下单元格)

    标题必须与数据源对应的列一致,比如这里指定姓名列,所以条件区域的标题也是"姓名"

    条件值我们使用 ="=A1"如果只是指定文本值 A1 ,Excel 默认会把姓名前缀A1的记录筛选出来。

    pandas 中没有啥高级筛选的说法,因为他的筛选本来就很灵活,看看 pandas 的实现:

    053Z36259-6.jpg简单易懂,都是之前文章介绍过的,这里不多说

    053Z33213-7.jpg

    特定值过滤

    "4、5或7班的记录",Excel 高级筛选的条件区域设置如下:

    053Z3L54-8.jpg红框部分就是条件区域

    标题是"班级",这要与数据源保持一致

    条件值区域多行表示"或"关系,上图就是表示班级是4或5或7,任意一个符合的记录

    pandas 实现如下:

    053Z3A35-9.jpg同样使用 query 方法

    in [4,5,6] ,语义清晰,班级是在列表中即符合

    pandas 的 query 查询可以很灵活,可以接受外部的一个列表变量,如下:

    053Z31042-10.jpg查询字符串要使用外部变量,只需要写 "@+变量名字" 即可

    范围过滤

    "总分450至500之间的记录",Excel 高级筛选的条件区域设置如下:

    053Z3Ga-11.jpg数据源没有总分列,添加一个 sum 公式的总分列

    053Z35C7-12.jpg条件区域在同一行,表示"并且"关系

    条件值可以直接使用常用的比较符号

    还是要注意条件标题"总分"

    pandas 实现如下:

    053Z3KZ-13.jpg第一句,添加新列,总和列。pandas 新增列非常简单,df[新列名字]=新列值,即可

    df.loc[:,'语文':'生物'] ,是获取语文到生物之间的列的数据

    .sum(axis=1) ,横向求和。因为 pandas 可以灵活对行或列做运算,通过 axis 即可表达运算是对行还是列操作。

    第二句即查询,通俗易懂

    "语文高于90,或者,数学高于或等于100",Excel 高级筛选的条件区域设置如下:

    053Z341W-14.jpg

    pandas 实现如下:

    053Z3Cc-15.jpgquery 中的查询字符串可以使用 python 中的逻辑关键字 and 或 or 这些都可以

    053Z334T-16.jpg

    下篇预告

    本文从 Excel 高级筛选角度介绍简单的应用,下一篇将讲解更复杂的应用,先看看有哪些复杂需求:"总分高于全班平均分的学生",这需要每行记录与整体平均对比

    "总分高于所在班级平均分的学校" ,这是上一条的升级版

    "全级中,8科成绩都超出全级平均分的学生" ,每科成绩都要细致对比

    下篇将解决以上问题,敬请关注。

    转发本文并私信我"python",获取本案例的 Excel 数据与源码

    如果希望从零开始学习 pandas ,那么可以看看我的 pandas 专栏。

    展开全文
  • excel集成系统单机版 单击地图以筛选Excel数据 (Click a Map To Filter Excel Data)We'll set up an Excel workbook so you can click on a map, and see a list of fictional doctors in the state that you ...

    excel集成系统单机版

    单击地图以筛选Excel数据 (Click a Map To Filter Excel Data)

    We'll set up an Excel workbook so you can click on a map, and see a list of fictional doctors in the state that you selected.

    我们将设置一个Excel工作簿,以便您可以单击地图,并在您选择的状态下查看虚构医生的列表。

    MapUSA

    设置地图 (Set up the Map)

    First, I found a clip art map of the continental USA, with a separate shape for each state, and pasted that into a workbook.

    首先,我找到了美国大陆的剪贴画地图,每个州都有单独的形状,然后将其粘贴到工作簿中。

    Next, I named the shapes, using the two digit state abbreviation.

    接下来,我使用两位数字状态缩写来命名形状。

    • Click on a state shape, e.g. Washington

      单击状态形状,例如华盛顿
    • Click in the Name Box, and type the two digit code – WA

      单击名称框,然后输入两位数字代码– WA

    MapUSA_NameWA

    • Press the Enter key, to complete the naming.

      按Enter键完成命名。

    This will be a good test of your geography knowledge and state abbreviation skills.

    这将是对您的地理知​​识和州缩写技巧的很好测试。

    设置医生名单 (Set Up the List of Doctors)

    On a sheet named Doctors, I entered a list of doctor names, state codes and number of patients.

    在名为Doctors的工作表上,我输入了医生姓名,州代码和患者人数的列表。

    MapUSA_Docs

    I named the range, DoctorList, using the formula:

    我使用以下公式将范围命名为DoctorList

    =OFFSET(Doctors!$A$1,0,0,COUNTA(Doctors!$A:$A), COUNTA(Doctors!$1:$1))

    = OFFSET(医生!$ A $ 1,0,0,COUNTA(医生!$ A:$ A),COUNTA(医生!$ 1:$ 1))

    as described in my Dynamic Named Ranges tutorial.

    如我的“ 动态命名范围”教程中所述。

    设置过滤条件 (Set Up the Filter Criteria)

    On the Map sheet, I set up a criteria range for the filter, with the State heading in cell A1, and a State code in cell A2.

    在“映射”表上,我为过滤器设置了一个标准范围,“状态”标题位于单元格A1中,而“状态代码”位于单元格A2中。

    To name this range, I selected cells A1:A2 and typed Criteria in the Name box, then pressed the Enter key.

    要命名此范围,我选择了单元格A1:A2,并在“名称”框中键入了Criteria ,然后按Enter键。

    MapUSA_Crit

    设置过滤器提取 (Set Up the Filter Extract)

    Next, I copied the headings from the Doctor list, and pasted them below the map.

    接下来,我复制了Doctor列表中的标题,并将其粘贴到地图下方。

    With the pasted cells still selected, I named that range StateList.

    在粘贴的单元格仍处于选中状态的情况下,我将该范围命名为StateList

    This is where the list of doctors will appear when you click a state in the map.

    单击地图中的州时,将在此处显示医生列表。

    MapUSA_Extr

    添加过滤器宏 (Add the Filter Macro)

    I added a module to the workbook, and entered some code to use an Advanced Filter, to extract a list of doctors for the selected state.

    在工作簿中添加了一个模块 ,并输入了一些代码以使用Advanced Filter来提取所选状态的医生列表。

    Sub GetDoctorList()
    On Error Resume Next
    Dim strState As String
    Dim sh As Shape
    Dim wsMap As Worksheet
    Dim wsDoctor As Worksheet
    Dim wsList As Worksheet
    strState = Application.Caller
    Set wsMap = Worksheets("Map")
    Set wsDoctor = Worksheets("Doctors")
    wsMap.Range("A2").Value = strState
    wsDoctor.Range("DoctorList").AdvancedFilter _
      Action:=xlFilterCopy, _
      CriteriaRange:=wsMap.Range("Criteria"), _
      CopyToRange:=wsMap.Range("StateList"), Unique:=False
    wsMap.Activate
    wsMap.Range("A1").Activate
    End Sub
    

    将宏分配给形状 (Assign the Macro to Shapes)

    The final step is to assign the GetDoctorList macro to each shape. You could right-click on each shape, click Assign Macro, and select the macro, to do this manually.

    最后一步是将GetDoctorList宏分配给每个形状。 您可以右键单击每个形状,单击“分配宏”,然后选择宏以手动执行此操作。

    That might take quite a while for all 48 states, so I wrote a little macro, to do the work for me. You'll only have to run this once, while setting up the workbook.

    对于所有48个州来说,可能要花很长时间,所以我写了一个宏,为我完成工作。 设置工作簿时,只需运行一次。

    Sub AddMacro()
    Dim sh As Shape
    For Each sh In Worksheets("Map").Shapes
        sh.OnAction = "GetDoctorList"
    Next
    End Sub
    

    运行过滤器 (Run the Filter)

    Now, to run the filter, click on a state in the map.

    现在,要运行过滤器,请单击地图中的状态。

    The state code appears in cell A2, and the list of doctors is in the extract range, starting in cell B18.

    状态代码出现在单元格A2中,并且医生列表在提取范围内,从单元格B18开始。

    翻译自: https://contexturesblog.com/archives/2009/08/17/click-a-map-to-filter-excel-data/

    excel集成系统单机版

    展开全文
  • 后续更多干货资料,请转发、收藏、关注!!!前言经常听别人说 Python 数据领域有多厉害,结果学了很长时间...今天我们就用 pandas 看看怎么做到 Excel筛选功能,并且看看 Excel 也做不到的功能。数据本文示例数...

    后续更多干货资料,请转发、收藏、关注!!!

    55e736d12f2eb938f79b2ab054dab430e4dd6f3f.jpeg?token=d576c8b02ef2bcec6e36786b87733c6d&s=741AC83384C07EEA4B54D6E40300E021

    前言

    经常听别人说 Python 数据领域有多厉害,结果学了很长时间,连数据处理都麻烦得要死。后来才发现,原来不是 Python 数据处理厉害,而是他有数据分析神器—— pandas 。

    Excel 自带筛选功能,可以对表格中进行各种条件筛选。今天我们就用 pandas 看看怎么做到 Excel 的筛选功能,并且看看 Excel 也做不到的功能。

    数据

    本文示例数据如下:

    8326cffc1e178a82cd7a91c174bb4288a877e84f.jpeg?token=f0214e56b539d80ea5a5885a8eea0130&s=C9028C1A199DC8CA0E65A4DB0200C0B3

    数据都是用代码生成的,就是一些个人信息数据加载数据

    pandas需要加载Excel数据,非常容易,如下:

    d1160924ab18972b6b08451f66754a8c9f510a76.jpeg?token=f2f43402bda5f70deb25e8a0c741fc23&s=69E093424FEEBF700C4DBD0C0000E0C3

    import pandas as pd ,导入 pandas 包pd.read_excel ,即可加载 Excel 数据指定文件路径,由于文件在 Python 脚本同目录,直接输入文件名即可sheet_name 指定读取哪个工作表还有很多其他参数,我们这次的数据非常规范,因此不需要用到其他参数按位置过滤

    Excel的筛选只能根据列值进行操作,因此我们在表格添加一序号列。看图:

    2934349b033b5bb553741d76b56be43cb400bcd0.jpeg?token=68b89b6ecae8f6bac6270906450a042d&s=E800CD1A199548CA484DC4CA020010B1

    为了与 pandas 行索引保持一致,这里添加的列值是从0开始接着试试,"显示第3至6行",如下:

    faf2b2119313b07e716892a98f6fa02695dd8cfc.jpeg?token=72d9949ceac0af9b54ffc1b54b4357ca&s=E940CD1A09BFC8CE14508CC90300C0B3

    功能卡"数据"页面,在"排序和筛选"中点击大大的"筛选"图标

    4b90f603738da977e88d303a32e9c91c8718e3bb.jpeg?token=39c22d8a179c6f1957d4ad4c0e95f7aa&s=F018CC3A8185E0EA4C79D0CE0200D0B3

    点首行第一列的下角标签默认是全选了,点一下"全选",即可取消所有选中的分别点选对应的值即可看看pandas中如何做到,如下:

    ac4bd11373f0820255aa8a85ca43cae8aa641b88.jpeg?token=d5130caa9bbe699f2c4dd8eb75d20671&s=68E2336643F1907B0E49900B0000F0C3

    pandas 中的 DataFrame 自带行索引直接使用 df.loc[2:5]

    9e3df8dcd100baa1a482da8ec5a88817c9fc2e92.jpeg?token=1b7032f4cc8782afefebc85ccc1df576&s=ED268654481765CE063216D10300F0BC

    按值过滤

    位置过滤太简单了,很少场景使用。这次看看"显示所有男性"记录。

    Excel不介绍了,看看pandas怎么完成:

    359b033b5bb5c9eaaac5d046548187053bf3b39b.jpeg?token=8318f0567ad631ae21c464779f40e991&s=60E233665BA5824F4EC9B40F0000B0C1

    df['sex'] ,就是性别列df['sex']=='M' ,语义清晰,性别等于M多条件也不在话下,"显示男性并且血型是A+",如下:

    6159252dd42a2834eb23c2b4d90df8ef17cebfc2.jpeg?token=dad13654d0ba057278d7011772718caa

    当然,有更加简单的写法,如下:

    5bafa40f4bfbfbedfddfe9dafa48c633adc31fca.jpeg?token=c017371c0182e2654fc1c24d3961baac

    query 方法,可以直接接受一个查询字符串,是不是很像 Sql 呢指定多个值也很简单,"血型是A+或B-",如下:

    500fd9f9d72a605923ef436daa8c059e023bba03.jpeg?token=577ec2284213327380f465163be92873

    查询字符串可以直接用 in模式匹配

    太简单了?来个复杂一点的。"血型值首字母是A"的记录。Excel中的筛选也是强大的,直接有此功能。如下:

    f603918fa0ec08fa15961f0cdb560c6857fbdac0.jpeg?token=474956e409095649217ebea908655e75&s=C810AD1A150F50EA56D880CA030080B3

    0df431adcbef7609c7769ab4af6592c97dd99edd.jpeg?token=29455f1aba643178ec0c4295fcc4763c&s=2A8A7C22111A45C81E5DE5DB0000D0B3

    pandas对应操作如下:

    fc1f4134970a304e482403ab53709683c8175c9e.jpeg?token=0c150017fa44bb3216d0ad08751eab16

    血型 列是文本类型,因此可以用 .str ,从而使用一系列文本快捷方法当然,pandas中的文本处理功能比Excel强大得多,来看看。"住址内容包含天津市3字"的记录。如下:

    8d5494eef01f3a2928913e601b9d8d345d607c04.jpeg?token=c08d324978022de13f944d3514adbbf6

    什么,Excel也可以?来看看下面这个需求就不寻常了。"住址内容有x座,x是字母a至c,大小写都算",看看pandas怎么做:

    1e30e924b899a901531925559f2d3b7e0308f5e6.jpeg?token=3f81bdf5354563909d80927de53d0276&s=A1F263265BADB24D08C5D50B0000B0C1

    contains 方法可以用正则表达式

    7af40ad162d9f2d3370005552854bb166227ccb0.jpeg?token=7aa9333e1b6a75bad0880800e0ccbbfa&s=4CE1587E31A6611F99704048030070F2

    值范围

    "收入在5000至8000的男性",pandas如下:

    9c16fdfaaf51f3de39f0dd261656c11a3b29796b.jpeg?token=4b0b9747f21b098162d53ad6f39e2a40

    简单吧。Excel 上操作也简单,不再展示。

    再看看这个需求。"出生在1980至1990之间的男性"

    d8f9d72a6059252d32969147b523323e5ab5b92a.jpeg?token=b7e4c081e6077eab2f432352d55e7f8e&s=E9D083429FE0AB701471DC070000F0C1

    冰山一角

    Excel 的筛选功能无疑是强大的,不过 pandas 也很厉害。想必有抬杠的小伙伴会说,既然 Excel 自带功能都有,用 pandas 干啥?当然是自动化啦。并且 pandas 中有许多功能,在 Excel 中需要用复杂的函数公式或 Vba 才能实现。

    下期看看 Excel 的高级筛选功能,在 pandas 中是如何实现。

    展开全文
  • Sheet1存有大范围数据,xxx1存有小范围数据,需要从大范围中剔除小数据 可以用vlookup和countif两个函数实现 vlookup =VLOOKUP(A1,xxx1!A:A,1,0) A1:是范围大的第一行数据 xxx1!A:A:是范围小的所有行数据 1:若xxx...

    Sheet1存有大范围数据,xxx1存有小范围数据,需要从大范围中剔除小数据

    可以用vlookup和countif两个函数实现

    vlookup

    =VLOOKUP(A1,xxx1!A:A,1,0)
    A1:是范围大的第一行数据
    xxx1!A:A:是范围小的所有行数据
    1:若xxx1!A:A有很大范围则是返回的是哪一列的数值,这里是第一列数据
    0:精确匹配

    返回的数值就是若有则是对应出来,若没有则显示#N/A
    最后筛选#N/A

    countif

    =COUNTIF(xxx1!A:A,A1)
    xxx1!A:A:这个就是先选取范围小的
    A1:是范围大的第一行数据
    大范围数据有的就是1,没有的就是0,筛选0值

    范围大的数据(第三列是vlookup,第六列是countif)
    在这里插入图片描述
    范围小的数据

    在这里插入图片描述

    展开全文
  • 在开始之前,再次强调一下:本内容为高频常用的数据处理操作对比,不涉及底层技术问题,烦请爱钻牛角的杠精绕行。本内容尽量简单直白、详细步骤,适合数据分析入门。特别喜欢技术语言的大佬们,可自行跳过。关于网友...
  • excel下拉列表数据筛选筛选Excel列表中下拉 (Drop Down from Filtered Excel List) Someone asked me how to make a data validation drop down that only shows the visible rows from a filtered list....
  • 话不多说直接上方法:方法一:函数法1,在需要统计的单元格输入=countif(统计的范围,统计的项)2、回车后直接出结果,双击填充符号十号自动向下填充3、统计结果如图方法二:筛选法1、选中表头---数据---筛选---自动...
  • 在日常使用Excel处理数据时,相信小伙伴们对于筛选功能已经是不陌生了,Excel筛选功能可以快速有效的帮助我们处理大量的数据,将我们想要的结果一一列出来,是我们分析数据的好帮手。今天小编就来分享一组筛选和高级...
  • 我们都知道了,高级筛选可以轻松解决:可以轻松解决多条件的筛选问题可以把结果复制到其他区域或表格中可以完成多列联动筛选,比如筛选B列大于A列的数据可以筛选非重复的数据,重复的只保留一个可以用函数完成非常...
  • Excel中的数据筛选和高级筛选

    千次阅读 2018-12-20 15:07:40
    转载自 ...nbsp; Excel中提供了自动筛选(包括数字筛选、文本筛选和自定义筛选)、和高级筛选两种数据筛选操作。 Excel会根据数据类型来自动判断显示数字筛选或文本筛选...
  • Excel数据筛选到多个工作表 (Filter Excel Data Onto Multiple Sheets) There is a sample Excel file on the Contextures website that has a list of orders, and sales rep names. It has a macro to filter ...
  • Excel 使用 VLOOKUP 筛选数据 VLOOKUP 官方文档 公式: =VLOOKUP (查找值、包含查找值的范围、包含返回值的范围中的列号、近似匹配 (TRUE) 或精确匹配 (FALSE) ) 重点: 第二个参数,需注意查找值所在列应该位于...
  • excel用宏如何筛选日期 使用Excel筛选器宏缩短数据验证列表 (Shorten Data Validation List With Excel Filter Macro) An Excel data validation drop down list only shows 8 items at a time, and with a long ...
  • excel用宏如何筛选日期 Excel筛选器的日期范围2种方式 (Excel Filter for Date Range 2 Ways) In Excel, you can use the drop down arrows in the table headings, to sort or filter the data. In this example, ...
  • 学Python,用RPA ...需筛选出第二年于第一年差值与第一年的比例超过 50% 的数据 二、实现如下: 注意 注意 1、 金额的数据类型 如图所示,将两列金额数据以 int 类型读取出来 注意 2、绝对值 两年的差值...
  • 查找特定范围数据 查找6月份销售额前三位 选中数据 选择开始菜单栏的条件格式,接下来选择最前或最后原则 选择其他规则 五角星选中的一定要改根据自己的需求改 选择格式,设置单元格格式 点击确定 ...
  • 编按:Excel处理数据有自己的游戏规则,尤其是数据源表规矩森严。想Excel运行快,操作顺,就必须遵守Excel的家规。在数据处理中,那种蔑视规则的人,忽视规则的人,注定要碰得头破血流,因为Excel不!留!情!在前几...
  • 筛选出第二年于第一年差值与第一年的比例超过50%的数据 二、实现如下: 注意 注意1、 金额的数据类型 如图所示,将两列金额数据以 int 类型读取出来 注意2、绝对值 两年的差值 前面需要 加上 abs,意将差值转换...
  • Excel 下拉列表数据有效性智能匹配筛选 来源公式如下(适用于2010以上版本) =OFFSET(数据!$A$1,MATCH(INDIRECT("R"&ROW()&"C"&COLUMN(),)&"*",数据!$A:$A,0)-1,,COUNTIF(数据!$A:$A,INDIRECT("R...
  • excel数据处理

    千次阅读 多人点赞 2019-01-12 21:08:25
    excel大部分常用操作
  • 一:EXCEL 2007使用 在Excel2007中快速删除重复记录的方法 http://www.pconline.com.cn/pcedu/soft/office/exce
  • Python是如何生成一个新表xlrd阅读过量excel过滤后,和xlwt写过量。实现搜索一些字符的一些细胞在excel中作为关键字。不多说直接在代码上。utf - 8 # - * -编码:- *进口xlrdimport xlwtdef read_excel():工作簿= _...
  • 数据筛选在大量数据中,有时只有一部分数据可以分析和参考,此时可以利用数据筛选功能筛选出有用的数据,然后在这些数据范围内进行进一步的统计和分析。Excel提供了【自动筛选】【自定义筛选】和【...
  • Excel提供了许多用于对工作表中的数据进行排序和筛选的基本功能。这些功能可从数据 功能区访问 。
  • 使用色阶显示不同范围数据 选中数据 开始菜单栏中选择条件格式,然后选择色阶 设置色阶的颜色,如果自己对已经存在的色阶不满意,可以选择其他规则建立自己的色阶。 自己建立规则后,点击确定 ...
  • Excel数据分析中最常用的工具,本篇文章通过python与excel的功能对比介绍如何使用python通过函数式编程完成excel中的数据处理及分析工作。在Python中pandas库用于数据处理 ,我们从1787页的pandas官网文档中总结出...
  • c# excel 怎样使导出到excel数据的某个区域(如:A列数据)的数据进行筛选,数值超过5.0%的字体红色,excel里实现为:条件格式>突出显示单元格规则
  • excel高级筛选 Excel自动筛选还是高级筛选? (Excel AutoFilter or Advanced Filter?) Do you ever use the Excel Advanced Filter feature? Or is all your filtering done with an AutoFilter? 您是否曾经使用过...
  • 分别介绍了创建数据透视表、改变数据透视表的布局、刷新数据透视表、数据透视表的格式设置、在数据透视表中排序和筛选数据透视表的切片器、数据透视表的项目组合、在数据透视表中执行计算、数据透视表函数的综合...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 8,787
精华内容 3,514
关键字:

如何筛选excel数据范围