Excel NETWORKHOURS Spanning Midnight - Duel 191 MrExcel.com Italian Thai

Excel NETWORKHOURS Spanning Midnight - Duel 191YouTube↗ IA↗(long shot)
Howto & Style 1.3K views 73 likes 2 dislikes 14 Aug 2020

MrExcel.com 167K subscribers United States YouTube↗

Ehi, è proprio il momento per un altro podcast Dueling Excel.

เฮ้ได้เวลาแล้วสำหรับ Dueling Excel podcast อีก

Sono Bill Jelen di MrExcel. Sarò raggiunto da Mike Girvin di ExcelisFun.

ฉันชื่อ Bill Jelen จาก MrExcel ฉันจะเข้าร่วมโดย Mike Girvin จาก ExcelisFun

Questo è il nostro episodio # 191.

นี่คือตอนที่ # 191 ของเรา

NetWorkHours che copre la mezzanotte. Ehi, bentornato al podcast Dueling Excel.

NetWorkHours ช่วงเที่ยงคืน เฮ้ยินดีต้อนรับกลับสู่ Dueling Excel podcast

Questa è una domanda di cui sono sicuro che abbiamo affrontato anni fa.

นี่เป็นคำถามที่ฉันแน่ใจว่าเราเคยรับมือเมื่อหลายปีก่อน

Ma sarebbe interessante vedere se ci sono nuovi modi per farlo dato che abbiamo LET () e Power Query. Quindi, Mike la domanda oggi.

แต่มันน่าสนใจที่จะดูว่ามีวิธีใหม่ ๆ ในการทำหรือไม่เนื่องจากเรามี LET () และ Power Query ดังนั้นไมค์คำถามในวันนี้

Questo è di Sanjeev.

นี่มาจาก Sanjeev

Ha una data e un'ora di inizio e una data e un'ora di fine.

เขามีวันที่และเวลาเริ่มต้นและวันที่และเวลาสิ้นสุด

Ha bisogno di capire il numero di ore di lavoro tra i due.

เขาต้องการหาจำนวนชั่วโมงทำงานระหว่างสองคนนี้

L'azienda per cui lavora Sanjeev, i loro turni iniziano il lunedì alle 17:00 e vanno al martedì alle 4:00. Questo poi si ripete martedì, mercoledì, giovedì, venerdì.

บริษัท ที่ Sanjeev ทำงานกะของพวกเขาจะเริ่มในวันจันทร์เวลา 17.00 น. และไปถึงวันอังคารเวลา 04.00 น. จากนั้นจะเกิดซ้ำในวันอังคารพุธพฤหัสบดีศุกร์

Questa è la cosa che mi fa davvero impazzire perché significa che dobbiamo controllare il lunedì dalle 17:00 a mezzanotte e poi il martedì da mezzanotte alle 4:00.

นี่คือสิ่งที่ทำให้ฉันแทบคลั่งเพราะนั่นหมายความว่าเราต้องตรวจสอบวันจันทร์ตั้งแต่ 17.00 น. ถึงเที่ยงคืนและวันอังคารตั้งแต่เที่ยงคืนถึง 04.00 น.

Ci sono un sacco di cose che dobbiamo controllare qui e poi una piccola parte del sabato che dobbiamo lavorare - - dobbiamo includere come orario di lavoro. Adesso la complicazione.

มีหลายสิ่งหลายอย่างที่เราต้องตรวจสอบที่นี่จากนั้นส่วนเล็ก ๆ ของวันเสาร์ที่เราต้องทำงาน - - เราต้องรวมเป็นชั่วโมงทำงาน ตอนนี้ภาวะแทรกซ้อน

Sanjeev non ha Microsoft 365. Ha Excel 2016. Quindi niente LET o Dynamic Arrays.

Sanjeev ไม่มี Microsoft 365 เขามี Excel 2016 ดังนั้นจึงไม่มี LET หรือ Dynamic Arrays

Almeno quando lo stavo risolvendo.

อย่างน้อยก็ตอนที่กำลังแก้

E poi Sanjeev dice: "La mia azienda dice che tre ore e 36 minuti dovrebbero essere segnalate come 3,36" Bene, questo mi farà impazzire, eccome.

แล้ว Sanjeev ก็พูดว่า "บริษัท ของฉันบอกว่าควรรายงานเวลา 3 ชั่วโมง 36 นาทีเป็น 3.36" นั่นจะทำให้ฉันแทบคลั่ง

Quindi per prima cosa ho capito che avere turni che si estendono a mezzanotte potrebbe complicare tutto perché poi invece di soli cinque turni hai essenzialmente 10 turni perché li stai spezzando.

สิ่งแรกที่ฉันรู้ว่าการมีกะที่ช่วงเที่ยงคืนอาจทำให้ทุกอย่างซับซ้อนขึ้นเพราะแทนที่จะเป็นแค่ห้ากะคุณจะมี 10 กะเป็นหลักเพราะคุณแยกมันออกจากกัน

Il mio primo passo è: prenderò tutti i tempi di Sanjeev e sottrarrò quattro ore e un secondo da tutti loro.

ขั้นตอนแรกของฉันคือฉันจะใช้เวลาทั้งหมดของ Sanjeev และฉันจะลบสี่ชั่วโมงกับหนึ่งวินาทีออกจากพวกเขาทั้งหมด

Allora diventa davvero semplice.

แล้วมันจะกลายเป็นเรื่องง่ายจริงๆ

Ho solo cinque turni di cui devo preoccuparmi dal lunedì alle 15:00 a mezzanotte.

ฉันมีห้ากะที่ฉันต้องกังวลตั้งแต่วันจันทร์เวลา 15.00 น. ถึงเที่ยงคืน

E poi è solo dal lunedì al venerdì. Quindi ribadirò questo Mike.

