Note: The other languages of the website are Google-translated. Back to English

Sut i ddiffinio ystod yn seiliedig ar werth cell arall yn Excel?

Mae cyfrifo ystod o werthoedd yn hawdd i'r mwyafrif o ddefnyddwyr Excel, ond a ydych erioed wedi ceisio cyfrifo ystod o werthoedd yn seiliedig ar y nifer mewn cell benodol? Er enghraifft, mae colofn o werthoedd yng ngholofn A, ac rwyf am gyfrifo nifer y gwerthoedd yng ngholofn A yn seiliedig ar y gwerth yn B2, sy'n golygu, os yw'n 4 yn B2, y byddaf yn cyfartaleddu'r 4 gwerth cyntaf yn colofn A fel y dangosir isod. Nawr rwy'n cyflwyno fformiwla syml i ddiffinio ystod yn gyflym yn seiliedig ar werth cell arall yn Excel.
doc diffinio ystod yn ôl gwerth cell 1

Diffinio ystod yn seiliedig ar werth celloedd


swigen dde glas saeth Diffinio ystod yn seiliedig ar werth celloedd

I gyfrifo am ystod yn seiliedig ar werth cell arall, gallwch ddefnyddio fformiwla syml.

Dewiswch gell wag y byddwch chi'n ei rhoi allan o'r canlyniad, nodwch y fformiwla hon = CYFARTAL (A1: INDIRECT (CONCATENATE ("A", B2))), a'r wasg Rhowch allwedd i gael y canlyniad.
doc diffinio ystod yn ôl gwerth cell 2

Nodyn:

1. Yn y fformiwla, A1 yw'r gell gyntaf yn y golofn rydych chi am ei chyfrifo, A yw'r golofn rydych chi'n cyfrif amdani, B2 yw'r gell rydych chi'n ei chyfrifo yn seiliedig arni. Gallwch chi newid y cyfeiriadau hyn yn ôl yr angen.

2. Os ydych chi am wneud crynodeb, gallwch ddefnyddio'r fformiwla hon = SUM (A1: INDIRECT (CONCATENATE ("A", B2))).

3. Os nad yw'r data cyntaf yr ydych am ei ddiffinio yn rhes gyntaf yn yr Excel, er enghraifft, yng nghell A2, gallwch ddefnyddio'r fformiwla fel hyn: = CYFARTAL (A2: INDIRECT (CONCATENATE ("A", ROW (A2) + B2-1))).
doc diffinio ystod yn ôl gwerth cell 2


Cyfrif / Swmio celloedd yn gyflym yn ôl cefndir neu liw fformat yn Excel

Mewn rhai achosion, efallai bod gennych chi ystod o gelloedd â lliwiau lluosog, a'r hyn rydych chi ei eisiau yw cyfrif / swm gwerthoedd yn seiliedig ar yr un lliw, sut allwch chi gyfrifo'n gyflym?
gyda Kutools ar gyfer Excel's Cyfrif yn ôl Lliw, gallwch chi wneud llawer o gyfrifiadau yn gyflym yn ôl lliw, a hefyd gallwch gynhyrchu adroddiad o'r canlyniad a gyfrifwyd.  Cliciwch ar gyfer treial llawn sylw am ddim mewn 30 diwrnod!
cyfrif doc yn ôl lliw 1
 
Kutools ar gyfer Excel: gyda mwy na 300 o ychwanegion Excel defnyddiol, am ddim i geisio heb unrhyw gyfyngiad mewn 30 diwrnod.

Yr Offer Cynhyrchedd Swyddfa Gorau

