excel自动生成天数

作者结合在支付领域工作一年的经验，并结合在跨境支付公司的实际项目和理论知识，对计费系统的渠道对账功能搭建进行详细阐述；供大家学习和参考。

一、需求背景

目前渠道对账是资金根据渠道侧给的文件脚本生成，为方便资金更快的查看处理对账结果，上线渠道对账功能；同时核对渠道侧和我方交易侧的账单明细后将，渠道对账面板功能将结算金额按照计费规则拆解成费用项展示在面板上方便后续计费。

二、需求分析

是确保所有交易都被正确无误地记录在账户或账单上，获取渠道和交易侧的匹配情况，方便资金处理对账结果，同时对无误的账单明细渠道对账后根据计费规则罗列对账单具体的收费项总的费用；

对的是账单明细和总额，由于wechat和alipay渠道目前只有支付，无退款；所以这两个渠道只对支付明细；对退款不进行对账。

1. 对明细：对的是渠道金融机构拿到的报表（明细）和公司数据库里的交易明细对账；最终显示的条目只展示交易侧和渠道侧的明细需要匹配多少条，成功匹配了多少条；未匹配多少条；
2. 对总额：最终展示在页面上的是无差异账单明细中每个收费项的总额；不同的渠道匹配规则不同。主要展示结算总金额信息、和计费相关的具体收费项金额信息。

1）文件上传模式：由于渠道侧的账单明细无法从数据库直接获取，是渠道那边发给我们的excel文件，因此；我们通过上传文件的形式进行匹配；支持excel\\csv文件上传。

2）对账时间：由于负责资金的同事在中国，结合工作习惯以北京时间为准进行对账，每日0点，根据渠道侧的不同的币种，系统自动生成待上传账单的基本表头信息的条目；即根据币种不同显示渠道待上传的条目。

• 考虑提前生成脚本的好处：由于是对账文件是人工上传，系统根据渠道所支持的币种多少，对应生成多少条目；这样根据币种来划分方便后续资金结算；同时避免上传的文件有误；
• 由于负责资金的同事在中国，所以对账时间按照中国时间为准，但公司总部在国外的话结算日期按照总部所在国家为准，即当结算周期遇到节假日时，按照国外的节假日为准。

• 北京时间0点系统按照渠道的结算币种数量生成对应的渠道对账条目，次日资金上传从渠道那边获取的excel文件，通过人工上传进行对账；
• 由于渠道侧的文件来源于不同渠道方给的excel文件；文件取名或内容格式不一，因此该功能对上传的文件名格式不做限定；但是文件中字段的内容需要和渠道方核实，做格式兼容处理。

上传完成后系统自动解析，解析完成后对应以下几点：

1）当解析成功时，弹出弹窗【解析成功，是否开始对账】。

2）当解析失败时，有几点原因导致：

• 上传文件内容格式有误——找渠道侧确认文件内容；
• 上传的文件内容重复——需要把历史上传的文件删除后才能二次上传；
• 网络加载错误。

1）交易侧的匹配账单以当天账单的post_time字段为准进行匹配

比如10.17号获取账单、其账单内的post_time记录的是5.10号，则对应在系统中生成的条目是匹配时间是5.10号；开始对账时间是10.17号。

2）渠道侧和商户侧的账单明细按照PI号进行匹配

“PI号”是指付款信息号码（Payment Instruction Number），通常用于跟踪和匹配交易。PI号的达标意味着PI号字段的信息是准确、一致和完整的，这使得渠道侧和交易侧的账单能够按照PI号进行匹配。

3）币种和对账条目一一对应

由于渠道涉及多币种；考虑到不同币种汇率差异会导致对账金额不一致；随意不同渠道的对账条目按照币种划分；即以wechat为例，涉及到结算币种由GBP、HK和USD；即0点时系统准备拉取交易对账脚本，按照币种数量生成对应的条目，当天资金上传渠道侧文件时，同一份文件按照币种不同上传三次，每一次只解析和对账币种所对应的账单。

1）匹配无差异：平账；无需处理。

2）匹配有差异：对账金额不一致即交易或渠道的某一侧少钱。

由于具体的收费项是根据计费规则确定的；影响最终收费项金额的因素由：费率、汇率、费用计算公式，结算金额、和系统等因素组成。

