Skip to main content

Conas meán a dhéanamh bunaithe ar lá na seachtaine in Excel?

doc-meán-le-seachtain-1

In Excel, an ndearna tú iarracht riamh meánuimhir liosta a ríomh ag brath ar cén lá den tseachtain? Ag rá, tá an raon sonraí seo a leanas agam, agus anois ba mhaith liom meán-orduithe a fháil gach Luan, lá oibre nó deireadh seachtaine. An t-alt seo, tabharfaidh mé isteach roinnt foirmlí chun cabhrú leat an tasc seo a réiteach.

Ríomh an meán bunaithe ar lá na seachtaine le foirmlí


mboilgeog cheart gorm saighead Ríomh an meán bunaithe ar lá na seachtaine le foirmlí

Ríomh an meán bunaithe ar lá faoi leith den tseachtain

Chun an meán a fháil bunaithe ar lá faoi leith den tseachtain, d’fhéadfadh an fhoirmle seo a leanas cabhrú leat. Sa sampla seo, ríomhfaidh mé meán-orduithe Dé Luain den raon sonraí, déan mar a leanas le do thoil:

Iontráil an fhoirmle seo le do thoil: =AVERAGE(IF(WEEKDAY(D2:D15)=2,E2:E15)) isteach i gcill bhán, agus ansin brúigh Shift + Ctrl + Iontráil eochracha le chéile chun an toradh ceart a fháil. Féach an pictiúr:

doc-meán-le-seachtain-2

Nóta: San fhoirmle thuas:

D2: D15 an raon dáta ar a bhfuil tú bunaithe;

An líon 2 Léiríonn Dé Luain, agus 1 = Dé Domhnaigh, 3 = Dé Máirt, 4 = Dé Céadaoin…, is féidir leat uimhir 2 a athrú de réir mar is gá duit;

E2: E15 tagraíonn sé don raon sonraí a theastaíonn uait an meán a fháil.

Leideanna: Is féidir leis an bhfoirmle seo a leanas cabhrú leat an fhadhb seo a réiteach: =SUMPRODUCT((WEEKDAY(D2:D15,2)=1)*E2:E15)/SUMPRODUCT((WEEKDAY(D2:D15,2)=1)*1) agus díreach brúigh Iontráil eochair chun an toradh a fháil. (D2: D15 an raon dáta ar a bhfuil tú bunaithe, E2: E15 tagraíonn sé don raon sonraí a theastaíonn uait an meán, an uimhir, a fháil 1 Léiríonn Dé Luain, 2 = Dé Máirt, 3 = Dé Céadaoin ...)


Ríomh an meán bunaithe ar laethanta oibre

Más mian leat na horduithe a mheánú le gach lá oibre sa raon, cuir an fhoirmle seo i bhfeidhm: =AVERAGE(IF(WEEKDAY(D2:D15,2)={1,2,3,4,5},E2:E15)), ansin brúigh Shift + Ctrl + Iontráil eochracha le chéile, agus gheobhaidh tú na gnáthorduithe ó Luan go hAoine.

doc-meán-le-seachtain-3

Nótaí:

1. Seo freisin go bhféadfadh foirmle eile fabhar a dhéanamh duit:=SUMPRODUCT((WEEKDAY(D2:D15,2)<6)*E2:E15)/SUMPRODUCT((WEEKDAY(D2:D15,2)<6)*1) agus brúigh Iontráil eochair.

2. Sna foirmlí thuas: D2: D15 an raon dáta ar a bhfuil tú bunaithe, agus E2: E15 tagraíonn sé don raon sonraí a theastaíonn uait an meán a fháil.


Ríomh an meán bunaithe ar an deireadh seachtaine

Agus mura dteastaíonn uait ach na horduithe a mheánú ag an deireadh seachtaine (Dé Sathairn agus Dé Domhnaigh), b’fhéidir go dtaitneodh an fhoirmle seo a leanas leat:

Clóscríobh an fhoirmle seo le do thoil: =AVERAGE(IF(WEEKDAY(D2:D15,2)={6,7},E2:E15)) isteach i gcill bhán ar leith, agus brúigh Shift + Ctrl + Iontráil eochracha ag an am céanna, agus ansin ní gheobhaidh tú ach na gnáthorduithe ag an deireadh seachtaine. Féach an pictiúr:

doc-meán-le-seachtain-4

Nótaí:

1. Is féidir leat an fhoirmle thíos a úsáid freisin chun an tasc seo a réiteach: =SUMPRODUCT((WEEKDAY(D2:D15,2)>5)*E2:E15)/SUMPRODUCT((WEEKDAY(D2:D15,2)>5)*1) agus díreach brúigh Iontráil eochair.

2. Sna foirmlí thuas: D2: D15 an raon dáta ar a bhfuil tú bunaithe, agus E2: E15 tagraíonn sé don raon sonraí a theastaíonn uait an meán a fháil.