แล้วก็แค่วันจันทร์ถึงวันศุกร์ ดังนั้นฉันจะพูดใหม่ไมค์นี้

Questa è la mia domanda più semplice.

นี่เป็นคำถามที่ง่ายกว่าของฉัน

Abbiamo bisogno di una formula per calcolare quante ore sono comprese tra le 1500 e la mezzanotte dal lunedì al venerdì.

เราต้องการสูตรเพื่อหาจำนวนชั่วโมงระหว่าง 1500 ถึงเที่ยงคืนของวันจันทร์ถึงวันศุกร์

NETWORKDAYS () è tornato in gioco perché ora non dobbiamo preoccuparci del sabato e della domenica.

NETWORKDAYS () กลับมาเล่นอีกครั้งเพราะตอนนี้เราไม่ต้องกังวลเรื่องวันเสาร์และวันอาทิตย์

Tuttavia, sarebbe davvero bello se avessimo una funzione NETWORKHOURS.

แม้ว่ามันจะดีมากถ้าเรามีฟังก์ชัน NETWORKHOURS

Le regole del duello: usare qualcosa nella Beta di Microsoft 365 Insider quindi LET?

กฎสำหรับการดวล: ใช้อะไรก็ได้ในเบต้าของ Microsoft 365 Insider งั้นเหรอ?

Assolutamente! XLOOKUP? Fallo! Array dinamici? Sì.

แน่นอน! XLOOKUP? ไปเลย! อาร์เรย์แบบไดนามิก? ใช่.

e per quanto riguarda il rapporto 3.36 significa 3 ore e 36 minuti. È solo una sciocchezza.

และเท่าที่รายงาน 3.36 หมายถึง 3 ชั่วโมง 36 นาที เป็นเพียงเรื่องไร้สาระ

Quello che farò è: risolverò questo problema con un formato numerico personalizzato di parentesi h parentesi punto MM, quindi continueremo a segnalarlo come tempo.

สิ่งที่ฉันจะทำคือ: ฉันจะแก้ไขด้วยรูปแบบตัวเลขที่กำหนดเองของวงเล็บ h วงเล็บดอท MM ดังนั้นเราจะยังคงรายงานสิ่งนี้เมื่อเวลาผ่านไป

Apparirà solo come vuole il manager di Sanjeev. Tutto ok. Quindi ecco il mio inizio.

มันจะเป็นไปตามที่ผู้จัดการของ Sanjeev ต้องการให้เป็น เอาล่ะ. นี่คือจุดเริ่มต้นของฉัน

Abbiamo questi diversi esempi.

เรามีตัวอย่างต่างๆเหล่านี้

Sanjeev mi ha dato alcuni di questi per farmi inciampare.

Sanjeev ให้สิ่งเหล่านี้กับฉันเพื่อเดินทางขึ้น

Ho aggiunto altro per gestire tutte le altre cose che potrebbero farci inciampare. Il mio piano: primo passo.

ฉันเพิ่มมากขึ้นเพื่อจัดการกับสิ่งอื่น ๆ ทั้งหมดที่สามารถเดินทางเราได้ แผนของฉัน: ขั้นตอนที่หนึ่ง

Torna indietro di quattro ore. Passo 2.

สำรองข้อมูลทุกอย่างภายในสี่ชั่วโมง ขั้นตอนที่ 2.

Suddividi il giorno dall'ora utilizzando la funzione INT (). Passaggio 3. È lo stesso giorno?

แบ่งวันออกจากเวลาโดยใช้ฟังก์ชัน INT () ขั้นตอนที่ 3. เป็นวันเดียวกันหรือไม่?

Sembra un caso più semplice. Quindi, prima voglio essere in grado di gestirlo.

ดูเหมือนจะเป็นกรณีที่ง่ายกว่า ดังนั้นฉันต้องการที่จะจัดการก่อน

E ora, dopo il fatto, mi chiedo se questa decisione abbia complicato le cose. Se avessi dovuto ignorarlo.

และหลังจากความจริงแล้วฉันสงสัยว่าการตัดสินใจครั้งนี้ซับซ้อนหรือไม่ ถ้าฉันควรจะเพิกเฉยต่อสิ่งนี้

Quindi non voglio che questo offuschi il tuo giudizio, Mike. Passaggio 4.

ดังนั้นฉันไม่อยากให้เรื่องนี้ทำให้การตัดสินของคุณขุ่นมัวไมค์ ขั้นตอนที่ 4.

Rompi il tempo della giornata usando MOD (, 1). Passaggio 5. Gestire il caso semplice dello stesso giorno.

แบ่งเวลาจากวันโดยใช้ MOD (, 1) ขั้นตอนที่ 5. จัดการกรณีง่ายๆของวันเดียวกัน

È l'ora di fine meno l'ora di inizio.

เวลาสิ้นสุดลบด้วยเวลาเริ่มต้น

Ma se l'ora di inizio è prima dell'inizio della giornata lavorativa, si desidera utilizzare l'ultima di queste due. Quindi il massimo di quelli.

แต่ถ้าเวลาเริ่มต้นอยู่ก่อนวันงานเริ่มคุณต้องการใช้เวลาสองอย่างในภายหลัง ดังนั้นค่าสูงสุดของสิ่งเหล่านั้น

Passaggio 6. Quindi, se non è lo stesso giorno, quanti giorni di lavoro interi ci sono?

ขั้นตอนที่ 6. ถ้าไม่ใช่วันเดียวกันจะมีวันทำงานทั้งหมดกี่วัน?

Quindi moltiplicalo per nove ore al giorno. Passaggio 7. Quindi quante ore il primo giorno?

แล้วคูณด้วยเก้าชั่วโมงต่อวัน ขั้นตอนที่ 7. แล้ววันแรกกี่ชั่วโมง?