① 时间差导致：由于不同地区和国家之间的时差和时区差异，会导致交易日期和时间的不匹配。

解决方案：对上传的渠道侧的数据，该日未对平的数据在数据库中回溯七天；并在差异处理中记录该条数据，如果七天内对平了该数据最终，则差异处理中该条数据自动删除；如果七天后仍未对平，则系统不再回溯，差异处理功能中仍会显示该条信息，对账结果显示有差异；状态显示待处理。

② 通信问题：由于渠道对账涉及多个系统和平台之间的数据传输，会存在通信问题，可能导致交易数据未能正确传递或同步，从而引起不平账。

解决方案同上。

③ 汇率差异：在跨境支付中，涉及不同货币的交易可能会受到汇率波动的影响。如果汇率不一致或不准确地应用于交易，可能会导致金额不匹配，从而引起不平账。

上线汇率面板功能，将汇率和计费系统联动；汇率面板中的数据每日从从中国银行和海云汇中直接拉取。当发掘拉去的汇率不合适时，支持人工修改。

对账完成后，会在【对账明细】字段中显示匹配结果，即交易侧和渠道侧的各自明细要匹配多少条；在【匹配结果】字段中显示成功匹配了多少条；有差异的有多少条；需要回溯的有多少条。

注：需要回溯的数据是指渠道侧或交易侧某一侧有这条数据；另一侧没有获取。

• 由于对账时间是以的post_time时间为准；获取的是post_time当天的渠道侧账单明细数据；当对账完成后，功能页面显示改天各收费项明细的总金额；方便内部人员知道公司在渠道这边计费相关的财务状况；
• 对账完成后，支持系统对渠道侧和交易侧对账结果账单的下载；同时系统会把对账结果的excel文件自动推送的到通知群里，文件包括渠道侧、交易侧的对账结果明细以及差异处理的文件。

注意：下载的文件中除了包括原始的账单明细外，还要加上对账的匹配结果字段，以及对需要回溯的数据，后面加上回溯天数。

差异处理：对账完成后，对有差异的账单明细系统推送给到差异处理功能中；去处理差异；同时也会生成excel文件自动发送到通知群里；提醒工作人员去处理。

① 对由于时间差异导致交易侧数据未录入，需要等待回溯的订单，回溯完成后渠道自动对账，对平后，差异处理面板中该条数据系统自动删除，如未对平则保留该条；等待差异处理。

② 差异处理面板中的数据处理完成后，数据重新进入对账流程和交易侧数据进行对账，直到账单对平为止。进入对账流程还是以该渠道的post_time时间为准。

• 渠道侧的文件命名：渠道名_matched_report_年_月_日
• 交易侧的文件命名：渠道名_Merchant_matched_report_年_月_日
• 差异明细的文件命名：渠道名_差异明细_年_月_日

三、产品方案

渠道对账涉及到的渠道：不同渠道匹配规则不同，现以wechat和Alipay+渠道为例。

1）搜索栏

① 渠道名称：可选择wechat 和Alipay+。

② 结算币种：来源于渠道侧给到的结算币种，我方从账单报表中进行提取可得：

• Wechat结算币种对应GBP/USD/EUR
• Alipay的结算币种对应HKD/GBP/USD/EUR

注：即根据前面【对账时间】中关于对账条目获取的规则描述，可知，在次日0点时，系统根据渠道对应币种数量自动生成对应的条目；每一个条目需要上传一次渠道对账文件；待此时工作时间资金上传渠道报表，按照比重条目生成对应的对账结果数据。

③ 时间：由于渠道侧和交易侧的对账是根据匹配对应时间进行对账，所以时间按照匹配对应时间获取相应的条目。

2）信息栏

① 渠道名称

② 结算币种

③ 匹配对应时间：按照渠道侧和交易侧文件中的post_time时间对应

④ 账单解析状态：解析成功&解析中&解析失败

⑤ 账单笔数：分别显示渠道侧和交易侧对账文件中各自有多少条目；即显示渠道侧：200条；交易侧：200条

⑥ 对账结果：显示：对平：XX条；待回溯：XX条；差异处理：XX条。