Mae Kutools for Excel yn Datrys y rhan fwyaf o'ch Problemau, ac yn Cynyddu Eich Cynhyrchedd 80%

  • Ailddefnyddio: Mewnosod yn gyflym fformwlâu cymhleth, siartiau ac unrhyw beth rydych chi wedi'i ddefnyddio o'r blaen; Amgryptio Celloedd gyda chyfrinair; Creu Rhestr Bostio ac anfon e-byst ...
  • Bar Fformiwla Gwych (golygu llinellau lluosog o destun a fformiwla yn hawdd); Cynllun Darllen (darllen a golygu nifer fawr o gelloedd yn hawdd); Gludo i'r Ystod Hidlo...
  • Uno Celloedd / Rhesi / Colofnau heb golli Data; Cynnwys Celloedd Hollt; Cyfuno Rhesi / Colofnau Dyblyg... Atal Celloedd Dyblyg; Cymharwch y Meysydd...
  • Dewiswch Dyblyg neu Unigryw Rhesi; Dewiswch Blank Rows (mae pob cell yn wag); Darganfyddiad Gwych a Darganfyddiad Niwlog mewn Llawer o Lyfrau Gwaith; Dewis ar Hap ...
  • Copi Union Celloedd Lluosog heb newid cyfeirnod fformiwla; Auto Creu Cyfeiriadau i Daflenni Lluosog; Mewnosod Bwledi, Blychau Gwirio a mwy ...
  • Testun Detholiad, Ychwanegu Testun, Tynnu yn ôl Swydd, Tynnwch y Gofod; Creu ac Argraffu Subtotals Paging; Trosi rhwng Cynnwys a Sylwadau Celloedd...
  • Hidlo Super (arbed a chymhwyso cynlluniau hidlo i ddalenni eraill); Trefnu Uwch yn ôl mis / wythnos / dydd, amlder a mwy; Hidlo Arbennig gan feiddgar, italig ...
  • Cyfuno Llyfrau Gwaith a Thaflenni Gwaith; Uno Tablau yn seiliedig ar golofnau allweddol; Rhannwch Ddata yn Daflenni Lluosog; Trosi Swp xls, xlsx a PDF...
  • Mwy na 300 o nodweddion pwerus. Yn cefnogi Swyddfa / Excel 2007-2019 a 365. Yn cefnogi pob iaith. Defnydd hawdd yn eich menter neu sefydliad. Nodweddion llawn treial am ddim 30 diwrnod. Gwarant arian yn ôl 60 diwrnod.
tab kte 201905

