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

Hur definierar man intervall baserat på ett annat cellvärde i Excel?

Att beräkna ett värdeintervall är lätt för de flesta av Excel-användare, men har du någonsin försökt beräkna ett värdeintervall baserat på numret i en specifik cell? Till exempel finns det en kolumn med värden i kolumn A, och jag vill beräkna antalet värden i kolumn A baserat på värdet i B2, vilket betyder att om det är 4 i B2, kommer jag att genomsnitta de fyra första värdena i kolumn A som visas nedan. Nu introducerar jag en enkel formel för att snabbt definiera intervall baserat på ett annat cellvärde i Excel.
doc definierar intervall efter cellvärde 1

Definiera intervall baserat på cellvärde


pil blå höger bubbla Definiera intervall baserat på cellvärde

För att göra beräkningar för ett intervall baserat på ett annat cellvärde kan du använda en enkel formel.

Välj en tom cell som du kommer att lägga ut resultatet, ange denna formel = GENOMSNITT (A1: INDIRECT (CONCATENATE ("A", B2)))och tryck på ange för att få resultatet.
doc definierar intervall efter cellvärde 2

Notera:

1. I formeln är A1 den första cellen i kolumnen du vill beräkna, A är den kolumn du beräknar för, B2 är den cell du beräknar baserat på. Du kan ändra dessa referenser efter behov.

2. Om du vill göra en sammanfattning kan du använda den här formeln = SUM (A1: INDIREKT (SAMMANFATTNING ("A", B2))).

3. Om de första uppgifterna du vill definiera inte finns i första raden i Excel, till exempel i cell A2, kan du använda formeln så här: = GENOMSNITT (A2: INDIRECT (CONCATENATE ("A", ROW (A2) + B2-1))).
doc definierar intervall efter cellvärde 2


Räkna / summera celler snabbt efter bakgrund eller formatfärg i Excel

I vissa fall kan du ha ett antal celler med flera färger, och vad du vill är att räkna / summera värden baserat på samma färg, hur kan du snabbt beräkna?
Med Kutools för Excel's Räkna efter färg, kan du snabbt göra många beräkningar efter färg och också kan generera en rapport över det beräknade resultatet.  Klicka för gratis provperiod med alla funktioner 30 dagar!
dok räkna efter färg 1
 
Kutools för Excel: med mer än 300 praktiska Excel-tillägg, gratis att prova utan begränsningar i 30 dagar.

De bästa Office-produktivitetsverktygen

Kutools för Excel löser de flesta av dina problem och ökar din produktivitet med 80%

  • återanvändning: Sätt snabbt i komplexa formler, diagram och allt som du har använt tidigare; Kryptera celler med lösenord; Skapa e-postlista och skicka e-post ...
  • Super Formula Bar (enkelt redigera flera rader med text och formel); Läslayout (enkelt läsa och redigera ett stort antal celler); Klistra in i filtrerat intervall...
  • Sammanfoga celler / rader / kolumner utan att förlora data; Delat cellinnehåll; Kombinera duplicerade rader / kolumner... Förhindra duplicerade celler; Jämför intervall...
  • Välj Duplicera eller Unikt Rader; Välj tomma rader (alla celler är tomma); Super Find och Fuzzy Find i många arbetsböcker; Slumpmässigt val ...
  • Exakt kopia Flera celler utan att ändra formelreferens; Skapa referenser automatiskt till flera ark; Sätt in kulor, Kryssrutor och mer ...
  • Extrahera text, Lägg till text, ta bort efter position, Ta bort mellanslag; Skapa och skriva ut personsökningstalsatser; Konvertera mellan celler innehåll och kommentarer...
  • Superfilter (spara och tillämpa filterscheman på andra ark); Avancerad sortering efter månad / vecka / dag, frekvens och mer; Specialfilter av fet, kursiv ...
  • Kombinera arbetsböcker och arbetsblad; Sammanfoga tabeller baserat på nyckelkolumner; Dela data i flera ark; Batchkonvertera xls, xlsx och PDF...
  • Mer än 300 kraftfulla funktioner. Stöder Office / Excel 2007-2021 och 365. Stöder alla språk. Enkel implementering i ditt företag eller organisation. Fullständiga funktioner 30 dagars gratis provperiod. 60 dagars pengarna tillbaka-garanti.
kte-flik 201905