待回溯指的是交易侧由于汇率时间差或其他原因还没有接收到对应的账单条目；需要等待一段时间才会显示。

⑦ 渠道侧总额：由于匹配的账单明细都是同一天，故渠道侧总额等于当天渠道侧账单明细的所有条目交易总额；同理交易侧总额等于交易侧账单明细所有条目的交易总额=tran_amount字段之和

⑧ 文件上传时间：为资金开始操作对账时间

⑨ 操作人：操作对账对应的工作人员名称

⑩ 管理：详情/差异处理/下载账单/重新解析/删除

• 详情：由于渠道除了为将账对平，方便资金人员了解财务状况及后续审计外，对公司侧我们该环节的目的是将公司渠道计费涉及到的费用项提出，方便老板了解渠道侧财务状况；故详情中重点罗列；
• 差异处理：同一匹配对应时间下，同一渠道下上传渠道文件后，按照渠道下币种数量生成条目，对应币种下有差异的账单明细进行差异处理；
• 下载账单：对账完成后，支持下载对账后的渠道账单和交易账单 i 下载的渠道账单和交易账单在原文件的基础上新增对账结果字段以及回溯天数字段（需要回溯的显示回溯天数，不需要的显示—）；
• 重新解析：由于网络加载错误等原因导致需要重新上传解析的，可以点击重新解析按钮，避免二次上传文件；
• 删除：资金会出现对同一天的渠道文件进行二次上传的场景，二次上传时必须把前一条上传的文件数据删除，才能二次上传。

3）搜索栏

① 页面信息展示：页面一开始展示所有渠道的数据，按照渠道名称首字母和匹配对应时间两个条件倒叙配列

② 渠道：支持Wechat 和Alipay+搜索

③ 结算币种：包括HKD/GBP/USD/EUR

④ 时间：选择时间后对应【匹配对应时间】字段的的检索结果

比如wechat渠道，对应结算货币有GBP/USD/EUR，即系统自动拉取三个条目。

上传渠道文件后解析过程提示分为解析成功和解析失败。

① 解析成功：上传—上传中—正在解析—解析成功并判断是否开始对账—开始对账—对账成功/对账失败；

• 对账成功后，即可在对应条目上显示计费信息；同时需要差异处理的账单条目显示在差异处理模块中；
• 对账失败后，请重新上传；——解析结果栏中显示解析失败。

② 解析失败：上传—上传中—正在解析—解析失败；

• 解析失败，请重新上传；
• 网络加载超时，解析失败请重新上传；
• 文件内容格式错误，解析失败请重新上传。

详情中主要展示计费相关的信息条目；主要包括：

① 对账信息：渠道名称/匹配对应时间/结算币种/文件上传时间/账单笔数/对账结果；

② 渠道侧账单明细：即展示post_time当天从渠道侧获取的账单中计费项的总金额，包括Total Settlement Amount/Total Acq_fee/Total Ic_fee/Total Scheme_fee/Total Other_fee/Total 3ds_fee/Total Settlement net Amount；

③ 交易侧账单明细：即展示post_time当天交易侧（即公司数据库中）获取的账单中计费项的总金额，包括Total Settlement Amount/Total Acq_fee/Total Ic_fee/Total Scheme_fee/Total Other_fee/Total 3ds_fee/Total Settlement net Amount；

④ 具体的费用项根据计费规则系统进行计算

部分渠道相关费用项，渠道侧会算好给我们；有的渠道需要我们自己按照计费规则，系统进行计算。

1）差异处理页面展示信息

① 信息栏：展示字段包括渠道名称/匹配对应时间/订单号/未匹配天数/订单金额/渠道流水总金额/交易流水总金额/差异处理状态/操作时间/差异原因/操作人/管理

订单号：用于标识商户或支付发起方系统中特定订单或交易的唯一标识符。

注：如何根据订单号追踪PI号：

获取订单号和相关支付信息：首先，您需要获取包含订单号的商户或支付发起方的相关交易信息。这可能是从您的系统中提取订单号，或者从商户或客户提供的相关信息中获取。

查找相关支付交易：使用订单号，您可以联系相关的支付渠道或银行来查找与该订单号相关的支付交易。这通常涉及与支付渠道或银行的客户支持或运营团队进行联系。