Quindi, è mezzanotte meno l'ora di inizio ma di nuovo l'ora di inizio o il 1500, a seconda di quale è successiva. Passaggio 8.

นั่นคือเวลาเที่ยงคืนลบเวลาเริ่มต้น แต่เวลาเริ่มต้นอีกครั้งหรือ 1500 แล้วแต่ว่าอย่างใดจะช้ากว่า ขั้นตอนที่ 8.

Per l'ultimo giorno è l'ora di fine meno 14.59 ma non potrebbe mai essere negativa.

สำหรับวันสุดท้ายเวลาสิ้นสุดลบ 14.59 แต่จะไม่เป็นลบ

Non so perché avrebbero mai finito prima della giornata lavorativa. Posso immaginare qualcosa in cui qualcuno rimane oltre le quattro del mattino.

ฉันไม่รู้ว่าทำไมพวกเขาถึงจบลงก่อนวันทำงาน ฉันนึกภาพออกว่ามีใครบางคนเข้าพักในช่วงสี่ทุ่ม

Non lo so. Chissà. Passaggio 9. Somma tutte le volte.

ฉันไม่รู้ ใครจะรู้. ขั้นตอนที่ 9. รวมเวลาทั้งหมด

Formatta con [H] .MM. Tutto a posto.

ฟอร์แมตด้วย [H] .MM. Alright

Quindi esaminerò queste formule molto rapidamente.

ดังนั้นฉันจะเรียกใช้สูตรเหล่านี้อย่างรวดเร็ว

Prendi il tempo originale meno quattro ore e un secondo. Prendi l'ora di fine originale meno quattro ore e un secondo.

ใช้เวลาเดิมลบสี่ชั่วโมงหนึ่งวินาที ใช้เวลาสิ้นสุดเดิมลบสี่ชั่วโมงหนึ่งวินาที

Abbastanza semplice. Questo è il passaggio 1. Facile! Passo 2.

ง่ายพอ ขั้นตอนที่ 1. ง่ายนิดเดียว! ขั้นตอนที่ 2.

Calcola l'inizio corretto di una data. Quindi sbarazzarsi del tempo.

คำนวณการเริ่มต้นที่ปรับแล้วเป็นวันที่ ดังนั้นการกำจัดเวลา

Eliminare le 14:59 e eseguire il backup a 0.00.

กำจัด 14:59 น. และสำรองข้อมูลเป็น 0.00

Prendo INT () la funzione intera di entrambi. Passaggio 3. È lo stesso giorno?

ฉันใช้ INT () ฟังก์ชันจำนวนเต็มของทั้งสองอย่าง ขั้นตอนที่ 3. เป็นวันเดียวกันหรือไม่?

Quindi questo F9 è uguale a G9?

แล้ว F9 นี้เท่ากับ G9 หรือเปล่า?

Se fossi in grado di usare LET, questo sarebbe davvero utile, penso. Va bene passaggio 4.

ถ้าฉันสามารถใช้ LET สิ่งนี้จะมีประโยชน์มากฉันคิดว่า เอาล่ะขั้นตอนที่ 4

Rompi il tempo della giornata. Quindi proprio qui abbiamo la parte della data.

แบ่งเวลาออกจากวัน ตรงนี้เรามีส่วนวันที่

Oh, sai ora, perché non ho appena sottratto F9 da D9? Sarebbe stato più facile.

อ้อรู้แล้วทำไมไม่ลบ F9 ออกจาก D9 นั่นจะง่ายกว่า

Ma invece. Ho usato la funzione MOD perché mi fa sembrare più geek, immagino.

แต่กลับกัน ฉันใช้ฟังก์ชั่น MOD เพราะมันทำให้ฉันดูเท่ขึ้นฉันเดา

Il MOD (D9,1) si sbarazza di intere giornate e ci dà solo il tempo. E l'ora della fine. Va bene ora il caso facile.

MOD (D9,1) กำจัดทั้งวันและให้เวลากับเรา และเวลาสิ้นสุด เอาล่ะกรณีง่าย ๆ

A me sembra facile. Se questo è lo stesso giorno.

สำหรับฉันมันดูเหมือนง่าย ถ้าเป็นวันเดียวกัน.

Se H9 è TRUE, prenderemo l'ora di fine meno l'ora di inizio.

ถ้า H9 เป็น TRUE เราจะใช้เวลาสิ้นสุดลบด้วยเวลาเริ่มต้น

Ma vogliamo assicurarci che l'ora di fine non sia mai maggiore di 23:59:59.

แต่เราต้องการให้แน่ใจว่าเวลาสิ้นสุดจะไม่เกิน 23:59:59 น.

E dobbiamo assicurarci che l'ora di inizio non sia mai precedente alle 14 59:59.

และเราต้องตรวจสอบให้แน่ใจว่าเวลาเริ่มต้นไม่เคยเร็วกว่า 14 59:59 น.

Vedi, ce ne sono solo alcuni che finiscono il giorno dell'inizio.

คุณจะเห็นว่ามีเพียงบางส่วนเท่านั้นที่เสร็จสิ้นในวันเริ่มต้น

Ma per quelli, Wow, abbiamo finito. Passaggio 6. Giorni interi.

แต่สำหรับสิ่งนั้นว้าวเราทำเสร็จแล้ว ขั้นตอนที่ 6. ตลอดทั้งวัน

Cerchiamo di calcolare il numero di giorni lavorativi netti dalla data precedente alla data successiva.

เราพยายามหาจำนวนวันทำงานสุทธิจากวันก่อนหน้าถึงวันหลัง

Se ci pensi. Diciamo che lunedì è arrivato qualcosa.

ถ้าคุณคิดเกี่ยวกับมัน. สมมติว่ามีบางอย่างเข้ามาในวันจันทร์