Mae Tab Office yn Dod â rhyngwyneb Tabbed i'r Swyddfa, a 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!
gwaelod officetab
sylwadau (21)
Dim sgôr eto. Byddwch y cyntaf i sgorio!
Lleihawyd y sylw hwn gan y safonwr ar y wefan
Helo, Diolch yn fawr iawn am eich help, ond mae gen i broblem fach sef nad oes gen i ddim ond un golofn ond sawl un (tua 100 colofn) a oes ffordd i newid y fformiwla i wneud lle i'r newid mewn colofnau felly O yn gallu cael y swm ar gyfer Colofnau ABC ac ati. Diolch am eich help
Lleihawyd y sylw hwn gan y safonwr ar y wefan
Diolch i chi am eich neges. Os ydych chi am grynhoi n gwerthoedd cyntaf ar draws colofnau di-dor lluosog, newidiwch A i'r golofn olaf rydych chi'n ei defnyddio. E.e., swm y 5 gwerth cyntaf ar draws colofn A i golofn D, cymhwyso'r fformiwla hon =SUM(A1:INDIRECT(CONCATENATE("D",F2))).
Lleihawyd y sylw hwn gan y safonwr ar y wefan
A allaf ddefnyddio'r gystrawen hon wrth gyfeirio at ddalen wahanol?
Lleihawyd y sylw hwn gan y safonwr ar y wefan
Os ydych am grynhoi gan gyfeirio at Shet Wahanol Yn fformiwla CONCATENATE ysgrifennwch [ ENW'R TAFLEN+COLOFN+RES]
Formula=SUM(INDIRECT(CONCATENATE("Taflen1!","D",1)):INDIRECT(CONCATENATE("Taflen1!","D",5)))
Lleihawyd y sylw hwn gan y safonwr ar y wefan
Beth os ydw i am gyfrif i fyny o gell yn hytrach nag i lawr? Yn yr enghraifft uchod, mae'r fformiwla yn dechrau gydag A1 ac yn mynd i lawr. Gadewch i ni ddweud fy mod eisiau ystod o 4 cell, gan ddechrau gyda Cell A10 ac yn mynd i A6. Diolch!
Lleihawyd y sylw hwn gan y safonwr ar y wefan
Jared - Gweler fy post diweddaraf am sut i ddatrys hyn.
Lleihawyd y sylw hwn gan y safonwr ar y wefan
Rwy'n cael y broblem ar i fyny hon hefyd ond ni allaf weld eich post arall ar gyfer yr ateb. Allwch chi roi dolen o'r post i mi os gwelwch yn dda? Diolch yn fawr!
Lleihawyd y sylw hwn gan y safonwr ar y wefan
Helo, dyma fformiwla a all eich helpu i gyfrifo i fyny.
= AVERAGE(INDIRECT(CONCATENATE("A", COUNT(A:A)-B1+1)): INDIRECT(CONCATENATE("A", COUNT(A:A)))))
A: A yw'r golofn rydych chi am ei chyfrifo'r gwerthoedd cyfartalog, B1 yw'r gell sy'n diffinio'r amrediad yng ngholofn A i'w gyfrifo.
Lleihawyd y sylw hwn gan y safonwr ar y wefan
Diolch, ond ga i wybod pam fod angen defnyddio "-B1+1" os gwelwch yn dda?
Lleihawyd y sylw hwn gan y safonwr ar y wefan
Helo, gadewch imi gymryd enghraifft fanwl i'w esbonio i chi. Mae 10 rhes yng ngholofn A, rwyf am gael cyfartaledd y 2 werth olaf, mewn geiriau eraill, cyfartaledd y gwerthoedd yn rhes 9 a rhes 10. Nawr mae B1 yn cynnwys 2, =COUNT(A:A)-B1+1 fydd cael y canlyniad 9, bydd COUNT(A:A) yn cael y canlyniad 10 (rhes olaf colofn A). Nawr cyfunwch swyddogaethau eraill i gyfartaleddu'r gwerthoedd yn rhes 9 a rhes 10. Gobeithio y gall hyn eich helpu.
Lleihawyd y sylw hwn gan y safonwr ar y wefan
Fformiwla wych ond pam mai dim ond wrth gyfeirio at y gell gyntaf y mae'n gweithio. Mewn geiriau eraill, os symudwch yr ystod gyfan i lawr a'r gell gyntaf yn yr ystod yw A6 yn lle A1, mae'r fformiwla yn peidio â gweithio. Mae hynny'n ymddangos yn rhyfedd.
Lleihawyd y sylw hwn gan y safonwr ar y wefan
Yn gynharach postiais: Fformiwla wych ond pam mai dim ond wrth gyfeirio at y gell gyntaf y mae'n gweithio. Mewn geiriau eraill, os symudwch yr ystod gyfan i lawr a'r gell gyntaf yn yr ystod yw A5 yn lle A1, mae'r fformiwla yn peidio â gweithio.
Ar ôl chwarae ag ef, fe wnes i ddarganfod pam nad yw'r fformiwla'n gweithio pan nad yw'r rhes gychwyn yn rhes 1. Mae Excel yn dehongli hyn fel yr ystod yw A1 i A4. Os ydych chi'n dechrau gyda rhes wahanol i res 1, dyma sut i addasu'r fformiwla gan dybio bod yr ystod bellach yn dechrau gydag A5:=MAX(A5:INDIRECT(CONCATENATE("A",ROW(A5)+B2))))
Mae hyn yn gweithio oherwydd bod y concatenation yn gweld y cyfeirnod rhes yn sefydlog yn yr enghraifft gyntaf, ond mae'n dod yn gymharol yn yr ail enghraifft. Gobeithio bod hynny'n helpu!
Jared - Gallwch dynnu 4 yn lle ychwanegu. =MAX(A5:INDIRECT(CONCATENATE("A",ROW(A5)-B2)))) [Gan dybio bod gennych chi 4 yng nghell B2, neu fe allech chi adael y fformiwla fel y mae a rhoi -4 yng nghell B2.]
Lleihawyd y sylw hwn gan y safonwr ar y wefan
Helo, Cathy, diolch am eich atodiad, rwyf wedi profi eich fformiwla, rwy'n meddwl efallai mai'r fformiwla gywir yw =MAX(A5:INDIRECT(CONCATENATE("A", ROW(A5)+B2-1))). Rwyf wedi diweddaru'r tiwtorial. Diolch eto.
Lleihawyd y sylw hwn gan y safonwr ar y wefan
Helo cwestiwn cyflym. Fy fformiwla =SUMIFS(DATA!$D$5:$D$13914,DATA!$E$5:INDIRECT(CONCATENATE("$E$",C3)),"<"&"1/1/2016") Mae ganddo ystod sydd o dab arall o'r enw "DATA." Rwy'n dal i gael #GWERTH! gwallau ar gyfer y fformiwla hon. Unrhyw awgrymiadau? diolch!
Lleihawyd y sylw hwn gan y safonwr ar y wefan
Beth os nad colofn yw'r cynddaredd, ond rhes?
Lleihawyd y sylw hwn gan y safonwr ar y wefan
Mae hynny ychydig yn anoddach fel y darganfyddais. Mae angen i chi ddefnyddio cyfuniad o'r ffwythiant CHAR i drosi rhif yn lythyren, CONCAT i lunio llythyren a rhif sy'n cynnwys cyfeiriad cell, a'r swyddogaeth INDIRECT i adnabod tannau fel cyfeiriadau cell.

