excel表格中订单号自动生成？_百度知道
excel表格中订单号自动生成？
大概意思是：我想做个表格例如订货单，并在单子上标明订单号，当我输入第一份订单打印后，再输入二份订单的时候，那个订单号可以自动更新，不需要我再手动去改！我想把这订单号编成类似这种的，就是年月日+编号（这个编号是为了知道这是今天打的第几...
我有更好的答案
　　一般“订单号”类型的数据是以文本形式存储的，可能包含自定义前缀、日期、流水号、等等自定义类型的数据。把需要的数据用文本连接符&号连接起来就可以。　　例如：　　=&XSD&&TODAY()&ROW(A1)　　公式中&XSD&是自定义前缀，TODAY()是返回当前日期的函数，ROW(A1)是返回行号。　　三个数据之间用文本连接符号&来连接起来的。　　需要注意：　　任何公式中的文本内容都需要用半角双引号括起来。　　ROW（）函数常用来返回流水号。　　可以用TEXT函数来设定流水号的格式，比如流水号是3位，自动用零补齐，可用公式=TEXT(ROW(A1),&000&)即可。　　文本连接符号&常用来连接任何文本内容，也可以将两个公式的结果进行连接。
采纳率：72%
能用宏操作打印候用宏打印直接增加~
本回答被提问者采纳
第编号手工打A1第二始A2写入公式=IF(LEFT(A1,8)=(YEAR(TODAY())&REPT(0,2-LEN(MONTH(TODAY())))&MONTH(TODAY())&REPT(0,2-LEN(DAY(TODAY())))&DAY(TODAY())),(YEAR(TODAY())&REPT(0,2-LEN(MONTH(TODAY())))&MONTH(TODAY())&REPT(0,2-LEN(DAY(TODAY())))&DAY(TODAY()))&REPT(0,3-LEN(RIGHT(A1,3)+1))&(RIGHT(A1,3)+1),YEAR(TODAY())&REPT(0,2-LEN(MONTH(TODAY())))&MONTH(TODAY())&REPT(0,2-LEN(DAY(TODAY())))&DAY(TODAY())&&001&)
假设你在SHEET1的A1中显示订单编号按下ALT+F11,双击左边的&THISWORKBOOK&,复制下列代码退出.另:在打印预览时,编号才会更新.Private Sub Workbook_beforePrint(Cancel As Boolean)Sheets(&Sheet1&).Cells(1, 1).Selecta = Selection.ValueIf a = && Or Left(a, 8) && Format(Date, &yyyymmdd&) ThenSelection.Value = Format(Date, &yyyymmdd&) & Format(1, &00&)ElseSelection.Value = Val(a) + 1End IfEnd Sub
在A在A2=A1+1向下拖
其他2条回答
为您推荐：
其他类似问题
您可能关注的内容
excel表格的相关知识
换一换
回答问题，赢新手礼包
个人、企业类
违法有害信息,请在下方选择后提交
色情、暴力
我们会通过消息、邮箱等方式尽快将举报结果通知您。999被浏览170,372分享邀请回答57081 条评论分享收藏感谢收起4216 条评论分享收藏感谢收起Access denied | adoncn.com used Cloudflare to restrict access
Please enable cookies.
What happened?
The owner of this website (adoncn.com) has banned your access based on your browser's signature (40c04d3dea8895fb-ua98).3794人阅读
SAP（217）
方法一：系统标准方法
如何查找到production order的修改记录呢?因为有很多用户 "赖皮",明明自己修改过某些资料,如qty or component,可就是不承认,并且还大骂SAP大大的有问题 ,真会气死人,所以想记录下来.
HEHE,这里我就摘录一段SAP的NOTES给大家看看,如何去整这玩意.
Note 390635 - Change documents for production and process order
You cannot activate the creation of change documents for production orders andprocess orders.
Customizing table T399X (parameters dependent on order type) contains fieldFLG_DOC but you cannot maintain it using the Customizing transactions forproduction or process orders.
Other terms
Production order, process order
T399X-FLG_DOC
SPRO, BADI
Reason and Prerequisites
It is not intended to use the SAP standard tool for the creation of changedocuments for production or process orders in the SAP standard system. Theindicator in table T399X is only interpreted by maintenance orders andnetworks.
The reason for this is that the tool cannot determine dependencies of changesand thus would log too many changes that would have a negative effect on theruntime.
Example: A production order contains 10 operations and 1000 components
Case 1: You change the requirements quantity for a component manually.
The system createsa change document for the changed component.
Case 2: You change the planned quantity in the order header manually.
The change causesthat the quantites are changed automatically for all operations and components.The system would create
a change document for the changed header quantity,
ten change documents for the automatically changed operation quantities (ifdefined) and
thousand change documents for automatically changed component quantities.
Use the SAP enhancement PPCO0007
that is processed when you save orderchanges. You can find the SAP enhancement in Customizing under the menu path"Shop Floor Control or Production Planning for Process Industries -&Process Order -& System Modifications -& Enhancements
for OrderMaintenance -& Enhancement when Saving an Order (Header Fields)".
Here you can define additional logic to decide whether or not the systemcreates change documents.
For this, you can access either the SAP standard tool for creating changedocuments or your own tool.
The call of the customer exit takes place in function module CO_ZV_ORDER_POSTwhen you save. The current data of an order header is supplied via parameterHEADER_IMP. For collective orders the exit can be called several times - oncefor each order in the collective
Remember that a dialog box must not be accessed and a message must not beissued from the exit.
You can use the attached source code as a template for accessing the creationof change documents.
You can also see in the source code how the current and original data of thedifferent order objects can be provided by internal tables.
The example source code uses function module ORDER_WRITE_DOCUMENT that logs -for the ORDER change document object - the changes for the tables transferredto the module.
It may be appropriate to define a separate object for change documents fororders, for example ZZORDER. If required, you can use the ORDER standard objectas a template.
The following tables of the order may have to be taken into ac_countfor thistype of change document object:
Description
Production Process order
Relationships
Productionresource/tool
Work ordersequence
Process instructions
Process instructionvalues
Order header dataPP
Generaloperation
User-specific dataoperation
Quantities/dates/values in theoperation
Order headergeneral
DRAD_PORDER Link: Document toorder
Triggerpoint
Reservation
Index reservation/directprocurement
Important:
The creation of change documents using the SAP standard tool requires that theindicator for change document creation is set for the data element that definesthe affected table field. For example, this indicator is not set for dataelement GAMNG (total order
quantity) in the standard system which is the reasonwhy changes to the planned order quantity of the order header are not logged.The reason for this is that the GAMNG field of the AFKO table is defined usingdata element GAMNG.
To be able to set the indicator for the creation of change documents withoutmodifications and display change document data at a later stage, refer to Note394887 (Enhancements for installing PH-ELR_100_46C) for Release 4.6C; as ofRelease 4.70 (SAP Enterprise),
the Add-On is part of the R/3 standard system.
You can of course set the indicator for activating the creation of documentchanges manually for each data element using Transaction SE11, however, thischanges the data element.
You can display the change documents using, for example, programs RSSCD100,RSSCD150, or RSSCD200. The display from the standard transactions of the orderis not supported.
For Release 45B to 46B, you must also create function moduleCO_BR_AFFX_GET_TABLES_POST in function group COBR using Transaction SE37.Define the following TABLES parameters for the interface:
Parameter name
Reference type
OptionalEXP_AFFT
To be able to archive the generated change documents, you must implement theprogram modification from Note 508212 in your system.
方法二：通过增强程序记录。
SAP生产工单更改(CO02)记录详细教程
In SAP system, it has not prodution order"s change record. it is verypity! how to solve it? you can use below ways :
需求：SAP在更改生产工单时并没有提供详细的更改记录，查看SAP的Notes，如果强行开启FLG_DOC（控制往更改记录CDHDR，CDPOS里写数据）标记，在批量处理组件过多的生产工单时会导致程序运行出错，SAP建议不开启。User要求对保护字段进行更改记录，无奈只有使用SAP提供的增强功能，东西经过测试初步完成，发出来共享一下，如果大家有更好的方法，发出来讨论讨论，步骤如下：
1.CMOD新增一项目ZCO02,增加增强PPCO0001。
2.查看、Debug SAP源代码，了解数据流向，主要了解在变更工单组件和表头未保存前这些数据存放在哪里？最后查到这两数据放在调用出口函数参数表：component_table,header_table。
3.设计数据表结构:ZPCO02,记录更改，结构如下:
MANDT MANDT CLNT 3
AUFNR AUFNR_char 12
MATNR MATNR_char 18
FILED FIELDNAME_char 30
AENAM AENAM_char 12
LAEDA LAEDA DATS 8
TCODE TCODE_char 20
CHNID CDCHNGIND_char 1
HOSTIP_char20_char 20
HOST MSHOST2_char 32
VALUE_OLD_char20_char 20
VALUE_NEW_char20_char 20
TIMES CDUZEIT TIMS 6
指示符：替代项目
4.在EXIT_SAPLCOBT_001的ZXCO1U01写入代码，component_table中的数据跟RESB中的相应保护字段值对比，得出组件更改记录,表头更改记录由header_table与视图CAUFV对比而来。对于因工单总数更改导至组件数量更改的记录可以写一程序从ZPCO02里分开，开放给用户使用，其他一些更改情况请自行研究。代码如下：
-----------------------------------------------------------------------------------------------------------------
代码通过了User测试更正已传至生产系统，改善了保存时的速度，不用拿更改后的工单组件与RESB里的一个个对比了。
*&---------------------------------------------------------------------*
*& 包括 ZXCO1U01_*
*&---------------------------------------------------------------------*
TABLES:caufv,resb.
DATA: BEGIN OF old_resb OCCURS 0, "更改后未保存之RESB.
aufnr LIKE resb-aufnr,
posnr LIKE resb-posnr,
matnr LIKE resb-matnr,
rsnum LIKE resb-rsnum,
rspos LIKE resb-rspos,
xloek LIKE resb-xloek,
bdmng LIKE resb-bdmng,
objnr LIKE resb-objnr,
END OF old_resb.
DATA: BEGIN OF new_resb OCCURS 0. "数据库中的RESB.
INCLUDE STRUCTURE old_resb.
DATA: END OF new_resb.
DATA: BEGIN OF obj_resb OCCURS 0. "插入的RESB
INCLUDE STRUCTURE old_resb.
DATA: END OF obj_resb.
DATA: i_zpco02 LIKE zpco02 OCCURS 0 WITH HEADER LINE.
DATA: i_temp LIKE zpco02 OCCURS 0 WITH HEADER LINE.
DATA: iporg LIKE msxxlist-hostadr,
ipdec(16) TYPE c,
host(18) TYPE c.
DATA:_char1(20) TYPE c,
char2(20) TYPE c.
DATA: inst_flg TYPE c VALUE "N",
chan_had TYPE c VALUE "N".
* teco_flg TYPE c VALUE "N",
* read_flg TYPE c .
*BREAK-POINT.
CHECK sy-tcode EQ "CO02".
** Get user IP,hostname
CALL FUNCTION "TH_USER_INFO" " Get user IP,hostname
client = sy-mandt
user = sy-uname
hostaddr = iporg
terminal = host
EXCEPTIONS
OTHERS = 1.
**"Conv.IP addr to format "xxx.xxx.xxx.xxx"
CALL FUNCTION "GWY_IPADR2STRING" "Conv.IP addr
ipadr = iporg
string = ipdec.
***Common var.
MOVE: sy-mandt TO i_temp-mandt,
sy-uname TO i_temp-aenam,
sy-datum TO i_temp-laeda,
sy-uzeit TO i_temp-times,
sy-tcode TO i_temp-tcode,
ipdec TO i_temp-hostip,
host TO i_temp-host,
header_table-aufnr TO i_temp-aufnr.
***Check M/O header
**check if status eq "TECO".
*LOOP AT status_table WHERE objnr EQ header_table-objnr
* AND stat EQ "I0045".
* MOVE: "Y" TO teco_flg.
*CHECK teco_flg NE "Y".
*CHECK read_flg NE "Y".
_selectSINGLE gamng gltrp INTO (caufv-gamng, caufv-gltrp)
FROM caufv WHERE aufnr EQ header_table-aufnr.
IF header_table-gamng NE caufv-gamng.
MOVE: caufv-gamng TO_char1, header_table-gamng TO_char2.
MOVE-CORRESPONDING i_temp TO i_zpco02.
MOVE: "更改工单总数" TO i_zpco02-filed,
"U" TO i_zpco02-chnid,
char1 TO i_zpco02-value_old,
char2 TO i_zpco02-value_new.
APPEND i_zpco02.
inst_flg = "Y".
chan_had = "Y".
IF header_table-gltrp NE caufv-gltrp.
MOVE-CORRESPONDING i_temp TO i_zpco02.
MOVE: "更改完成时间" TO i_zpco02-filed,
"U" TO i_zpco02-chnid,
caufv-gltrp TO i_zpco02-value_old,
header_table-gltrp TO i_zpco02-value_new.
APPEND i_zpco02.
inst_flg = "Y".
IF inst_flg = "Y".
_insertzpco02 FROM TABLE i_zpco02 ACCEPTING DUPLICATE KEYS.
inst_flg = "N".
*_deletecomponent_table WHERE vbkz EQ "I".
LOOP AT component_table WHERE vbkz EQ "U"
OR vbkz EQ "I"
OR vbkz EQ "D".
CASE component_table-vbkz.
MOVE-CORRESPONDING i_temp TO i_zpco02.
MOVE: "新增组件" TO i_zpco02-filed,
"I" TO i_zpco02-chnid,
"" TO i_zpco02-value_old, "old_resb-matnr
component_table-matnr TO i_zpco02-value_new,
"新增的组件" TO i_zpco02-matnr,
* read_flg TO i_zpco02-readf,
component_table-alpos TO i_zpco02-alpos,
sy-tabix TO i_zpco02-loopid.
APPEND i_zpco02.
inst_flg = "Y".
MOVE-CORRESPONDING i_temp TO i_zpco02.
MOVE: "删除组件" TO i_zpco02-filed,
"D" TO i_zpco02-chnid,
resb-xloek TO i_zpco02-value_old,
component_table-xloek TO i_zpco02-value_new,
component_table-matnr TO i_zpco02-matnr,
* read_flg TO i_zpco02-readf,
component_table-alpos TO i_zpco02-alpos,
sy-tabix TO i_zpco02-loopid.
APPEND i_zpco02.
inst_flg = "Y".
_selectSINGLE matnr bdmng INTO (resb-matnr, resb-bdmng)
FROM resb WHERE aufnr EQ component_table-aufnr
AND posnr EQ component_table-posnr
AND matnr EQ component_table-matnr
AND rsnum EQ component_table-rsnum
AND rspos EQ component_table-rspos.
IF component_table-matnr NE resb-matnr.
MOVE-CORRESPONDING i_temp TO i_zpco02.
MOVE: "更改组件" TO i_zpco02-filed,
"U" TO i_zpco02-chnid,
resb-matnr TO i_zpco02-value_old,
component_table-matnr TO i_zpco02-value_new,
"更改的工单组件" TO i_zpco02-matnr,
* read_flg TO i_zpco02-readf,
component_table-alpos TO i_zpco02-alpos,
sy-tabix TO i_zpco02-loopid.
APPEND i_zpco02.
inst_flg = "Y".
ELSEIF component_table-bdmng NE resb-bdmng.
CHECK chan_had NE "Y".
MOVE: component_table-bdmng TO_char1, resb-bdmng TO_char2.
MOVE-CORRESPONDING i_temp TO i_zpco02.
MOVE: "需求数量" TO i_zpco02-filed,
"U" TO i_zpco02-chnid,
char2 TO i_zpco02-value_old,
char1 TO i_zpco02-value_new,
component_table-matnr TO i_zpco02-matnr,
* read_flg TO i_zpco02-readf,
component_table-alpos TO i_zpco02-alpos,
sy-tabix TO i_zpco02-loopid.
APPEND i_zpco02.
inst_flg = "Y".
IF inst_flg = "Y".
_insertzpco02 FROM TABLE i_zpco02 ACCEPTING DUPLICATE KEYS.
IF sy-subrc EQ 0.
inst_flg = "N".
chan_had = "N".
* MESSAGE ID "ZX" TYPE "W"
* NUMBER "000" WITH "SAP系统已记录您的更改!".
--------------------------------------------------------------------------------------------------------
5.更改记录:
字段名 删除标记
更改者 SAP003
上一次修改
事务代码 CO02
修改标识符 D
字符20 (HOSTIP) 192.168.4.95
Host Name SAP-8
字符20 (VALUE OLD)
字符20 (VALUE NEW) X
方法三：Tcode-aut10