Ci hanno lavorato una parte di lunedì. Tutto martedì. Tutto il mercoledì e poi parte il giovedì.

พวกเขาทำงานในส่วนนี้ของวันจันทร์ ทุกวันอังคาร ทั้งหมดของวันพุธและบางส่วนของวันพฤหัสบดี

NETWORKDAYS tornerà quattro giorni. Quindi devo sottrarre due da quello.

NETWORKDAYS กำลังจะกลับมาอีกสี่วัน ผมจึงต้องลบสองตัวจากนั้น

Quindi assicurati di non ottenere mai un numero di giorni negativo.

จากนั้นตรวจสอบให้แน่ใจว่าฉันไม่เคยติดลบจำนวนวัน

Se è arrivato lo stesso giorno, finirò con un 1 negativo.

ถ้ามันเข้ามาในวันเดียวกันฉันจะต้องติดลบ 1 ที่นั่น

Quindi moltiplicalo per 9 ore in questo modo.

แล้วคูณด้วย 9 ชั่วโมงแบบนั้น

Destra. Quindi, sono i giorni interi. Ho quasi finito. Ora il primo giorno è ...

ขวา. นั่นคือตลอดทั้งวัน ฉันเกือบเสร็จแล้ว วันแรกคือ ...

quindi, se non è lo stesso giorno, prendi fondamentalmente mezzanotte meno l'ora in cui è arrivata, anche se mai prima delle 14:59.

ดังนั้นหากไม่ใช่วันเดียวกันให้ใช้เวลาเที่ยงคืนลบด้วยเวลาที่เข้ามาแม้ว่าจะไม่เร็วกว่า 14:59 น.

Per l'ultimo giorno è l'ora in cui hanno finito meno l'ora di inizio o 14:59.

สำหรับวันสุดท้ายเวลาที่พวกเขาทำเสร็จแล้วลบเวลาเริ่มต้นหรือ 14:59 น.

E poi semplicemente aggiungi tutto.

จากนั้นก็เพิ่มทุกอย่าง

Quindi K9 più L9 più M9 più N9. E per rendere felice il capo di Sanjeev: Ctrl + 1. Utilizza un formato numerico personalizzato di [h] .mm.

ดังนั้น K9 บวก L9 บวก M9 บวก N9 และเพื่อให้เจ้านายของ Sanjeev มีความสุข: Ctrl + 1 ใช้รูปแบบตัวเลขที่กำหนดเองของ [h] .mm.

Normalmente ci metteremmo i due punti.

โดยปกติเราจะใส่ลำไส้ใหญ่ไว้ที่นั่น

Inizialmente ho pensato di provare effettivamente a capire il numero di ore e il numero di minuti e di costruire qualcosa che sembrasse giusto, ma poi non verrà totale correttamente.

เดิมทีฉันคิดว่าจะพยายามหาจำนวนชั่วโมงจำนวนนาทีและสร้างสิ่งที่ดูถูกต้อง แต่มันจะไม่รวมอย่างถูกต้อง

Abbiamo davvero bisogno di questo per poter totalizzare correttamente.

เราต้องการสิ่งนี้จริงๆเพื่อให้สามารถรวมได้อย่างถูกต้อง

Quindi, questa è fondamentalmente la mia risposta.

นั่นคือคำตอบของฉัน

Ma, sfortunatamente, non voglio rimandarlo a Sanjeev per dirgli che devi usare tutte queste colonne tutto il tempo.

แต่น่าเสียดายที่ฉันไม่ต้องการส่งสิ่งนี้กลับไปยัง Sanjeev และบอกเขาว่าคุณต้องใช้คอลัมน์เหล่านี้ทั้งหมดตลอดเวลา

Quindi questo è solo l'Excel di base che combina le formule.

ดังนั้นนี่เป็นเพียงพื้นฐานของ Excel ที่รวมสูตร

Quindi, combina il primo e l'ultimo giorno in un'unica formula.

ดังนั้นให้รวมวันแรกและวันสุดท้ายเป็นสูตรเดียว

Vedi, abbiamo avuto SE NON H9 che era per il primo giorno e la stessa cosa per l'ultimo giorno.

ดูว่าเรามี IF NOT H9 ซึ่งเป็นวันแรกและวันสุดท้ายเหมือนกัน

Quindi ora è solo una semplice formula se non h9 quindi fai la parte rossa fai la parte blu. Quindi abbiamo una colonna in meno.

ตอนนี้มันเป็นแค่สูตรง่ายๆถ้าไม่ใช่ h9 ให้ทำส่วนสีแดงทำส่วนสีน้ำเงิน เราจึงมีคอลัมน์น้อยกว่าหนึ่งคอลัมน์

Metti la Fine rettificata nella formula successiva.

ใส่ Adjusted End ในสูตรในภายหลัง

Quindi, era stato in E9. Era quella formula.

นั่นคือใน E9 มันเป็นสูตรนั้น

È referenziato due volte.

มีการอ้างอิงสองครั้ง

L'ora di fine si riferiva a E9 e l'ora di fine rettificata si riferiva a E9.

เวลาสิ้นสุดหมายถึง E9 และเวลาสิ้นสุดที่ปรับแล้วหมายถึง E9

Ora ho preso quella formula E9 e l'ho inserita in quelle due formule.

ตอนนี้ฉันเอาสูตร E9 นั้นมาใส่ลงในสองสูตรนั้น

Quindi spostare l'inizio regolato nella data di inizio e ora di inizio regolate. Non ho intenzione di affrontare tutto questo.

จากนั้นย้าย Adjusted Start ไปยังวันที่เริ่มต้นและเวลาเริ่มต้นที่ปรับแล้ว ฉันจะไม่ผ่านสิ่งเหล่านี้ทั้งหมด

Ottieni il succo di base di quello che sta succedendo qui.

