למד כיצד להעתיק נוסחה לתאים מרובים, למטה בעמודה, לתאים שאינם סמוכים, להעתיק נוסחאות עם הפניות מוחלטות או מעורבות לתאים וכו'.
העתקת נוסחאות היא אחת המשימות הנפוצות והקלות ביותר שאתה עושה בגיליון אלקטרוני טיפוסי המסתמך בעיקר על נוסחאות. במקום להקליד את אותה נוסחה שוב ושוב ב-Excel, אתה יכול פשוט להעתיק ולהדביק נוסחה מתא אחד למספר תאים.
לאחר כתיבת נוסחה ב-Excel, תוכל להשתמש בפקודות העתק והדבק לתאים מרובים, תאים מרובים שאינם סמוכים או עמודות שלמות. אם לא תעשה את זה כמו שצריך, בסופו של דבר יהיו לך שגיאות # REF ו-/DIV0 נוראיות. במאמר זה, נראה לך את השיטות השונות בהן תוכל להשתמש כדי להעתיק נוסחאות ב-Excel.
כיצד להעתיק ולהדביק נוסחאות באקסל
Microsoft Excel מספק דרכים שונות להעתקת נוסחאות עם הפניות תאים יחסיות, הפניות תא מוחלטות או הפניות מעורבות.
- העתק נוסחה מתא אחד למשנהו
- העתק נוסחה אחת לתאים מרובים
- העתקת נוסחה לכל העמודה
- העתקת נוסחה ללא עיצוב
- העתק נוסחאות לתאים שאינם סמוכים
- העתק נוסחאות מבלי לשנות הפניות לתאים
כיצד להעתיק נוסחה מתא אחד לאחר באקסל
לפעמים תרצה להעתיק נוסחה מתא אחד למשנהו באקסל כדי להימנע מהקלדה חוזרת של הנוסחה כולה מחדש ולחסוך זמן מה.
נניח שיש לנו את הטבלה הזו:
יש מעט שיטות להעתיק נוסחה מתא אחד למשנהו.
ראשית, בחר את התא עם הנוסחה ולחץ באמצעות לחצן העכבר הימני, ובתפריט ההקשר, בחר 'העתק' כדי להעתיק את הנוסחה. או שאתה יכול להשתמש באפשרות 'העתק' בקטע 'לוח' של הכרטיסייה 'בית'.
אבל אתה גם מעתיק נוסחאות פשוט על ידי לחיצה על קיצור המקלדת Ctrl + C. זוהי שיטה יעילה יותר וחוסכת זמן.
לאחר מכן נעבור לתא שאנו רוצים להדביק אותו הקש על קיצור הדרך Ctrl + V כדי להדביק את הנוסחה. לחלופין, לחץ לחיצה ימנית על התא שברצונך להדביק ולחץ על האפשרויות תחת 'אפשרויות הדבקה': אפשרות פשוטה של 'הדבק (P)' או הדבק כאפשרות 'נוסחה (F)'.
לחלופין, אתה יכול גם ללחוץ על 'הדבק מיוחד' מתחת לששת סמלי ההדבקה כדי לפתוח את תיבת הדו-שיח 'הדבקה מיוחדת'. כאן, יש לך מספר אפשרויות כולל שש אפשרויות ההדבקה מתפריט ההקשר. בחר 'הכל' או 'נוסחאות' בקטע הדבק ולחץ על 'אישור'.
כעת התא עם הנוסחה המודבקת צריכה לכלול את אותן הנוסחאות (כמו זה בתא שהועתק) אך עם הפניות התא השונות. כתובת התא מותאמת בעצמה על ידי ה- Excel כדי להתאים למספר השורה של התא המודבק.
העתק נוסחה מתא אחד לתאים מרובים
אותה פעולת הדבק פועלת בדיוק אותו הדבר אם נבחר מספר תאים או טווח של תאים.
בחר את התא עם הנוסחה והקש Ctrl + C להעתיק את הנוסחה. לאחר מכן, בחר את כל התאים שבהם ברצונך להדביק את הנוסחה ולחץ Ctrl + V כדי להדביק את הנוסחה או להשתמש באחת משיטות ההדבקה לעיל כדי להדביק את הנוסחה (כמו שעשינו עבור התא הבודד).
העתק נוסחה לעמודה או שורה שלמה
ב-Excel, אתה יכול להעתיק במהירות נוסחה לעמודה או שורה שלמה.
כדי להעתיק נוסחה לעמודה או לשורה, תחילה הזן נוסחה בתא. לאחר מכן, בחר בתא הנוסחה (D1), ורחף עם הסמן מעל ריבוע ירוק קטן בפינה הימנית התחתונה של התא. בזמן שאתה מרחף, הסמן ישתנה לסימן פלוס שחור (+), אשר נקרא ידית המילוי. לחץ והחזק את ידית המילוי הזו וגרור אותה לכל כיוון שתרצה (עמודה או שורה) מעל התאים כדי להעתיק את הנוסחה.
כאשר אתה מעתיק נוסחה לטווח של תאים, הפניות לתאים של הנוסחה יתאימו אוטומטית על סמך המיקום היחסי של שורות ועמודות והנוסחה תבצע חישובים על סמך הערכים באותן הפניות לתאים (ראה להלן).
בדוגמה שלמעלה, כאשר הנוסחה ב-D1 (=A1*B1)/2) מועתקת לתא D2, ההפניה היחסית משתנה על סמך מיקומה (=A2*B2)/2) וכן הלאה.
באותו אופן, אתה יכול לגרור את הנוסחה לתאים סמוכים שמאלה, ימינה או כלפי מעלה.
דרך נוספת להעתיק את הנוסחה לעמודה שלמה היא על ידי לחיצה כפולה על ידית המילוי במקום לגרור אותה. כאשר אתה לוחץ פעמיים על ידית המילוי, הוא מחיל מיד את הנוסחה ככל שיש נתונים על התא הסמוך.
העתק נוסחה לטווח מבלי להעתיק עיצוב
כאשר אתה מעתיק נוסחה לטווח של תאים עם ידית המילוי, היא מעתיקה גם את העיצוב של תא המקור, כגון צבע גופן או צבע רקע, מטבע, אחוז, זמן וכו' (כפי שמוצג להלן).
כדי למנוע העתקת עיצוב התא, גרור את ידית המילוי ולחץ על 'אפשרויות מילוי אוטומטי' בפינה הימנית התחתונה של התא האחרון. לאחר מכן, בתפריט הנפתח, בחר 'מילוי ללא עיצוב'.
התוצאה:
העתק נוסחת Excel עם עיצוב מספרים בלבד
אם תרצו להעתיק את הנוסחה רק עם הנוסחה והעיצוב כמו תבנית אחוז, נקודות עשרוניות וכו'.
העתק את הנוסחה ובחר את כל התאים שאליהם ברצונך להעתיק את הנוסחה. בכרטיסייה 'בית', לחץ על החץ מתחת ללחצן 'הדבק' בסרט. לאחר מכן, לחץ על הסמל 'נוסחאות ועיצוב מספרים' (הסמל עם % fx) מהתפריט הנפתח כדי להדביק רק את הנוסחה ואת עיצוב המספרים.
אפשרות זו מעתיקה רק עיצוב נוסחאות ומספרים אך מתעלמת מכל שאר עיצובי התא כמו צבע רקע, צבע גופן וכו'.
העתק נוסחה לתאים לא סמוכים/לא רציפים
אם אתה רוצה להעתיק נוסחה לתאים לא סמוכים או לטווחים לא סמוכים, אתה יכול לעשות זאת בעזרת Ctrl מַפְתֵחַ.
בחר את התא עם הנוסחה והקש Ctrl + C להעתיק אותו. לאחר מכן, בחר תאים/טווחים שאינם סמוכים תוך לחיצה ממושכת על Ctrl מַפְתֵחַ. לאחר מכן, הקש Ctrl + V כדי להדביק את הנוסחה ולהקיש להיכנס להשלים.
העתקת נוסחאות מבלי לשנות הפניות לתאים באקסל
כאשר נוסחה מועתקת לתא אחר, Excel משנה אוטומטית את הפניות התא כך שיתאימו למיקומה החדש. הפניות לתא אלו משתמשות במיקום היחסי של כתובת תא, ולכן הן נקראות הפניה לתא יחסי (ללא $). לדוגמה, אם יש לך את הנוסחה '=A1*B1' בתא C1, ואתה מעתיק את הנוסחה הזו לתא C2, הנוסחה תשתנה ל'=A2*B2'. כל השיטות שדנו בהן לעיל משתמשות בהפניות יחסיות.
כאשר אתה מעתיק נוסחה עם הפניות תאים יחסיות, היא משנה באופן אוטומטי הפניות כך שהנוסחה מתייחסת לשורות ולעמודות המתאימות. אם אתה משתמש בהפניות מוחלטות בנוסחה, אותה נוסחה מועתקת מבלי לשנות את הפניות התא.
כאשר אתה שם סימן דולר ($) לפני אות העמודה ומספר השורה של תא (לדוגמה $A$1), זה הופך את התא לתא מוחלט. עכשיו לא משנה לאן אתה מעתיק את הנוסחה המכילה את ההפניה המוחלטת לתא, הנוסחה לעולם לא תעשה זאת. אבל אם יש לך התייחסות לתא יחסית או מעורבת בנוסחה, השתמש בכל אחת מהשיטות הבאות כדי להעתיק מבלי לשנות הפניות לתאים.
העתק נוסחה עם הפניה מוחלטת לתא בשיטת העתק-הדבק
מדי פעם, ייתכן שיהיה עליך להעתיק/להחיל את הנוסחה המדויקת במורד העמודה, מבלי לשנות את הפניות התא. אם ברצונך להעתיק או להעביר נוסחה מדויקת עם התייחסות מוחלטת, עשה זאת:
ראשית, בחר את התא עם הנוסחה שברצונך להעתיק. לאחר מכן, לחץ על שורת הנוסחאות, בחר את הנוסחה באמצעות העכבר ולחץ Ctrl + C להעתיק אותו. אם ברצונך להעביר את הנוסחה, לחץ על Ctrl + X לחתוך אותו. לאחר מכן, הקש על יציאה מקש לעזוב את שורת הנוסחאות.
לחלופין, בחר את התא עם הנוסחה והקש F2 מקש או לחץ פעמיים על התא. זה יעביר את התא שנבחר למצב עריכה. לאחר מכן, בחר את הנוסחה בתא ולחץ Ctrl + C כדי להעתיק את הנוסחה בתא כטקסט.
לאחר מכן, בחר את תא היעד ולחץ Ctrl + V כדי להדביק את הנוסחה.
כעת הנוסחה המדויקת מועתקת לתא היעד ללא שינויים בהפניה לתא.
העתק נוסחאות עם הפניות תא מוחלטות או מעורבות
אם ברצונך להעביר או להעתיק נוסחאות מדויקות מבלי לשנות הפניות לתאים, עליך לשנות הפניות יחסיות לתא להפניות מוחלטות. לדוגמה, הוספת סימן ($) להפניה לתא יחסי (B1) הופכת אותו להפניה מוחלטת ($B$1), כך שהוא נשאר סטטי לא משנה לאן הנוסחה מועתקת או מועברת.
אבל לפעמים, ייתכן שיהיה עליך להשתמש בהפניות מעורבות לתאים ($B1 או B$1) על ידי הוספת סימן דולר ($) לפני אות העמודה או מספר השורה כדי לנעול שורה או עמודה במקום.
הבה נסביר בעזרת דוגמה. נניח שיש לך את הטבלה הזו שמחשבת את החיסכון החודשי על ידי הפחתת שכר דירה (B9) מהרווחים (בעמודה B) מדי חודש.
בדוגמה שלמטה, הנוסחה משתמשת בהפניה מוחלטת לתא ($B$9) כדי לנעול את סכום השכירות לתא B9, ובהפניה יחסית לתא B2 מכיוון שיש להתאים אותה לכל שורה כדי להתאים בכל חודש. B9 נעשה הפניה מוחלטת לתא ($9 B$) מכיוון שאתה רוצה להפחית את אותו סכום שכר דירה מהרווחים של כל חודש.
נניח שאתה רוצה להעביר את היתרות מעמודה C לעמודה E. אם תעתיק את הנוסחה (בשיטת העתק/הדבק רגילה) מתא C2 (=B2-$B$9) תשתנה ל-=D2-$B$9 כשהיא תודבק בתא E2, מה שהופך את החישובים שלך לשגויים!
במקרה זה, שנה את ההפניה היחסית לתא (B2) להפניה לתא מעורב ($B2) על ידי הוספת הסימן '$' לפני אות העמודה של הנוסחה שהוזנה בתא C2.
ועכשיו, אם תעתיקו או תעבירו את הנוסחה מתא C2 ל-E2, או כל תא אחר, ותחילו נוסחה במורד העמודה, הפניה לעמודה תישאר זהה בעוד שמספר השורה יותאם לכל תא.
העתק הדבק נוסחאות Excel מבלי לשנות הפניות באמצעות פנקס רשימות
אתה יכול לראות כל נוסחה בגיליון האלקטרוני של Excel שלך באמצעות אפשרויות הצג נוסחה. כדי לעשות זאת עבור ללשונית 'נוסחאות' ובחר 'הצג נוסחאות'.
לחלופין, תוכל להיכנס למצב תצוגת הנוסחה על ידי לחיצה על Ctrl + ` קיצור דרך, המציג כל נוסחה בגליון העבודה שלך. אתה יכול למצוא את מקש ההדגשה (`) בפינה השמאלית העליונה של המקלדת בשורה עם מקשי המספרים (מתחת ל- יציאה מקש ולפני מקש מספר 1).
בחר את כל התאים עם הנוסחאות שברצונך להעתיק ולחץ Ctrl + C להעתיק אותם, או Ctrl + X לחתוך אותם. לאחר מכן פתח את פנקס הרשימות ולחץ Ctrl + V כדי להדביק את הנוסחאות בפנקס הרשימות.
לאחר מכן, בחר את הנוסחה והעתק (Ctrl + C) אותו מהפנקס והדבק אותו (Ctrl + V) בתא שבו ברצונך להעתיק את הנוסחה המדויקת. אתה יכול להעתיק ולהדביק אותם אחד אחד או כולם בבת אחת.
לאחר הדבקת הנוסחאות, כבה את מצב תצוגת הנוסחה על ידי לחיצה Ctrl + ` או שוב עבור אל 'נוסחאות' -> 'הצג נוסחאות'.
העתק את הנוסחאות המדויקות באמצעות חיפוש והחלפה של Excel
אם ברצונך להעתיק מגוון של נוסחאות מדויקות, תוכל גם להשתמש בכלי חיפוש והחלפה של Excel כדי לעשות זאת.
בחר את כל התאים עם הנוסחאות שברצונך להעתיק. לאחר מכן עבור ללשונית 'בית', לחץ על 'מצא ובחר' בקבוצת העריכה, ובחר באפשרות 'החלף', או פשוט לחץ על Ctrl + H כדי לפתוח את תיבת הדו-שיח מצא והחלפה.
בתיבת הדו-שיח מצא והחלפה, הזן את סימן השוויון (=) בשדה 'מצא מה'. בשדה 'החלף ב', הזן סמל או תו שאינם כבר חלק מהנוסחאות שלך, כמו #, או וכו'. לאחר מכן, לחץ על הלחצן 'החלף הכל'.
תקבל תיבת הודעה מיידית שאומרת 'עשינו 6 החלפות' (מכיוון שבחרנו 6 תאים עם נוסחאות). לאחר מכן לחץ על 'אישור' ו'סגור' כדי לסגור את שתי תיבות הדו-שיח. פעולה זו מחליפה את כל הסימנים השווים ל-(=) בסימני hash (#), והופכת נוסחאות למחרוזות טקסט. כעת הפניות לתאים של הנוסחאות לא ישתנו בעת העתקה.
כעת, אתה יכול לבחור תאים אלה, הקש Ctrl + C כדי להעתיק אותם, ולהדביק אותם בתאי יעד עם Ctrl + V.
לבסוף, עליך לשנות את הסימנים (#) בחזרה לסימנים (=). כדי לעשות זאת, בחר את שני הטווחים (הטווח המקורי והמועתק) ולחץ על Ctrl + H כדי לפתוח את תיבת הדו-שיח מצא והחלפה. הפעם, הקלד את סימן ה-hash (#) בשדה 'מצא מה', ושווה לסימן (=) בשדה 'החלף ב', ולחץ על כפתור 'החלף הכל'. לחץ על 'סגור' כדי לסגור את תיבת הדו-שיח.
כעת, מחרוזות הטקסט מומרות בחזרה לנוסחאות ותקבלו את התוצאה הבאה:
בוצע!