Skip i'r prif gynnwys

Sut i rifo colofn yn seiliedig ar werth celloedd ar golofn arall yn Excel?

doc-rhif-colofn-seiliedig-ar-werth-1
Er enghraifft, mae gennych ystod o ddata yn Excel, ac rydych chi am rifo colofn A yn seiliedig ar y gwerthoedd yng ngholofn B fel y dangosir isod. Nawr bydd y tiwtorial hwn yn dweud fformiwla wrthych i ddatrys y dasg hon yn Excel.
Rhif rhif colofn yn seiliedig ar werthoedd ar golofn arall

swigen dde glas saeth Rhif rhif colofn yn seiliedig ar werthoedd ar golofn arall

Yn ffodus, mae fformiwla sy'n gallu rhifo colofn yn gyflym yn seiliedig ar werthoedd colofn anther yn Excel.

1. Teipiwch 1 i mewn i gell gyntaf y golofn rydych chi am ei rhifo. Gweler y screenshot:

doc-rhif-colofn-seiliedig-ar-werth-2

2. Yn ail gell y golofn rydych chi am ei rhifo, teipiwch y fformiwla hon =IF(B1:B13="Total","",COUNTIF($A$1:A1,">0")+1), a gwasgwch Enter key.

doc-rhif-colofn-seiliedig-ar-werth-3

Tip: Yn y fformiwla uchod, B1: B13 yw'r ystod golofn rydych chi am ei rhifo yn seiliedig arni, ac A1 yw'r gell y gwnaethoch chi deipio 1 iddi, a “Cyfanswm” yw'r gwerth nad ydych chi am ei rifo.

3. Yna llusgwch y handlen autofill i'r ystod golofn rydych chi am ei rhifo. Gweler y screenshot:

doc-rhif-colofn-seiliedig-ar-werth-4

Erthyglau Perthynas:

Offer Cynhyrchiant Swyddfa Gorau

