b1gc8v 发表于 2024-10-1 14:09:20

把握这些Excel函数用法，工作效率加强60%


    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">这儿</span>，<span style="color: black;">咱们</span>将引导您探索六大Excel函数的奥秘，它们分别是VLOOKUP、IF、DATEDIF、IFERROR、SUMIF和MEDIAN。<span style="color: black;">把握</span>这些函数的<span style="color: black;">运用</span>，就如同<span style="color: black;">持有</span>了<span style="color: black;">加强</span>工作效率的神奇工具。</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">01</span></p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">VLOOKUP函数</span></p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">VLOOKUP函数按列<span style="color: black;">查询</span>，在指定区域中<span style="color: black;">查询</span>指定值，返回与其对应的<span style="color: black;">另一</span>一个值。。</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">参数形式：</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">函数VLOOKUP（<span style="color: black;">查询</span>值，被<span style="color: black;">查询</span>区域，要返回的结果在<span style="color: black;">查询</span>区域的第几列，<span style="color: black;">查询</span>方式）</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">如下图所示，<span style="color: black;">按照</span>订单编码<span style="color: black;">查询</span>对应的<span style="color: black;">营销</span>人员，公式为：=VLOOKUP(M8,A:H,3,0)</p>
    <div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-axegupay5k/d20295a4e81d4390b283a9e1d9be928e~noop.image?_iz=58558&amp;from=article.pc_detail&amp;lk3s=953192f4&amp;x-expires=1727967665&amp;x-signature=DQ6%2BnpB1WXFVFwd7h2ebKPVfkIw%3D" style="width: 50%; margin-bottom: 20px;"></div>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">Excel老陈说：</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">第1</span>个参数：<span style="color: black;">查询</span>值：“Excel-007”，单元格M8</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">第二个参数：被<span style="color: black;">查询</span>区域：A:H（<span style="color: black;">查询</span>区域A列到H列，要<span style="color: black;">重视</span>首列要<span style="color: black;">包括</span><span style="color: black;">查询</span>值）</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">第三个参数：要返回的结果在C列，即在<span style="color: black;">查询</span>区域的第3列，<span style="color: black;">因此</span>填3</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">第四个参数：<span style="color: black;">查询</span>方式：精确<span style="color: black;">查询</span>（0或FALSE）</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">02</span></p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">IF函数</span></p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">IF函数<span style="color: black;">功效</span>：<span style="color: black;">按照</span><span style="color: black;">要求</span>进行判断并返回<span style="color: black;">区别</span>的值。</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">函数参数形式：</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">=IF(判断<span style="color: black;">要求</span>,符合<span style="color: black;">要求</span>时返回的值,不符合<span style="color: black;">要求</span>时返回的值)</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">如下图所示，判断金额<span style="color: black;">倘若</span>大于20000则返回达标，否则返回不达标，公式为=IF(H2&gt;2000,"达标","不达标")</p>
    <div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/aa2870cbbaad4eefa96b15572e1ed9fd~noop.image?_iz=58558&amp;from=article.pc_detail&amp;lk3s=953192f4&amp;x-expires=1727967665&amp;x-signature=eIKplli4L85rWsgpj6un%2FMWD6Zw%3D" style="width: 50%; margin-bottom: 20px;"></div>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">03</span></p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">DATEDIF函数</span></p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">DATEDIF函数<span style="color: black;">功效</span>：用于计算两个日期之间的相差。</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">函数参数形式：</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">DATEDIF(<span style="color: black;">初始</span>日期,截止日期,间隔的类型（Y,MD))</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">如下图所示，<span style="color: black;">按照</span>入职日期计算工龄，公式为：=DATEDIF(D2,TODAY(),"Y")</p>
    <div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/f627157ed94f4aa69a561233826225f6~noop.image?_iz=58558&amp;from=article.pc_detail&amp;lk3s=953192f4&amp;x-expires=1727967665&amp;x-signature=163qnkVwoRinE%2BFIDZ5tfYDplq0%3D" style="width: 50%; margin-bottom: 20px;"></div>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">Excel老陈说：DATEDIF函数以D2的入职日期<span style="color: black;">做为</span><span style="color: black;">初始</span>日期，以系统当天(<span style="color: black;">经过</span>TODAY()返回）<span style="color: black;">做为</span>截止日期，第三参数<span style="color: black;">运用</span>“Y”，<span style="color: black;">暗示</span>计算两个日期之间的整年数。</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">04</span></p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">IFERROR函数</span></p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">IFERROR函数它用于<span style="color: black;">检测</span>公式返回的结果，<span style="color: black;">倘若</span>这个结果是错误（如#DIV/0!、#N/A、#VALUE!等），则返回指定的默认值；<span style="color: black;">倘若</span>结果是正常值，则返回这个正常值。</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">=IFERROR(value, value_if_error)</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">第1</span>个参数是要运算的公式，第二个参数是在公式计算<span style="color: black;">显现</span>错误值的<span style="color: black;">状况</span>下，要指定返回的值。</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">如下图所示，<span style="color: black;">倘若</span>VLOOKUP函数找不到，默认会返回#N/A错误，<span style="color: black;">运用</span>IFERROR函数<span style="color: black;">能够</span>让其返回指定的值。公式为：=IFERROR(VLOOKUP(M8,A:H,3,0),"未找到")</p>
    <div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/0df7de3dc0d5430291a18c829b1e0a1c~noop.image?_iz=58558&amp;from=article.pc_detail&amp;lk3s=953192f4&amp;x-expires=1727967665&amp;x-signature=d0FovPJgJea2dZVFMRfZal6CZnE%3D" style="width: 50%; margin-bottom: 20px;"></div>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">05</span></p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">SUMIF函数</span></p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">SUMIF函数：用于对区域中符合<span style="color: black;">要求</span>的单元格求和，<span style="color: black;">因为</span>该函数只能指定一个<span style="color: black;">要求</span>，<span style="color: black;">因此</span>又将其<span style="color: black;">叫作</span>为单<span style="color: black;">要求</span>求和。</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">函数参数：</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">=SUMIF(<span style="color: black;">要求</span>单元格区域,设置<span style="color: black;">要求</span>,<span style="color: black;">需求</span>和单元格区域 )</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">如下图所示，需<span style="color: black;">需求</span>出Excel<span style="color: black;">基本</span>的金额合计，公式为：=SUMIF(B:B,M8,H:H)</p>
    <div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/b867a557abbe4e7784cc8d8324f9b71c~noop.image?_iz=58558&amp;from=article.pc_detail&amp;lk3s=953192f4&amp;x-expires=1727967665&amp;x-signature=joswIXz%2FpEU%2BJH6HQUb%2FyRMNIpQ%3D" style="width: 50%; margin-bottom: 20px;"></div>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">06</span></p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">MEDIAN函数</span></p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">MEDIAN函数<span style="color: black;">重点</span>是返回给定数值集合的中值。中值<span style="color: black;">亦</span><span style="color: black;">叫作</span>中位数，是一组数据中<span style="color: black;">位置于</span>中间的数值。</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">如下图所示，计算金额的中间值，公式为：=MEDIAN(H2:H84)</p>
    <div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/389aad43f2814efba1d9c352fb63a69a~noop.image?_iz=58558&amp;from=article.pc_detail&amp;lk3s=953192f4&amp;x-expires=1727967665&amp;x-signature=fh8U8ENoouTu8YxLXh6GT0Cg8p4%3D" style="width: 50%; margin-bottom: 20px;"></div>




qzmjef 发表于 2024-10-7 00:40:42

外贸网站建设方法 http://www.fok120.com/

qzmjef 发表于 2024-10-19 19:31:36

谷歌外贸网站优化技术。
页: [1]
查看完整版本: 把握这些Excel函数用法，工作效率加强60%