คุณได้รับสาระสำคัญพื้นฐานของสิ่งที่เกิดขึ้นที่นี่

Espandi la formula dello stesso giorno.

ขยายสูตรวันเดียวกัน

Elimina lo stesso giorno: Combina IF.

ลบวันเดียวกัน: รวม IF

Rendi la prima e l'ultima formula la clausola Value_If_False. Nello stesso giorno.

ทำให้สูตรแรกและสูตรสุดท้ายเป็นประโยค Value_If_False ในวันเดียวกัน

Questo era quello in cui avevamo una formula in cui diciamo che se è lo stesso giorno, fallo altrimenti zero.

นี่คือสูตรหนึ่งที่เรามีสูตรที่เราบอกว่าเป็นวันเดียวกันหรือไม่ให้ทำอย่างนั้นเป็นศูนย์

E poi un'altra formula sta dicendo che se NON è lo stesso giorno, allora fallo altrimenti zero.

แล้วอีกสูตรก็บอกว่าถ้าไม่ใช่วันเดียวกันให้ทำอย่างนั้นเป็นศูนย์

Quindi ho preso la clausola blu e la clausola rossa e le ho combinate in un'unica grande formula mostruosa.

ดังนั้นฉันจึงเอาประโยคสีน้ำเงินและประโยคสีแดงมารวมกันเป็นสูตรมอนสเตอร์ตัวใหญ่ตัวเดียว

Elimina la colonna Data di inizio modificata. Era stato in D9. Deve andare per l'intera giornata.

ลบคอลัมน์วันที่เริ่มต้นที่ปรับแล้ว ที่เคยอยู่ใน D9. จะต้องเข้าสู่ตลอดทั้งวัน

Quindi prendiamo quella clausola e la inseriamo dove era D9. Otteniamo quella formula.

เราก็เลยเอาอนุประโยคนั้นไปวางในตำแหน่งที่ D9 อยู่ เราได้สูตรนั้น

Elimina la data di fine modificata. Quella clausola va in D9.

ลบวันที่สิ้นสุดที่ปรับปรุง อนุประโยคนั้นไปหาร D9

La colonna D si sposta a sinistra di una colonna. Ecco perché sembra sempre che stiamo lavorando su D9.

คอลัมน์ D กำลังเลื่อนไปทางซ้ายหนึ่งคอลัมน์ นั่นเป็นเหตุผลที่ดูเหมือนว่าเรากำลังทำงานกับ D9 อยู่เสมอ

Elimina l'ora di inizio che era stata in D9.

ลบเวลาเริ่มต้นที่เคยอยู่ใน D9

Quindi quella clausola blu va dentro e là.

ประโยคสีน้ำเงินนั้นก็ไปตรงนั้นและตรงนั้น

Otteniamo quella formula lunga per un nuovo "stesso giorno + primo e ultimo".

เราได้รับสูตรยาวสำหรับ "วันเดียวกัน + วันแรกและวันสุดท้าย" ใหม่

Stessa cosa: elimina l'ora di fine.

สิ่งเดียวกัน: ลบเวลาสิ้นสุด

Quella formula va dentro e fuori dallo schermo. E poi la formula finale.

สูตรนั้นจะอยู่ตรงนั้นและปิดหน้าจอ แล้วก็สูตรสุดท้าย

Prendiamo lo stesso giorno + il primo e l'ultimo, più i giorni interi.

เราใช้เวลาวันเดียวกัน + วันแรกและวันสุดท้ายรวมทั้งวัน

Li stavo aggiungendo in una nuova colonna.

ฉันได้เพิ่มสิ่งเหล่านี้ในคอลัมน์ใหม่

Basta sommarli in un'unica grande formula come questa. Sai, e poi mi piace quell'email che mi rispondi.

เพียงเพิ่มพวกเขาในสูตรใหญ่เช่นนี้ รู้แล้วฉันชอบอีเมลที่คุณเขียนตอบกลับมา

"Ehi Sanjeev. Prova questa formula qui"., Come se fossi in grado di scrivere una formula del genere.

"เฮ้ Sanjeev ลองใช้สูตรนี้ที่นี่" ราวกับว่าฉันสามารถเขียนสูตรแบบนั้นได้

E ai vecchi tempi prima di Power Query, prima di LET, prima di Dynamic Arrays, penso che questo fosse più o meno il modo in cui dovevamo farlo.

และในสมัยก่อนก่อน Power Query ก่อน LET ก่อน Dynamic Arrays ฉันคิดว่านี่เป็นวิธีที่เราต้องทำ

Va bene, quindi Mike ora avanziamo rapidamente da Excel 2016 a quello che abbiamo oggi.

เอาล่ะตอนนี้ Mike ขอก้าวต่อจาก Excel 2016 สู่สิ่งที่เรามีในวันนี้

Cosa puoi inventare? [Mike Girvin:] Grazie, MrExcel! Wow, questo è un modo folle per avere a che fare con il tempo.

คุณคิดอะไรได้บ้าง? [Mike Girvin:] ขอบคุณ MrExcel! ว้าวเป็นวิธีที่บ้ามากที่ต้องทำเวลา

Vorrei che avessero fogli di tempo individuali con ogni giorno.

ฉันหวังว่าพวกเขาจะมีใบบันทึกเวลาส่วนตัวในแต่ละวัน

Ora, la cosa più interessante della tua soluzione è stato il semplice fatto che hai sottratto quattro ore per ottenere le date in un giorno.

ตอนนี้สิ่งที่ยอดเยี่ยมที่สุดในการแก้ปัญหาของคุณคือความจริงง่ายๆที่คุณลบสี่ชั่วโมงเพื่อให้ได้วันที่เป็นหนึ่งวัน

Questo lo rende davvero molto più semplice.

นั่นทำให้ง่ายขึ้นมาก