🤖 Kutools AI Aide: Chwyldro dadansoddi data yn seiliedig ar: Cyflawniad Deallus   |  Cynhyrchu Cod  |  Creu Fformiwlâu Personol  |  Dadansoddi Data a Chynhyrchu Siartiau  |  Invoke Swyddogaethau Kutools...
Nodweddion Poblogaidd: Darganfod, Amlygu neu Adnabod Dyblygiadau   |  Dileu Rhesi Gwag   |  Cyfuno Colofnau neu Gelloedd heb Colli Data   |   Rownd heb Fformiwla ...
Super-edrych: VLookup Meini Prawf Lluosog  |   VLookup Gwerth Lluosog  |   VLookup Ar Draws Taflenni Lluosog   |   Edrych Niwlog ....
Rhestr gwympo Uwch: Creu Rhestr Gollwng yn Gyflym   |  Rhestr Gollwng Dibynnol   |  Rhestr Gollwng Aml-ddewis ....
Rheolwr Colofn: Ychwanegu Nifer Penodol o Golofnau  |  Symud Colofnau  |  Toglo Statws Gwelededd Colofnau Cudd  |  Cymharwch Ystodau a Cholofnau ...
Nodweddion dan Sylw: Ffocws ar y Grid   |  Golwg Dylunio   |   Bar Fformiwla Mawr   |  Rheolwr Llyfr Gwaith a Thaflen   |  Llyfrgell Adnoddau (Testun Auto)   |  Dewiswr Dyddiad   |  Cyfuno Taflenni Gwaith   |  Amgryptio/Dadgryptio Celloedd   |  Anfon E-byst trwy Restr   |  Hidlo Super   |   Hidlo Arbennig (hidlo mewn print trwm/italig/strikethrough...) ...
15 Set Offer Gorau:  12 Testun offer (Ychwanegu Testun, Dileu Cymeriadau,...)   |   50 + Siart Mathau (Siart Gantt,...)   |   40+ Ymarferol Fformiwlâu (Cyfrifwch oedran yn seiliedig ar ben-blwydd,...)   |   19 mewnosod offer (Mewnosod Cod QR, Mewnosod Llun o'r Llwybr,...)   |   12 Trosi offer (Rhifau i Eiriau, Trosi arian cyfred,...)   |   7 Uno a Hollti offer (Rhesi Cyfuno Uwch, Celloedd Hollt,...)   |   ... a mwy

Supercharge Eich Sgiliau Excel gyda Kutools ar gyfer Excel, a Phrofiad Effeithlonrwydd Fel Erioed Erioed. Kutools ar gyfer Excel Yn Cynnig Dros 300 o Nodweddion Uwch i Hybu Cynhyrchiant ac Arbed Amser.  Cliciwch Yma i Gael Y Nodwedd Sydd Ei Angen Y Mwyaf...

Disgrifiad

Mae Office Tab yn dod â rhyngwyneb Tabbed i Office, ac yn Gwneud Eich Gwaith yn Haws o lawer

  • Galluogi golygu a darllen tabbed yn Word, Excel, PowerPoint, Cyhoeddwr, Mynediad, Visio a Phrosiect.
  • Agor a chreu dogfennau lluosog mewn tabiau newydd o'r un ffenestr, yn hytrach nag mewn ffenestri newydd.
  • Yn cynyddu eich cynhyrchiant 50%, ac yn lleihau cannoedd o gliciau llygoden i chi bob dydd!
Comments (11)
No ratings yet. Be the first to rate!
Anil Kumar D S
Anil Kumar D S
This comment was minimized by the moderator on the site
Hi Sir,

When Cell 1 is Number this FT20240001 and Next need to auto calculate FT20240002 ( FT20240001 +1) in If function.

Thanks
Reply
0
0
sv
sv
This comment was minimized by the moderator on the site
How do i take set values and add different varying values to it for projection.
Reply
0
0
Mark Zago
Mark Zago
This comment was minimized by the moderator on the site
Sales data and applying a sequential job number that grows by 1 for each entry?As sales data is entered into a table, at the time of the client acceptance a date stamp is applied to the relevant cell in that rowAcceptance is random and the row (client) to be next accepted is also random.
I have a Column of cells (Column A) partially populated by a numerical date range that increases as per the date
Without sorting the table.
How do I write a formula that will calculate a sequential whole number starting at 1 in the corresponding cellegcell A32020 Nov 15th 13:17:50 (time stamp)(equates to) 44150.5540549768
A10 next entry 2020 Nov 15th 11:48:16 (time stamp)44150.4918552083
A13 next entry
2020 Nov 15th 11:48:26 (time stamp)
44150.4919619213
etc
In the corresponding cellsC3 needs to return a value of 3C10 needs to return a value of 1C13 a value of 2etcgrowing by 1 each time
Any ideas?
Reply
0
0
Neeraj Nagpal
Neeraj Nagpal
This comment was minimized by the moderator on the site
This formula works but my first number based on the criteria is starting with 2 and then 3,4,5 onwards. How can I get it to start at 1?
Reply
0
0
Sunny
Sunny     Neeraj Nagpal
This comment was minimized by the moderator on the site
Hi, you can change the last argument 1 in the formula to 2, like =IF(B1:B13="Total","",COUNTIF($A$1:A1,">0")+2).
Reply
Report
0
0
Damphir
Damphir
This comment was minimized by the moderator on the site
Yo necesito hacer lo mismo que este ejemplo, pero en mi caso es requerido que me de un consecutivo segun un numero X en una columna, pero que si me varia el numero X en esa columna, me de un consecutivo diferente ej...

A B
1 1
2 1
3 1
1 2
2 2

Algo asi....
Reply
0
0
AG
AG     Damphir
This comment was minimized by the moderator on the site
In cell A3 paste the following

=IF(B3=B2,"",COUNTIF($A$2:A2,">0")+1)
Reply
0
0
sb
sb
This comment was minimized by the moderator on the site
How do i autonumber based on a value but in my case there is no "TOTAL". This is how my data is: UNKNOWN UNKNOWN UNKNOWN SEG034-PI-012.038 SEG034-PI-012.039 SEG034-PI-012.038 SEG034-PI-012.038 SEG034-PI-012.038 SEG034-PI-001.019 SEG034-PI-001.019 UNKNOWN UNKNOWN SEG034-PI-001.019 SEG034-PI-001.019 SEG034-PI-001.019 SEG034-PI-001.021 SEG034-PI-001.021 SEG034-PI-001.021 UNKNOWN UNKNOWN UNKNOWN UNKNOWN UNKNOWN UNKNOWN UNKNOWN UNKNOWN UNKNOWN UNKNOWN UNKNOWN UNKNOWN UNKNOWN UNKNOWN CT0034.0202.004 CT0034.0202.004 So i basically want to put number (ID), if the value repeats itll get the same value if it already has assigned (in the example data unknown repeats randomly). Please help.
Reply
0
0
Sean
Sean     sb
This comment was minimized by the moderator on the site
Hi there,
Hope this works. =IF(A3="";"";IF(IFERROR(VLOOKUP(A3;$A$2:B2;2;FALSE);"N/A")="N/A";MAX($B$2:B2+1);VLOOKUP(A3;$A$2:B2;2;FALSE)))
REMEMBER TO MAKE YOUR FIRST CELL ex. "B1", 1 THEN BELOW IT ADD THE FORMULA ex. "B2".


Reply
0
0
Brandon
Brandon     sb
This comment was minimized by the moderator on the site
Did you ever get an answer to this? I have the same question.
Reply
0
0
sanjay
sanjay     Brandon
This comment was minimized by the moderator on the site
Hi, I have similar question. I have a alphanumeric values of Column A, some times repeating some time unique.. So Base on the numerical values available in the Column A, I want the a serial number auto assigned in Column B as shown below.Can any help please?

COLUMN# A COLUMN# BWO/20-21/001 001-01WO/20-21/001 001-02
WO/20-21/001 001-03
WO/20-21/002 002-01
WO/20-21/002 002-02
WO/20-21/002 002-03
WO/20-21/002 002-04
WO/20-21/003 003-01


Reply
0
0
There are no comments posted here yet
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations