Ka papa inoa hāʻule me nā koho he nui

He maikaʻi ka papa inoa hāʻule iho i lalo i kahi pepa Excel, akā hiki iā ʻoe ke koho i hoʻokahi koho mai ka hoʻonohonoho i hōʻike ʻia. I kekahi manawa ʻo ia ka mea āu e makemake ai, akā aia nā kūlana e pono ai ka mea hoʻohana ke koho kekahi nā mea mai ka papa inoa.

E nānā kākou i kekahi mau hoʻokō maʻamau o ia papa inoa koho nui.

Koho 1. Horizontal

Koho ka mea hoʻohana i nā mea mai ka papa inoa hāʻule i lalo i kēlā me kēia, a ʻike ʻia lākou ma ka ʻaoʻao ʻākau o ke kelepona e hoʻololi ʻia, e hoʻopaʻa inoa ʻia ma ka pae ākea:

Hoʻokumu ʻia nā papa inoa hāʻule i lalo i nā cell C2:C5 i kēia hiʻohiʻona ma ke ʻano maʻamau, ʻo ia hoʻi

  1. koho i nā pūnaewele C2:C5
  2. papa kuhikuhi 'Ikepili koho i kahi hui Hōʻoia ʻikepili
  3. ma ka puka aniani e wehe ana, koho i kahi koho List a e kuhikuhi ma ke ʻano he laulā kahi nā pūnaewele me ka ʻikepili kumu no ka papa inoa A1:A8

A laila pono ʻoe e hoʻohui i kahi macro i ka module sheet, e hana i nā hana nui āpau, ʻo ia hoʻi e hoʻohui i nā waiwai i koho ʻia ma ka ʻākau o nā ʻōmaʻomaʻo. No ka hana ʻana i kēia, kaomi ʻākau ma ka ʻaoʻao pepa me nā papa inoa hāʻule i lalo a koho i ke kauoha Hoʻopā'ālua. Hoʻopili i kēia code i loko o ka puka makani hoʻoponopono Visual Basic e wehe ana:

Private Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next If Not Intersect(Target, Range("C2:C5")) is Nothing And Target.Cells.Count = 1 A laila Application.EnableEvents = False Inā Len(Target.Offset (0, 1)) = 0 A laila Target.Offset(0, 1) = Target Else Target.End(xlToRight).Offset(0, 1) = Target End If Target.ClearContents Application.EnableEvents = True End If End Sub

Inā pono, e hoʻololi i ka laulā koʻikoʻi o nā papa inoa hāʻule iho C2:C5 ma ka laina ʻelua o kēia code me kāu iho.

Koho 2. Vertical

E like me ka mea ma mua, akā ʻaʻole i hoʻohui ʻia nā waiwai hou i ka ʻākau, akā i lalo:

Hana ʻia ma ke ʻano like, akā hoʻololi iki ke code macro handler:

Private Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next If Not Intersect(Target, Range("C2:F2")) is Nothing and Target.Cells.Count = 1 A laila Application.EnableEvents = False Inā Len(Target.Offset (1, 0)) = 0 A laila Target.Offset(1, 0) = Target ʻEa Target.End(xlDown).Offset(1, 0) = Target Hope If Target.ClearContents Application.EnableEvents = True End If End Sub

Eia hou, inā pono, e hoʻololi i ka ʻāpana koʻikoʻi o ka papa inoa hāʻule iho C2:F2 me kāu iho ma ka laina ʻelua o kēia code.

Ke koho 3. Me ka hōʻiliʻili ʻana i loko o ka cell like

Ma kēia koho, loaʻa ka hōʻiliʻili i loko o ke kelepona hoʻokahi kahi i loaʻa ai ka papa inoa hāʻule. Hoʻokaʻawale ʻia nā mea i koho ʻia e kekahi ʻano i hāʻawi ʻia (no ka laʻana, kahi koma):

Hoʻokumu ʻia nā papa inoa hāʻule i lalo i nā ʻōmaʻomaʻo ma ke ʻano maʻamau, e like me nā ʻano ma mua. Hoʻopau hou ʻia nā hana a pau e kahi macro ma ka module sheet:

Private Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next If Not Intersect(Target, Range("C2:C5")) is Nothing And Target.Cells.Count = 1 A laila Application.EnableEvents = False newVal = Target Application.Undo oldval = Target Inā Len(oldval) <> 0 A oldval <> newVal Then Target = Target & "," & newVal Else Target = newVal End If If Len(newVal) = 0 Then Target.ClearContents Application.EnableEvents = True End If Hoʻopau Sub

Inā makemake ʻia, hiki iā ʻoe ke hoʻololi i ka mea hoʻokaʻawale (koma) ma ka laina 9 o ke code me kāu ponoʻī (e laʻa, kahi ākea a i ʻole semicolon).

  • Pehea e hana ai i kahi papa inoa hāʻule maʻalahi i kahi cell sheet excel
  • Ka papa inoa iho me ka ʻike
  • Hoʻohui ʻia ka papa inoa hāʻule me nā koho nalo
  • He aha nā macros, pehea e hoʻohana ai iā lākou, kahi e hoʻokomo ai i ka code macro i Visual Basic

Waiho i ka Reply