Ora, l'ho fatto in singoli pezzi prima di eseguire la funzione LET. E ho preso l'intera colonna.

ตอนนี้ฉันทำทีละชิ้นก่อนที่จะทำฟังก์ชัน LET และฉันก็เอาทั้งคอลัมน์

Invece di usare la funzione TIME, ho sottratto un valore temporale (4 diviso 24).

แทนที่จะใช้ฟังก์ชัน TIME ฉันลบค่าเวลา (4 หารด้วย 24)

L'ho fatto per entrambe le colonne.

ฉันทำเช่นนั้นสำหรับทั้งสองคอลัมน์

Quindi ci serviva solo la data, quindi abbiamo usato INT su tutta la colonna. E si è rovesciato. L'abbiamo fatto lì.

จากนั้นเราต้องการเพียงวันที่ดังนั้นเราจึงใช้ INT กับทั้งคอลัมน์ และมันก็ทะลักลงมา เราทำที่นั่น

Abbiamo controllato i due array fuoriusciti. Sono gli stessi? Quindi usiamo il MOD.

เราตรวจสอบอาร์เรย์ที่รั่วไหลทั้งสอง พวกเดียวกันหรือเปล่า? จากนั้นเราใช้ MOD

Ottiene solo il valore temporale.

ซึ่งได้รับเพียงค่าเวลา

Abbiamo calcolato il numero di giorni lavorativi esclusi sabato e domenica.

เราคำนวณจำนวนวันทำงานไม่รวมวันเสาร์และวันอาทิตย์