Earraí gaolmhara:

Conas an meán idir dhá dháta in Excel a ríomh?

Conas cealla a mheánú bunaithe ar chritéir iolracha in Excel?

Conas meánluachanna barr nó bun 3 a fháil in Excel?

Uirlisí Táirgiúlachta Oifige is Fearr

🤖 Kutools AI Aide: anailís sonraí a réabhlóidiú bunaithe ar: Forghníomhú Chliste   |  Gin Cód  |  Cruthaigh Foirmlí Saincheaptha  |  Anailís a dhéanamh ar Sonraí agus Cairteacha a Ghin  |  Feidhmeanna Kutools a agairt...
Gnéithe Coitianta: Faigh, Aibhsigh nó Aithnigh Dúblaigh   |  Scrios Sraitheanna Bána   |  Comhcheangail Colúin nó Cealla gan Sonraí a Chailleadh   |   Babhta gan Foirmle ...
Cuardaigh Super: Ilchritéir VLookup    VLookup Illuachanna  |   VLookup Trasna Ilbhileoga   |   Amharc doiléir ....
Liosta anuas Casta: Go tapa Cruthaigh Liosta Anuas   |  Liosta anuas Cleithiúnach   |  Liosta Buail Isteach Ilroghnacha ....
Bainisteoir Colún: Cuir Líon Sonrach Colún leis  |  Colúin Bog  |  Scoránaigh Stádas Infheictheachta na gColún Ceilte  |  Déan comparáid idir Raonta & Colúin ...
Gnéithe Réadmhaoin: Fócas Eangaí   |  Amharc Dearaidh   |   Barra Mór na Foirmle    Leabhar Oibre & Bainisteoir Bileog   |  Leabharlann Acmhainní (Uaththéacs)   |  Piocálaí Dáta   |  Comhcheangail Bileoga Oibre   |  Criptigh/Díchriptigh Cealla    Seol Ríomhphost trí Liosta   |  Scagaire Super   |   Scagaire Speisialta (scagaire trom/iodálach/stailc tríd...) ...
Barr 15 Uirlisí12 Téacs uirlisí (Cuir Téacs, Bain Carachtair,...)   |   50 + Cairt cineálacha (Cairt Gantt,...)   |   40+ Praiticiúil Foirmlí (Ríomh aois bunaithe ar lá breithe,...)   |   19 Insertion uirlisí (Cuir isteach Cód QR, Ionsáigh Pictiúr ón gCosán,...)   |   12 Tiontú uirlisí (Uimhreacha le Focail, Comhshó Airgeadra,...)   |   7 Cumaisc & Scoilt uirlisí (Sraitheanna Comhcheangail Casta, Cealla Scoilt,...)   |   ... agus eile

Supercharge Do Scileanna Excel le Kutools le haghaidh Excel, agus Éifeachtúlacht Taithí Cosúil Ná Roimhe. Kutools le haghaidh Excel Tairiscintí Níos mó ná 300 Ardghnéithe chun Táirgiúlacht a Treisiú agus Sábháil Am.  Cliceáil anseo chun an ghné is mó a theastaíonn uait a fháil ...

Tuairisc


Tugann Tab Oifige comhéadan Tabbed chuig Office, agus Déan Do Obair i bhfad Níos Éasca

  • Cumasaigh eagarthóireacht agus léamh tabbed i Word, Excel, PowerPoint, Foilsitheoir, Rochtain, Visio agus Tionscadal.
  • Oscail agus cruthaigh cáipéisí iolracha i gcluaisíní nua den fhuinneog chéanna, seachas i bhfuinneoga nua.
  • Méadaíonn do tháirgiúlacht 50%, agus laghdaíonn sé na céadta cad a tharlaíonn nuair luch duit gach lá!
Comments (13)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
I have some data, that is in one day i have some order and with different quantity, how do i get the formula for average quantity in one singel date?
This comment was minimized by the moderator on the site
12-Dec-22 1 Week 51 Mon W-Day 86.85
13-Dec-22 1 Week 51 Tue W-Day 83.57
14-Dec-22 1 Week 51 Wed W-Day 89.67
15-Dec-22 1 Week 51 Thu W-Day 89.67
16-Dec-22 1 Week 51 Fri W-Day 85.45
17-Dec-22 1 Week 51 Sat PH 80.28
18-Dec-22 1 Week 51 Sun FH 85.45

I want the above data average for monday to friday(W-Day) average ,Saturday(PH) and Sunday(FH) average
This comment was minimized by the moderator on the site
Hello, Manigandan
If you need to average from Monday to Friday and average from Saturday and Sunday, please apply the below formulas:
Average from Monday to Friday: =AVERAGE(IF(WEEKDAY(A2:A8,2)={1,2,3,4,5},D2:D8))
Average from Saturday and Sunday: =AVERAGE(IF(WEEKDAY(A2:A8,2)={6,7},D2:D8))
Note: Both the above fromulas are array formulas, please press Shift + Ctrl + Enter keys together to get the correct result.
https://www.extendoffice.com/images/stories/comments/comment-skyyang/2023-comment/doc-average-workdays.png