Yn fy enghraifft, ysgrifennais yr ymadrodd canlynol:

=SUM(INDIRECT(CONCAT("F",ROW())):INDIRECT(CONCAT(CHAR(64+5+$B$31),ROW())))


lle:
Rhes() yw'r rhes gyfredol
$B$31 yw'r gell lle mae'r # o golofnau i'w cyfrif yn cael eu storio
5 yw'r gwrthbwyso ar gyfer y golofn gyntaf i'w chyfrif

os oes angen i chi fynd y tu hwnt i golofn Z, byddai angen amrywiad llai greddfol ar y mynegiad hwnnw gan ddefnyddio'r swyddogaeth ADDRESS:

=SUM(indirect(concat("F",row())):indirect(ADDRESS(row(),$B$31+5,4)))


lle:
Mae 4 yn nodi cyfeiriad cymharol
Lleihawyd y sylw hwn gan y safonwr ar y wefan
sut ydw i'n diweddaru ystod y swm trwy ddefnyddio cell helpwr...Er enghraifft: os yw ystod y swm cychwynnol yn = C1 i M1, sut i newid ystod y swm os yw data newydd hyd at AB1 gan ddefnyddio cell helpwr, yn lle newid y fformiwla â llaw ?
Lleihawyd y sylw hwn gan y safonwr ar y wefan
A oes ffordd i greu golygfa ystod ddeinamig, a fydd yn ynysu pob archeb gan gleient penodol o fewn rhestr o gleientiaid? Rwyf wedi edrych ar fideos lluosog ac nid oes yr un ohonynt yn gwneud yr hyn sydd ei angen arnaf i'w wneud. A oes ffordd i wneud hyn neu a fyddai angen i mi dynnu'r wybodaeth angenrheidiol i mewn i dabl rheoledig a defnyddio'r wybodaeth o'r tabl hwnnw?
Lleihawyd y sylw hwn gan y safonwr ar y wefan
Helo, Gofalwr. Nid wyf yn cael eich cwestiwn yn glir. A allech chi ddisgrifio'ch cwestiwn neu uwchlwytho ffeil i ddangos y cwestiwn yn fanwl? Ond rwy'n argymell cyfleustodau o'r enw Data Association i chi, efallai y gall eich helpu chi, ewch i'r we hon am fanylion: https://www.extendoffice.com/product/kutools-for-excel/excel-click-cell-to-filter-based-on-another-workbook.html.
Lleihawyd y sylw hwn gan y safonwr ar y wefan
Diolch am yr ymateb, mae'n beth eithaf cymhleth i'w egluro ond llwyddais i ddod yn gywir yn y diwedd gan ddefnyddio'r fformiwla mynegeio gyda'r fformiwla paru hefyd.   =INDEX($B:$B;MATCH($C$2;$B:$B;0);):INDEX($N:$N;MATCH($C$2;$B:$B;0)+$C$3-1;)   ar y dechrau roedd yn ddryslyd ond rwy'n dda nawr 
Lleihawyd y sylw hwn gan y safonwr ar y wefan
Ond Os ydw i eisiau crynhoi ystod colofn (dyweder l7: l7800) allan o'r ystod d7: ct9000 yn seiliedig ar yr amod yng ngholofn a(a7:a9000) a cholofn b(b7:b9000). Sut y byddaf yn ei wneud. Rhowch wybod
Nid oes unrhyw sylwadau wedi'u postio yma eto
Gadewch eich sylwadau
Postio fel Gwestai
×
Graddiwch y swydd hon:
0   Cymeriadau
Lleoliadau a Awgrymir