Fliken Office ger ett flikgränssnitt till Office och gör ditt arbete mycket enklare

  • Aktivera flikredigering och läsning i Word, Excel, PowerPoint, Publisher, Access, Visio och Project.
  • Öppna och skapa flera dokument i nya flikar i samma fönster, snarare än i nya fönster.
  • Ökar din produktivitet med 50 % och minskar hundratals musklick för dig varje dag!
officetab botten
Sortera kommentarer efter
Kommentarer (21)
Inga betyg än. Bli först med att betygsätta!
Denna kommentar minimerades av moderatoren på webbplatsen
Hej, tack så mycket för din hjälp, men jag har ett litet problem som är att jag inte bara har en kolumn utan flera (cirka 100 kolumner) finns det ett sätt att ändra formeln så att den passar förändringen i kolumner så O kan få summan för kolumner ABC och så vidare. tack för hjälpen
Denna kommentar minimerades av moderatoren på webbplatsen
Tack för ditt meddelande. Om du vill summera de första n värdena över flera kontinuerliga kolumner, ändra bara A till den sista kolumnen du använder. EG, summera de första 5 värdena över kolumn A till kolumn D, använd denna formel =SUMMA(A1:INDIREKT(CONCATENATE("D",F2))).
Denna kommentar minimerades av moderatoren på webbplatsen
Kan jag använda den här syntaxen när jag hänvisar till ett annat blad?
Denna kommentar minimerades av moderatoren på webbplatsen
Om du vill summera med hänvisning till ett annat ark i CONCATENATE-formeln skriv [ ARKNAMN+KOLUMN+RAD]
Formel=SUMMA(INDIREKT(CONCATENATE("Ark1!","D",1)):INDIREKT(KONKATERA("Blad1!","D",5)))
Denna kommentar minimerades av moderatoren på webbplatsen
Vad händer om jag vill räkna uppåt från en cell istället för nedåt? I exemplet ovan börjar formeln med A1 och går nedåt. Låt oss säga att jag vill ha ett intervall på 4 celler, som börjar med cell A10 och går till A6. Tack!
Denna kommentar minimerades av moderatoren på webbplatsen
Jared – Se mitt senaste inlägg för hur du löser detta.
Denna kommentar minimerades av moderatoren på webbplatsen
Jag har också det här uppåtgående problemet men kan inte se ditt andra inlägg för lösningen. Kan du ge mig länken till inlägget snälla? Tack så mycket!
Denna kommentar minimerades av moderatoren på webbplatsen
Hej, här är en formel som kan hjälpa dig att beräkna uppåt.
=MEDEL(INDIREKT(CONCATENATE("A",ANTAL(A:A)-B1+1)):INDIREKT(KONKATERA("A",ANTAL(A:A))))
A:A är kolumnen som du vill beräkna medelvärdena, B1 är cellen som definierar intervallet i kolumn A att beräkna.
Denna kommentar minimerades av moderatoren på webbplatsen
Tack, men kanske jag vet varför jag behöver använda "-B1+1"?
Denna kommentar minimerades av moderatoren på webbplatsen
Hej, Låt mig ta ett detaljerat exempel för att förklara det för dig. Det finns 10 rader i kolumn A, jag vill få de sista 2 värdenas medelvärde, med andra ord, medelvärde för värdena på rad 9 och rad 10. Nu innehåller B1 2, =COUNT(A:A)-B1+1 kommer få resultatet 9, COUNT(A:A) får resultatet 10 (den sista raden i kolumn A). Kombinera nu andra funktioner för att beräkna medelvärdena på rad 9 och rad 10. Hoppas detta kan hjälpa dig.
Denna kommentar minimerades av moderatoren på webbplatsen
Bra formel men varför fungerar det bara när man hänvisar till den första cellen. Med andra ord, om du flyttar hela intervallet nedåt och den första cellen i intervallet är A6 istället för A1, slutar formeln att fungera. Det verkar konstigt.
Denna kommentar minimerades av moderatoren på webbplatsen
Tidigare skrev jag: Bra formel men varför fungerar det bara när man hänvisar till den första cellen. Med andra ord, om du flyttar hela intervallet nedåt och den första cellen i intervallet är A5 istället för A1, slutar formeln att fungera.
Efter att ha lekt med det kom jag på varför formeln inte fungerar när startraden inte är rad 1. Excel tolkar detta som att intervallet är A1 till A4. Om du börjar med en annan rad än rad 1, så här ändrar du formeln förutsatt att intervallet nu börjar med A5:=MAX(A5:INDIREKT(CONCATENATE("A",RAD(A5)+B2)))
Detta fungerar eftersom sammanlänkningen ser radreferensen som fixerad i det första exemplet, men den blir relativ i det andra exemplet. Hoppas det hjälper!
Jared - Du kan bara subtrahera 4 istället för att lägga till. =MAX(A5:INDIREKT(CONCATENATE("A",RAD(A5)-B2))) [Antar att du har 4 i cell B2, eller så kan du bara lämna formeln som den är och sätta -4 i cell B2.]
Denna kommentar minimerades av moderatoren på webbplatsen
Hej, cathy, tack för ditt tillägg, jag har testat din formel, jag tror att den korrekta formeln kan vara =MAX(A5:INDIREKT(CONCATENATE("A",RAD(A5)+B2-1))). Jag har uppdaterat handledningen. Tack igen.
Denna kommentar minimerades av moderatoren på webbplatsen
Hej snabb fråga. Min formel =SUMIFS(DATA!$D$5:$D$13914,DATA!$E$5:INDIRECT(CONCATENATE("$E$",C3)),"<"&"1/1/2016") har ett intervall som är från en annan flik som heter "DATA". Jag får #VALUE! fel för denna formel. Några förslag? tack!
Denna kommentar minimerades av moderatoren på webbplatsen
Vad händer om ilskan inte är kolumn, utan en rad?
Denna kommentar minimerades av moderatoren på webbplatsen
Det är lite knepigare som jag nyss fick reda på. Du måste använda en kombination av CHAR-funktionen för att konvertera en siffra till en bokstav, CONCAT för att sätta ihop en bokstav och en siffra som består av en celladress, och INDIREKTA-funktionen för att känna igen strängar som celladresser.