E poiché dobbiamo eseguire calcoli individuali il giorno di inizio (che ha un'ora di inizio) e il giorno di fine quando c'è più di un giorno, ne abbiamo sottratti due.

และเนื่องจากเราต้องคำนวณแต่ละวันในวันเริ่มต้น (ซึ่งมีเวลาเริ่มต้น) และวันสิ้นสุดเมื่อมีมากกว่าหนึ่งวันเราจึงลบสอง

Per il tempo del primo giorno siamo andati avanti e abbiamo verificato che fosse VERO?

ในวันแรกที่เราดำเนินการต่อไปและตรวจสอบว่าเป็นจริงหรือไม่?

Ciò significa che c'è solo un giorno e invece di usare MIN e MAX insieme, ho controllato il valore dell'ora di fine.

นั่นหมายความว่ามีเวลาเพียงวันเดียวแทนที่จะใช้ MIN และ MAX ร่วมกันฉันตรวจสอบค่าเวลาสิ้นสุด

Per favore, dammi quello a metà tra 24 ore (che è 1), 15/24 o il valore effettivo.

โปรดระบุค่าที่อยู่ตรงกลางระหว่าง 24 ชั่วโมง (ซึ่งก็คือ 1), 15/24 หรือค่าจริง

Quindi ho sottratto il MAX dell'ora di inizio o 15/24.

จากนั้นฉันก็ลบ MAX ของเวลาเริ่มต้นหรือ 15/24

Quindi uno zero viene depositato nella cella quando c'è più di un giorno. Tempo per i giorni feriali. Ho controllato che i giorni feriali siano meno di uno.

จากนั้นศูนย์จะถูกฝากไว้ในเซลล์เมื่อมีมากกว่าหนึ่งวัน เวลาสำหรับวันธรรมดา ฉันตรวจสอบแล้วว่าวันธรรมดาน้อยกว่าหนึ่งวัน

Se è vero di zero.

ถ้าเป็นจริงมากกว่าศูนย์

Altrimenti, prendi in questo modo 5 volte il numero massimo di ore che puoi lavorare che è 9 diviso 24.

มิฉะนั้นให้ใช้เวลาแบบนี้ 5 ตรงนี้กับจำนวนชั่วโมงสูงสุดที่คุณสามารถทำงานได้ซึ่งก็คือ 9 หารด้วย 24

Tempo per il primo giorno. Sono andato avanti e ho controllato è lo stesso?

เวลาสำหรับวันแรก ไปตรวจสอบก่อนว่าเหมือนเดิมไหม

Se è solo un giorno, allora vogliamo zero qui.

ถ้าเป็นเพียงวันเดียวเราต้องการศูนย์ที่นี่

Altrimenti dobbiamo prendere 24 ore e sottrarre non l'ora di inizio ma il massimo dell'ora di inizio o 15/24.

มิฉะนั้นเราจะต้องใช้เวลา 24 ชั่วโมงและไม่ได้ลบเวลาเริ่มต้น แต่เป็นเวลาสูงสุดของเวลาเริ่มต้นหรือ 15/24

L'ultimo giorno è simile ma abbiamo a che fare solo con l'ora di fine, quindi prendiamo il massimo dell'ora di fine o 15/24 e poi sottraiamo 15/24. Quindi possiamo aggiungere le quattro parti.

วันสุดท้ายคล้ายกัน แต่เรากำลังจัดการกับเวลาสิ้นสุดเท่านั้นดังนั้นเราจึงใช้เวลาสูงสุดของเวลาสิ้นสุดหรือ 15/24 แล้วลบ 15/24 จากนั้นเราสามารถเพิ่มสี่ส่วน

Mettendo tutto insieme in LET ().

รวมทั้งหมดเข้าด้วยกันใน LET ()

Bene, LET ci permette di definire una variabile C'è il nome, e poi definire quella variabile.

เอาล่ะให้เรากำหนดตัวแปรมีชื่อแล้วกำหนดตัวแปรนั้น

Possiamo usare quella variabile per tutto il resto della formula.

เราจะใช้ตัวแปรนั้นตลอดส่วนที่เหลือของสูตร

E deve calcolare solo una volta. Ore massime.

และจะต้องคำนวณครั้งเดียวเท่านั้น ชั่วโมงสูงสุด

Regola il tempo verso il basso di quattro ore. Quindi ho definito la data di inizio modificata.

ปรับเวลาลงสี่ชั่วโมง จากนั้นฉันกำหนดวันที่เริ่มต้นที่ปรับปรุงแล้ว

C'è l'unico riferimento di cella a cui ci riferiamo.

มีการอ้างอิงเซลล์เดียวที่เราอ้างถึง

Meno le quattro ore. Stessa cosa per End. Quindi solo Data di inizio. C'è l'INT ().

ลบสี่ชั่วโมง สิ่งเดียวกันสำหรับ End จากนั้นเพียงแค่วันที่เริ่มต้น มี INT ()

Solo data di fine. Usiamo INT (). Solo ora di inizio. Uso il MOD (). Solo l'ora della fine.

วันที่สิ้นสุด เราใช้ INT () เพียงแค่เริ่มเวลา ฉันใช้ MOD () เวลาสิ้นสุด

Uso di nuovo MOD (). Poi abbiamo fatto il nostro test logico.

ฉันใช้ MOD () อีกครั้ง จากนั้นเราทำการทดสอบเชิงตรรกะของเรา

L'inizio è uguale alla data di fine?

เริ่มต้นเท่ากับวันที่สิ้นสุดหรือไม่?

Quindi abbiamo calcolato il numero di giorni della settimana e ne abbiamo sottratto due. Poi il tempo in cui c'è solo un giorno.

จากนั้นเราคำนวณจำนวนวันธรรมดาและลบสอง แล้วเวลาที่มีเพียงวันเดียว

Diciamo IF e usiamo la nostra variabile SameDay.

เราพูดว่า IF และเราใช้ตัวแปร SameDay

Quindi il MEDIAN guarda 1, 15/24 o EndTime.

จากนั้น MEDIAN จะดูที่ 1, 15/24 หรือ EndTime

Questo ci fornisce l'ora di fine corretta.

นั่นทำให้เรามีเวลาสิ้นสุดที่ถูกต้อง

Quindi sottraiamo l'ora di inizio corretta.

จากนั้นเราจะลบเวลาเริ่มต้นที่ถูกต้อง

MAX del nostro JustStartTime o 15/24, altrimenti zero. Poi il TimeForWeekdays.

MAX ของ JustStartTime หรือ 15/24 มิฉะนั้นจะเป็นศูนย์ จากนั้น TimeForWeekdays

Diciamo SE il numero di giorni della settimana è inferiore a 1, quindi 0.

เราบอกว่าจำนวน IF ของวันธรรมดาน้อยกว่า 1 แล้วจึงเป็น 0

Altrimenti numero di giorni feriali moltiplicato per 9 diviso per 24, (le ore massime).

มิฉะนั้นจำนวนของวันธรรมดาคูณ 9 หารด้วย 24 (จำนวนชั่วโมงสูงสุด)

TimeForFirstDay: Se SameDay in questo caso se esce TRUE vogliamo zero altrimenti 24 ore meno il MAX del nostro 15/24 o JustStartTime.

TimeForFirstDay: ถ้า SameDay ในกรณีนี้ถ้ามันออกมาเป็น TRUE เราต้องการเป็นศูนย์เป็นอย่างอื่น 24 ชั่วโมงลบ MAX ของ 15/24 หรือ JustStartTime ของเรา

TimeForLastDay eseguiamo il nostro stesso test logico.

TimeForLastDay เราทำการทดสอบตรรกะเดียวกัน

Altrimenti l'ora di fine corretta è il MAX di 15/24 o JustEndTime e quindi sottraiamo quel 15/24.

มิฉะนั้นเวลาสิ้นสุดที่ถูกต้องคือ MAX 15/24 หรือ JustEndTime จากนั้นเราจะลบ 15/24 นั้น

Infine, TotalHours.

สุดท้าย TotalHours

Aggiungiamo tutto e come unica variabile non definita in tutta questa formula, visualizzeremo solo l'ultima variabile definita. Quindi lo inseriamo e lo copiamo.

เราเพิ่มทุกอย่างและเป็นตัวแปรเดียวที่ไม่ได้กำหนดในสูตรทั้งหมดนี้เราจะแสดงตัวแปรที่กำหนดสุดท้ายนั้น จากนั้นเราใส่และคัดลอกลง

E poi, ovviamente, usiamo la formattazione del numero di tempo con le parentesi quadre. Questo è stato selvaggio.

แน่นอนว่าเราใช้การจัดรูปแบบตัวเลขเวลานั้นกับวงเล็บเหลี่ยม นี่เป็นป่า

Ho dovuto chiedere aiuto a MrExcel.

ฉันต้องขอความช่วยเหลือจาก MrExcel

Costruisci tutti i pezzi nel foglio e poi schiaccia tutto insieme. Perché guarda questo: quella è UNA colonna.

สร้างชิ้นส่วนทั้งหมดในแผ่นแล้วบดเข้าด้วยกัน เพราะดูสิ่งนี้นั่นคือหนึ่งคอลัมน์

È un mucchio di colonne. Va bene, lo restituirò a MrExcel.

นั่นคือกลุ่มคอลัมน์ ได้เลยฉันจะส่งกลับไปให้ MrExcel

[Bill Jelen:] Questa è una formula selvaggia. La funzione LET () è una novità di Microsoft 365.

[Bill Jelen:] นั่นคือสูตรป่า ฟังก์ชัน LET () เป็นฟังก์ชันใหม่ใน Microsoft 365

Mentre guardo la mia formula e la tua formula, Mike.

ขณะที่ฉันดูสูตรของฉันและสูตรของคุณไมค์

In ogni caso, a chiunque Sanjeev consegni la sua cartella di lavoro.

ไม่ว่าจะด้วยวิธีใดก็ตามใครก็ตามที่ Sanjeev มอบสมุดงานของเขาให้

Forse Sanjeev passa a un altro lavoro e tra due anni.

บางที Sanjeev อาจจะย้ายไปทำงานอื่นและอีกสองปีนับจากนี้

La prossima persona odierà sia la mia soluzione che la tua soluzione perché sono follemente lunghe.

คนต่อไปจะเกลียดทั้งวิธีแก้ปัญหาของฉันและวิธีแก้ปัญหาของคุณเพราะมันยาวมาก

Ma quello che mi è veramente piaciuto è stato il tuo uso di MEDIAN.

แต่สิ่งที่ฉันชอบมากคือการใช้ MEDIAN ของคุณ

Per evitare il mio problema di min / max nidificato.

เพื่อหลีกเลี่ยงปัญหา Min / Max ที่ซ้อนกันของฉัน

Il tuo prende solo quello al centro. È davvero brillante.

คุณเพิ่งได้รับหนึ่งที่อยู่ตรงกลาง ที่ยอดเยี่ยมจริงๆ

E ciò di cui abbiamo davvero bisogno qui è invece di NETWORKDAYS è NETWORKHOURS. È divertente.

และสิ่งที่เราต้องการจริงๆคือแทนที่จะเป็น NETWORKDAYS คือ NETWORKHOURS มันสนุกมาก.

Sono andato su Google. Ho cercato la funzione NETWORKHOURS Excel.

ฉันออกไปหา Google ฉันค้นหาฟังก์ชัน NETWORKHOURS Excel

E mentre c'erano diversi successi.

และในขณะที่มีเพลงฮิตหลายครั้ง

Uno dei successi è stato proprio nella bacheca di MrExcel. Tornando al 2013.

หนึ่งในเพลงฮิตอยู่ที่กระดานข้อความ MrExcel ย้อนกลับไปในปี 2013

SHG e Barry Houdini usano quel trucco MEDIANO.

SHG และ Barry Houdini ใช้เคล็ดลับ MEDIAN

È uno di cui non ero a conoscenza.

มันเป็นสิ่งที่ฉันไม่ทราบ

Dovrò provare a metterlo via per la prossima volta.

ฉันจะต้องพยายามเก็บมันไว้ในครั้งต่อไป

Sai, Mike, penso che questo sia uno di quelli in cui qualcuno che guarda questo video dirà. "Cosa state facendo ragazzi?

คุณรู้ไหมไมค์ฉันคิดว่านี่เป็นหนึ่งในคนที่ดูวิดีโอนี้จะพูด “ พวกนายทำอะไรน่ะ?

"C'è qualcosa di incredibilmente facile con Power Query o con gli array dinamici.

"มีบางอย่างที่ง่ายอย่างไม่น่าเชื่อด้วย Power Query หรืออาร์เรย์แบบไดนามิก

Avevo un'idea di un array dinamico che avrebbe cercato ogni minuto e una sorta di versione di gamma di XLOOKUP o VLOOKUP per verificare se ogni minuto contava o meno.

ฉันมีความคิดเกี่ยวกับ Dynamic Array ที่จะค้นหาทุกนาทีและเวอร์ชันช่วงของ XLOOKUP หรือ VLOOKUP เพื่อตรวจสอบว่าแต่ละนาทีถูกนับหรือไม่

Ma ho seguito questo metodo qui.

แต่ฉันมุ่งหน้าไปที่วิธีนี้ที่นี่

Comunque, Mike: è stato fantastico fare un altro podcast Dueling Excel.

อย่างไรก็ตาม Mike: การทำ Dueling Excel podcast อีกรายการเป็นเรื่องที่ดี

Dovremo iniziare a renderlo una cosa regolare.

เราจะต้องเริ่มทำให้สิ่งนี้เป็นประจำ

Se qualcuno che guarda vede una soluzione migliore, annotalo nei commenti di YouTube.

หากใครดูแล้วเห็นวิธีแก้ปัญหาที่ดีกว่าโปรดจดไว้ในความคิดเห็นของ YouTube

Dacci le tue idee su come renderlo più semplice.

ให้แนวคิดของคุณเกี่ยวกับวิธีทำให้ง่ายขึ้น

Voglio ringraziare Sanjeev per avermi inviato questa domanda. Voglio ringraziarti per essere passato.

ฉันอยากขอบคุณ Sanjeev ที่ส่งคำถามนี้เข้ามาฉันอยากขอบคุณที่แวะเข้ามา

Ci vediamo la prossima volta per un altro podcast Dueling Excel da MrExcel ed ExcelisFun.

แล้วพบกันใหม่สำหรับ Dueling Excel Podcast จาก MrExcel และ ExcelisFun

[♫♫ Rullo di tamburi ♫] [♫] Formule? o VBA?

[♫♫ Drumroll ♫] [♫] สูตร? หรือ VBA?

[♫] [♫] Ctrl + Maiusc + Invio o una modalità Macro?

[♫] [♫] Ctrl + Shift + Enter หรือวิธีมาโคร?

[♫] [♫] Beh, c'è Mike G sul C.S.E.

[♫] [♫] ก็มี Mike G ใน C.S.E.

[♫] [♫] Billy J scende con il VBA! [♫] [♫] Oh mio! [♫] [♫] È il duello di Excel, ragazzi! [♫] [♫] Attendi! Sono i ragazzi di Duelin 'Excel! [♫] [♫] Mio mio! È l'ora del duello di Excel! [♫] [applausi]

[♫] [♫] บิลลี่เจลง VBA! [♫] [♫] โอ้! [♫] [♫] มันคือ Duelin 'Excel Guys! [♫] [♫] ยืนด้วย! มันคือพวก Duelin 'Excel! [♫] [♫] ของฉัน! ถึงเวลา Duelin 'Excel! [♫] [เสียงปรบมือ]