确认PI号：一旦找到相关支付交易，您可以请求支付渠道或银行提供与订单号相关的PI号。PI号通常可以在他们的支付指令或结算文件中找到。它是用于跟踪国际支付和结算的重要标识符。

进行对账或差异处理：一旦获得了PI号，您可以将其用于对账或差异处理。通过将订单号与相关PI号关联，您可以验证支付是否已经成功处理，或者解决可能存在的不匹配或差异。

• 未匹配天数：由于交易侧数据获取滞后导致部分数据未匹配，故允许在数据库中回溯7天；当回溯时间为第四天匹配成功时，未匹配天数＝4；
• 订单金额：即交易金额；
• 渠道结算净额：由于该渠道只对支付，不含退款；且仅对订单状态处于成功时的订单进行对账；所以渠道结算净额=结算金额-手续费
• 交易结算净额：交易结算净额=结算金额-手续费；
• 差异处理状态：已处理、待处理和处理中；
• 操作时间：等于差异处理时间；
• 差异原因：差异取决于交易侧金额、渠道侧金额于订单金额不一致和订单状态；故差异原因包括：交易侧金额与订单金额不一致/渠道侧金额与订单金额不一致/金额与订单状态不一致/渠道侧与交易侧金额不一致/汇率差异/交易侧无数据；
• 管理：【差异处理】按钮提交证据进行差异处理。

2）差异处理功能

• 订单号：自动获取；
• 上传凭证：支持上传原始交易记录/三方支付渠道或银行记录/我方订单和交易记录/汇率信息/手续费信息等截图；要求上传JPG//PNG/JPEG格式，文件不超过20M；
• 差异处理记录日志：上传差异处理过程的操作日志/处理代码等；
• 差异原因：系统自动给出的原因有交易侧金额与订单金额不一致/渠道侧金额与订单金额不一致/金额与订单状态不一致/渠道侧与交易侧金额不一致/汇率差异/交易侧无数据；该字段需要在系统给定的基础上加以补充。

注：差异处理中的数据包括：渠道侧和交易侧确实存在差异（即账没有对平）和交易侧数据由于时间差未及时获取而无法和渠道侧匹配对账造成的差异

①渠道侧和交易侧确实存在差异：给出差异原因并进一步追溯解决；

• 金额导致的差异：修改金额并提交支付凭证截图；
• 系统导致的差异：迭代BUG后，提交差异处理证明；
• 汇率导致的差异：系统目前是每日线上从官方渠道拉取汇率值，若汇率不是想要的数值，后期支持手动修改，修正汇率。

②时间差未及时获取而无法和渠道侧匹配对账造成的差异：系统预留出7天的回溯时间，七天内如果该明细和交易侧对平了，则差异处理中该条记录自动删除，同时渠道对账中的相关费用随着也要变化——具体根据匹配的PI号和匹配时间确定对应条目。

支持下载渠道侧和交易侧的对账结果明细；

① 对账完成后，下载按钮由置灰状态变成可点击状态；同时选择想要下载的账单（支持多选）。

② 对账成功后，除了页面支持下载外，系统自动将交易和渠道侧的对账单明细以及差异明细文件发送到通知群中。

③ 下载与发送：

四、总结复盘

1）明确渠道对账的目的

• 确保渠道提供商提供的账单与实际交易记录一致。
• 跟踪和解决渠道侧和交易侧之间的不平账或差异。
• 方便老板查看公司计费相关的财务状况。

2）明确渠道对账的内容：对的是渠道侧和交易侧的账单明细；涉及的渠道是微信和支付宝；且目前渠道侧只包含支付成功的账单明细；故我们仅对订单状态处于success状态的明细进行对账。

3）明确渠道对账的上传解析规则&匹配规则。

4）明确计费项金额的规则：对应金额按照计费规则进行计算。

5）最终的结果展示。

1）明确目标—明确对账业务逻辑和计费数据逻辑后：最后进行功能设计。

2）目标拆解

① 确保渠道提供商提供的账单与实际交易记录一致：

• 渠道侧账单明细和交易侧账单明细具体涉及的金额由哪些，进行对账；
• 跨境国际对账需要考虑币种问题，不同的结算币种对应的汇率不同，最终的结算金额也不同；
• 如何展示最终对账结果——遵从交互逻辑简单，清晰展示涉及到的信息。