I mitt exempel skrev jag följande uttryck:

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


där:
Row() är den aktuella raden
$B$31 är cellen där antalet kolumner som ska räknas lagras
5 är offset för den första kolumnen som ska räknas

om du behöver gå längre än kolumn Z, skulle du behöva en mindre intuitiv variant på det uttrycket med ADDRESS-funktionen:

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


där:
4 indikerar en relativ referens
Denna kommentar minimerades av moderatoren på webbplatsen
hur uppdaterar jag summaintervallet med hjälp av hjälpcellen...Till exempel: om initialsummansintervall är = C1 till M1, hur ändrar man summaintervallet om nya data är upp till AB1 med hjälp av en hjälparcell, istället för att ändra formeln manuellt ?
Denna kommentar minimerades av moderatoren på webbplatsen
Finns det något sätt att skapa en dynamisk avståndsvy som isolerar alla beställningar från en specifik klient inom en lista med klienter? Jag har tittat på flera videor och ingen gör faktiskt vad jag behöver den för att göra. Finns det något sätt att göra detta eller skulle jag behöva dra den nödvändiga informationen till en kontrollerad tabell och använda informationen från den tabellen?
Denna kommentar minimerades av moderatoren på webbplatsen
Hej CareTaker. Jag förstår inte din fråga klart. Kan du beskriva din fråga eller ladda upp en fil för att visa frågan i detalj? Men jag rekommenderar ett verktyg som heter Data Association för dig, kanske kan hjälpa dig, gå till den här webben för detaljer: https://www.extendoffice.com/product/kutools-for-excel/excel-click-cell-to-filter-based-on-another-workbook.html.
Denna kommentar minimerades av moderatoren på webbplatsen
Tack för svaret, det är ganska komplicerat att förklara men jag lyckades komma rätt till slut med hjälp av indexeringsformeln med matchningsformeln också.   =INDEX($B:$B;MATCH($C$2;$B:$B;0);):INDEX($N:$N;MATCH($C$2;$B:$B;0)+$C$3-1;)   först var det förvirrande men jag mår bra nu 
Denna kommentar minimerades av moderatoren på webbplatsen
Men om jag vill summera ett kolumnintervall (säg l7:l7800) utanför intervallet d7:ct9000 baserat på villkoret i kolumn a(a7:a9000) och kolumn b(b7:b9000). Hur ska jag göra det. Vänligen ge råd
Det finns inga kommentarer här ännu
Lämna dina kommentarer
Postar som gäst
×
Betygsätt detta inlägg:
0   Tecken
Föreslagna platser

Följ oss

Copyright © 2009 - www.extendoffice.com. | Alla rättigheter förbehållna. Drivs av ExtendOffice. | | Sitemap
Microsoft och Office-logotypen är varumärken eller registrerade varumärken som tillhör Microsoft Corporation i USA och / eller andra länder.
Skyddad av Sectigo SSL