Please have a try, hope it can help you!
This comment was minimized by the moderator on the site
How do I get my formula to work with it not taking zeros into the average?
=AVERAGE(IF(WEEKDAY($B$4:$B$34,2)={1,2,3,4,5},C4:C34))
This comment was minimized by the moderator on the site
Hello, Phil,
To get the average while skip the zeros, the following formula may help you:
=AVERAGE(IF((WEEKDAY($B$2:$B$14,2)<6)*($C$2:$C$14<>0)=1,$C$2:$C$14))

After pasting the formula, please press Ctrl + Shift + Enter keys together to get the result.

Please have a try, hope it can help you!
This comment was minimized by the moderator on the site
Yes, this works. Why does it work with such a large RANGE and not just for the number of days in a month?

Doing it my other way I had to manually change the formula's each month. THANK YOU 😀👊
This comment was minimized by the moderator on the site
I have this formula; {=AVERAGE(IF(WEEKDAY(I12:I25,2)={1,2,3,4,5},J12:J25))} the range: i12:i25(col 1 is the month date series) and j12:j25(col of values to be averaged) will change each day
11/1/2022 57
11/2/2022 45
11/3/2022 58
11/4/2022 51
11/5/2022 56
11/6/2022 65
11/7/2022 79
11/8/2022 80
11/9/2022 56
11/10/2022 60
11/11/2022 51
11/12/2022 59
11/13/2022 79
11/14/2022 76
11/15/2022 76
11/16/2022
11/17/2022
11/18/2022
11/19/2022
11/20/2022
11/21/2022
11/22/2022
11/23/2022
11/24/2022
11/25/2022
11/26/2022
11/27/2022
11/28/2022
11/29/2022
11/30/2022
12/1/2022
12/2/2022

Avg / M-F 61.3 < formula is here {=AVERAGE(IF(WEEKDAY(I12:I25,2)={1,2,3,4,5},J12:J25))}
avg # active each workday

how do i update the 2 ranges every day without manually changing them?
This comment was minimized by the moderator on the site
Hello, Daniel
To solve your problem, you just need to adjust the cell references large as this:
=AVERAGE(IF(WEEKDAY(I12:I100000,2)={1,2,3,4,5},J12:J100000))
Please have a try, hope it can help you!
This comment was minimized by the moderator on the site
if I use your formula for just weekends...=AVERAGE(IF(WEEKDAY(I12:I100000,2)={6,7},J12:J100000)) the value comes up as "0.0"
This comment was minimized by the moderator on the site
Hello, Daniel
Sorry, the above formula is not correct, plrase use the below formulas:
Average weekend: =AVERAGE(IF(IF(NOT(ISBLANK(B2:B10000)),WEEKDAY(A2:A10000,2)>5,FALSE),B2:B10000,FALSE))
Average workday: =AVERAGE(IF(IF(NOT(ISBLANK(B2:B10000)),WEEKDAY(A2:A10000,2)<6,FALSE),B2:B10000,FALSE))

Note: Both the formulas are array formulas, please press Ctrl + Shift + Enter keys together to get the result.

Please try, thank you!
This comment was minimized by the moderator on the site
I have this formula; {=AVERAGE(IF(WEEKDAY(I12:I25,2)={1,2,3,4,5},J12:J25))} the range: i12:i25(col 1 is the month date series) and j12:j25(col of values to be averaged) will change each day
11/1/2022 57
11/2/2022 45
11/3/2022 58
11/4/2022 51
11/5/2022 56
11/6/2022 65
11/7/2022 79
11/8/2022 80
11/9/2022 56
11/10/2022 60
11/11/2022 51
11/12/2022 59
11/13/2022 79
11/14/2022 76
11/15/2022 76
11/16/2022
11/17/2022
11/18/2022
11/19/2022
11/20/2022
11/21/2022
11/22/2022
11/23/2022
11/24/2022
11/25/2022
11/26/2022
11/27/2022
11/28/2022
11/29/2022
11/30/2022
12/1/2022
12/2/2022

Avg / M-F 61.3 < formula is here {=AVERAGE(IF(WEEKDAY(I12:I25,2)={1,2,3,4,5},J12:J25))}
avg # active each workday

how do i update the 2 ranges every day without manually changing them?
This comment was minimized by the moderator on the site
I found what I needed, why the Cntl Shift Enter to make formula work?
This comment was minimized by the moderator on the site
How is it that in the first formula 1 is Sunday but in the second and third formula 7 is Sunday??
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations