自制记账凭证的问题

scwjpyz · 发表于 2008-9-11 00:53

如下表，我想实现借贷方金额自动填入。当A列输入金额2,354,367.98时能实现这些数字分别自动填入B,C至 J  列。而当K列输入345.12时，能自动填入L至T列，且要求对应元、角、分。           A           B C D  E   F G  H I   J            K           L M N  O P Q  R S  T <table cellspacing="0" cellpadding="0" width="456" border="0" xstr="" style="WIDTH: 350pt; BORDER-COLLAPSE: collapse;"><colgroup><col width="100" style="WIDTH: 75pt; mso-width-source: userset; mso-width-alt: 3200;"></col><col span="8" width="14" style="WIDTH: 11pt; mso-width-source: userset; mso-width-alt: 448;"></col><col width="15" style="WIDTH: 11pt; mso-width-source: userset; mso-width-alt: 480;"></col><col width="2" style="WIDTH: 2pt; mso-width-source: userset; mso-width-alt: 64;"></col><col width="100" style="WIDTH: 75pt; mso-width-source: userset; mso-width-alt: 3200;"></col><col span="5" width="14" style="WIDTH: 11pt; mso-width-source: userset; mso-width-alt: 448;"></col><col width="14" style="WIDTH: 11pt; mso-width-source: userset; mso-width-alt: 448;"></col><col span="2" width="14" style="WIDTH: 11pt; mso-width-source: userset; mso-width-alt: 448;"></col><col width="15" style="WIDTH: 11pt; mso-width-source: userset; mso-width-alt: 480;"></col></colgroup><tbody><tr height="27" style="HEIGHT: 20.25pt; mso-height-source: userset;"><td class="xl39" width="100" height="54" rowspan="2" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 75pt; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 40.5pt; BACKGROUND-COLOR: transparent;">借方金额</td><td class="xl42" width="127" colspan="9" style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 99pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;">借方金额</td><td class="xl24" width="2" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 2pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;">　</td><td class="xl39" width="100" rowspan="2" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 75pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent;">贷方金额</td><td class="xl30" width="127" colspan="9" style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 99pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;">贷方金额</td></tr><tr height="27" style="HEIGHT: 20.25pt; mso-height-source: userset;"><td class="xl46" height="27" style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 20.25pt; BACKGROUND-COLOR: transparent;">百</td><td class="xl35" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;">十</td><td class="xl34" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;">万</td><td class="xl46" style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;">千</td><td class="xl35" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;">百</td><td class="xl34" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;">十</td><td class="xl36" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;">元</td><td class="xl37" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;">角</td><td class="xl34" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;">分</td><td class="xl25" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;">　</td><td class="xl46" style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;">百</td><td class="xl35" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;">十</td><td class="xl34" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;">万</td><td class="xl46" style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;">千</td><td class="xl26" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;">百</td><td class="xl34" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;">十</td><td class="xl36" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;">元</td><td class="xl37" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;">角</td><td class="xl26" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;">分</td></tr><tr height="32" style="HEIGHT: 24pt; mso-height-source: userset;"><td class="xl28" height="32" xnum="2354367.98" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 24pt; BACKGROUND-COLOR: transparent;">2,354,367.98</td><td class="xl45" style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;">　</td><td class="xl31" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;">　</td><td class="xl32" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;">　</td><td class="xl45" style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;">　</td><td class="xl31" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;">　</td><td class="xl32" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;">　</td><td class="xl33" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;">　</td><td class="xl38" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;">　</td><td class="xl32" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;">　</td><td class="xl27" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;">　</td><td class="xl28" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;">　345.12</td><td class="xl45" xnum="" style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;"></td><td class="xl31" xnum="" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;"></td><td class="xl32" xnum="" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;"></td><td class="xl45" xnum="" style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;"></td><td class="xl32" xnum="" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;"></td><td class="xl32" xnum="" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;"></td><td class="xl33" xnum="" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;"></td><td class="xl38" xnum="" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;"></td><td class="xl32" xnum="" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;"></td></tr></tbody></table>

一剑惊心 · 发表于 2008-9-11 07:34

L3=LEFT(RIGHT(" "&$K$3*100,COLUMNS(L3:$T$3)))

学良 · 发表于 2008-9-11 08:04

给个样子便于理解一剑惊心的公式:

vVrIr4dS.rar (3.47 KB, 下载次数: 74)

scwjpyz · 发表于 2008-9-11 08:44

谢谢学良大虾，问题解决。再次感谢

renjs60 · 发表于 2011-1-19 09:36

是不是要用EXCEL2007以上版本才能打开

		自动登录	找回密码
密码			注册

[问题] 自制记账凭证的问题