② 跟踪和解决渠道侧和交易侧之间的不平账或差异：

差异处理：展示差异和设计差异处理功能。

③ 方便老板查看公司计费相关的财务状况：老板想要了解计费相关的信息（涉及到公司的盈利情况）；故而最好放在一起展示。

本文由@月月有????吃 原创发布于人人都是产品经理，未经许可，禁止转载。

题图来自 Unsplash，基于CC0协议

该文观点仅代表作者本人，人人都是产品经理平台仅提供信息存储空间服务。

空淑滢594如何在excel中自动生成当天的日期?
融轰俭18086777538 ______ 1.手动输入:Ctrl+; 2.公式:=TODAY() 这两个是有区别的 1、是你插入时计算机的时间,也就是说你6-28插入就是6-28,不会变化了 2、是你带开是的时间,也就是说你6-28打开是6-28,7-28打开是7-28 明白?

空淑滢594怎么在excel里面自动生成日期 -
融轰俭18086777538 ______ 直接输入可以的,如2016/7/25 另外,如果是每天变的日期,可以使用=TODAY()+10 today()表示今天,根据电脑日期,每天自动变化,后面根据自己需求可加可减

空淑滢594excel自动生成日期 -
融轰俭18086777538 ______ 展开全部1、首先设置单元格格式. 单元格格式--数字--自定义,设置成m＂月＂d＂日＂2、输入时间,如5-9,系统会自动变成5月2日3、在选中这个单元格的情况下,鼠标放置在右下角,这是会出现黑色的十字架,点住鼠标拖动,(这但不用我交了把)4、OK了,如果时间到6月份,会自动变的.

空淑滢594如何使excel自动生成日期? -
融轰俭18086777538 ______ Excel自动生成日期我想在一列自动生成日期(不超过设定的某一天), 不要有规律的有什么办法么,先将这一列的单元格格式设置为“日期”格式,如果要将日期设定为2000-1-1到2100-1-1之间那么在这一列中输入以下公式: =INT(RAND()*(73051-36526)+36526) 要设置不同的时间段,只要将公式中的数值进行修改即可 想要的日期和数字的规律是什么啊,从1900-1-1的那一天起为第1天,到2000-1-1的那一天就刚好是第36526天

空淑滢594excel自动生成日期
融轰俭18086777538 ______ 先将这一列的单元格格式设置为“日期”格式 如果要将日期设定为2000-1-1到2100-1-1之间那么在这一列中输入以下公式: =INT(RAND()*(73051-36526)+36526) 要设置不同的时间段,只要将公式中的数值进行修改即可

空淑滢594excel如何自动统计天数,如图 -
融轰俭18086777538 ______ M2=counta(b2:L2), 下拉

空淑滢594excel表格自动统计日期天数 -
融轰俭18086777538 ______ 如果在日期A1,金额在B1 那么=sumif(A$1:A$8,＂1999-1-1＂,B$1:B$8),这就是1999-1-1累加之和 要统计1999-1-1的个数=countifs(A$1:A$8,＂1999-1-1＂)

空淑滢594如何在excel表格中快速计算天数呢? -
融轰俭18086777538 ______ 1、首先在打开的excel表格中输入一组日期数据,需要在该组日期上增加或减少天数. 2、以增加天数为例,在B1单元格中输入计算公式:=A1+45,意思是在A1的日期基础上增加45天. 3、点击回车即可生成计算数据,可以看到此时B1单元格中显示的日期为增加了45天之后的日期. 4、如果需要批量增加日期,可以将B1单元格的公式向下填充即可完成批量增加同样的45天的操作.

空淑滢594excel自动计算天数 -
融轰俭18086777538 ______ =DAY(DATE(2015,ROW(A2),))或=TEXT(DATE(2015,ROW(A1)-1,26),＂YYYY/M/D＂)&＂-＂&TEXT(DATE(2015,ROW(A1),25),＂YYYY/M/D＂)下拉填充

空淑滢594excel 怎样自动生成一个新日期,使得给定的某个日期,相隔给定的天数 -
融轰俭18086777538 ______ 在A2单元格输入以下公式,然后向下填充公式 =A1+147 详见附图示例