אקסל האשכול המרכזי לשאלות באקסל/גוגל שיטס

[יוסיK]

שלום וברכה
יש דרך להזין נתונים לאקסל מלמעלה ללמטה?
כלומר שהשורה מתחת לכותרת תהיה ריקה ואחרי שאני מזין לתוכה נתונים כל הטבלה חוץ מהכותרת תרד בשורה ושוב תהיה לי שורה ריקה וכן הלאה?
תודה מראש

 

[יעקב ויצמן]

שלום רב,
אשמח לעזרת האלופים כאן (בפרט @יעקב ויצמן @xl3391 @אפר ), אני רוצה לעשות גליון גוגל שיטס לחישוב משכנתא, לפי נתונים משתנים במשכנתא כמו ריבית הפריים והצמדה למדד, ויש לי כמה בעיות-

1. ריבית הפריים לפי תאריכים (לא רק המעודכנת, כי זה משתנה מתשלום לתשלום).
2. מדד המחירים לצרכן לפי תאריכים.
3. חישוב קבוע של יתרת הקרן (לפי המדד המעודכן והיתרה המעודכנת).
4. (הכי פחות חשוב) פונקציה שמחזירה את עלות האג"חים המעודכנים של הבנקים השונים, לחישוב משתנה כל 5 שנים וכו'.

האם יש למישהו פתרון לדברים האלו או אפילו לחלקם?

תודה רבה מראש.

תראה.אילו היה מדובר פה בהלוואה עם החזר בריבית קבועה בלבד.היה ניתן לפתור זאת בקלות עם פונקציית pmt .
אך כאן מדובר גם על חישוב של נתונים משתנים.ויש צורך בהבנה מעמיקה של שיטת התשלומים וזו מלאכת מחשבת דלאו זוטרתא היא...
אני לא הוכשרתי "כיועץ משכנתא" ועל דבר שאיני יודע,אני משתדל לקיים מה שאמרו חז"ל "למד לשונך לומר איני יודע, שמא תתבדה ותאחז..." (ברכות דף ד)
מה שכן אוכל להציע ולומר.שכיון שנתוני החזר (על פי מדדים דינמיים ) אמיתיים .הינם על סמך מה שהיה ומה שבהווה.
(בכל הקשור לעתיד ישנם כלים לשער את התחזיות "והתנודות" לכאן או לכאן.אך אלו לא נתונים מדוייקים ואין לנו אלא הנגלות לנו ולבנינו...)
וא"כ.בכל דרך שתבחר כך או כך, תצטרך לשאוב נתוני אמת אונליין לגבי ריבית בנק ישראל/ממד המחירים וכו. מאתרים המעדכנים זאת(בנק ישראל ועוד) על בסיס קבוע והכל נתוני אמת.
ואת זה ניתן לייבא בשיטס (באקסל ניתן בעזרת power query) על ידי פונקציית importhtml
משם ניתן לשאוב מידע לתא ספציפי העורך את החישוב (ואז ניתן בעזרת xlookup או filter וכדומה לייבא נתונים השייכים לתאריך ספציפי)
אם יש צורך לעזרה נוספת בענין היבוא עצמו אשמח לסייע במייל
yv0528174820 בגימייל.
בהצלחה רבה!

 

[יעקב ויצמן]

שלום וברכה
יש דרך להזין נתונים לאקסל מלמעלה ללמטה?
כלומר שהשורה מתחת לכותרת תהיה ריקה ואחרי שאני מזין לתוכה נתונים כל הטבלה חוץ מהכותרת תרד בשורה ושוב תהיה לי שורה ריקה וכן הלאה?
תודה מראש

על פי כללי הלוגיקה של האקסל הדבר אינו אפשרי בעזרת הכלים הרגילים /פונקצייות.
על ההסבר לכך כתבתי בזמנו בלינק כאן
https://www.prog.co.il/threads/רוצה-לעזור-לכם-באקסל.499341/post-13230238
בעזרת vba בלבד זה ניתן לפיתרון (כולי האי ואולי)
תוכל לראות בלינק כאן דוגמא למעין שאלתך.

How can I insert a row below if the cell contains a value

I want to insert a row below if in one column contains 12345 My code is not working because is not inserting anything Sub Macro 1() Dim i as Integer, inttofind as Integer, app as range, cell ...

stackoverflow.com

(לא מצאתי vba מדויק לשאלתך.וגם אין לי עסק ביום יום בכתיבת מאקרו כזה ואחר)
אם תרצה לכתוב בעמודה אחת ערכים באופן שיגרתי .ותרצה לשלוף לאחר מכן בעמודה נוספת וחיצונית נתונים בדילוג כזה ואחר.הדבר אפשרי ותלוי מה רוצים וכו...אז אם לזה התכוונת נא שלח גיליון דוגמא עם הסבר מפורט בתוך הגיליון .
בהצלחה

 

[יעקב ויצמן]

יש לי טבלה כזו:

שם:​	יום:​	תאריך:​	טלפון:​	סכום:​
אברהם​				150​
				35​
				160​
				40​
				40​
				150​
יצחק​				40​
				35​
				550​
				60​
יעקב​				50​
				450​
				35​
				35​
				35​
יוסף​				35​
				160​
				40​
				40​
				150​
משה​				40​
				35​
				550​
				60​
אהרן​				50​
				450​
				35​
				35​
				35​
דוד​				35​
				160​
				40​
				40​
				150​
				550​
				40​

והייתי שמח אם יוכלו לעשות לי שיחשב כמה תוצאות,
תוצאה ראשונה: סך של 5% מכל הטור
תוצאה שניה-שמינית: 5% מכל שם בנפרד
תודה רבה

תראה כאן בלינק

אחוזים באקסל | אוורסט - הדרכת מחשבים

everest.co.il

 

[#גמח]

@יעקב ויצמן דבר ראשון תודה, זה כבר לא פעם ראשונה שאתה עוזר לי ולכולם כאן...
אבל, זה עושה לי שגיאה #VALUE!
או שלא הבנתי מה לעשות...
העתקתי את =B2*(1-20%) לאקסל (ושיניתי את הB2 לD2 (ששם יש מספר 150) וזה מה שנהיה
רק אציין שאני מעוניין לעשות את זה על עמודה (או חלק מעמודה ולא קוביה אחת בודדת) ושאני משתמש בגוגל שיטס...

 

[יעקב ויצמן]

@יעקב ויצמן דבר ראשון תודה, זה כבר לא פעם ראשונה שאתה עוזר לי ולכולם כאן...

בשמחה רבה.תבורך מפי עליון

אבל, זה עושה לי שגיאה #VALUE!
או שלא הבנתי מה לעשות...
העתקתי את =B2*(1-20%) לאקסל (ושיניתי את הB2 לD2 (ששם יש מספר 150) וזה מה שנהיה
רק אציין שאני מעוניין לעשות את זה על עמודה (או חלק מעמודה ולא קוביה אחת בודדת) ושאני משתמש בגוגל שיטס...

מצורף לך גיליון מתוקן עם הנוסחאות בפנים
בתא g2 ישנה פונקציית unique לצורך מיזוג כפילויות של השמות בעמודה A

לאחר מכן בתא h2 יש להזין פונקציית sumif ולבסוף להכפיל ב5%

ואז לגרור למטה
החישוב 5% מכל הסכומים מבוצע בתא f2 על ידי פונקציית sum והכפלה ב5%
הגיליון מותאם לשיטס ולאקסל כאחד
אם משהו לא מסתדר.תשלח לי את הגיליון במייל ואטפל בו בשמחה
yv0528174820 בגימייל
נ.ב כפי שהבנתי בשאלתך .הנך מעונין רק לדעת כמה זה ה5% .ואין כאן חישוב להוסיף או להחסיר מהסך המקורי.
אך אם לזה התכוות אז גם זה אפשרי...

 

[Excel]

שלום וברכה
האם יש כזו פונקציה\נוסחה של מספור תאים אוטומטי באקסל?
כמו למשל אני רוצה שהוא יסמן לי לבד את היום ה 20 קדימה מהקוביה שאני עומדת עליה...

אשמח לדעת

 

[בתיה B]

תנסו עם פונקציה ROW

 

[יעקב ויצמן]

הניסוח בשאלה קצת מבלבל...ולכן אנסה לענות על כל האלמנטים שהוזכרו פה.

שלום וברכה
האם יש כזו פונקציה\נוסחה של מספור תאים אוטומטי באקסל?

לעצם יצירת מספור "רץ" (מה שנקרא "סדרה") הדבר ניתן בעזרת פונקציית sequence כפי שהסברתי בפירוט בלינק כאן
https://www.prog.co.il/threads/רוצה-לעזור-לכם-באקסל.499341/post-13134038
כמו כן.הסברתי בעבר באריכות על יתרונה של שיטה זו על פני השיטה "המסורתית" של הזנת שני תאים ידנית וגרירתם מטה.תוכלי לקרוא על כך בלינק המצורף
https://www.prog.co.il/threads/רוצה-לעזור-לכם-באקסל.499341/post-13179048

כמו למשל אני רוצה שהוא יסמן לי לבד את היום ה 20 קדימה מהקוביה שאני עומדת עליה...

אשמח לדעת

ניתן לבצע זאת במספר דרכים:
אך הדרך הפשוטה ביותר היא לבצע הפנייה לתא "ההתחלתי" ולסמן פעולת חיבור פשוטה

בצילום מסך הנ"ל.(בשורה ראשונה מועתקות הפונקציות שמקומם בשורה 2...)
אז בעמודה A וB הדגמתי את המספור רץ הן במספר והן בתאריכים על ידי פונקציית sequence הנ"ל.
בתא c2 וd2 ביצעתי פעולת חיבור לאחר ההפנייה לתא .
כתבתי 10 מינוס 1 כי הפונקצייה כוללת גם את תא הראשוני.(ולא אכנס כעת ללוגיקה סביב הסוגיא הזו) ובאותה מידה ניתן לכתוב +9...
שימי לב.שבגלל שאנו מדברים על מספרים "עוקבים" גם אם כל הערכים משורה 3 ומטה לא היו נכתבים.גם אז התוצאה היתה שווה.
והסיפור נכון גם לגבי התאריכים שבעמודה b.
ולמה???
התשובה היא- כפי שהסברתי כאן פעמים רבות בפורום זה.
(כאן למשל https://www.prog.co.il/threads/רוצה-לעזור-לכם-באקסל.499341/post-12161520)
שלכל תאריך יש "מספר סידורי" שהוא למעשה ה-subtext של התאריך.ולכן פעולת החיבור היא אומרת לאקסל "לקפוץ" עוד 9 ימים קדימה ולהביא לנו עת התוצאה שהוא רואה "אצלו" (בלוח שנה הגדול/ענק שפרוס "לפניו")
מקווה שהכל מובן וברור.
אם יש שאלה נוספת.נא צרפי גיליון דוגמא
בהצלחה!

 

[יעקב ויצמן]

תנסו עם פונקציה ROW

אם הכוונה הייתה לפתור את ענין הקפיצה שהוזכר בסוף השאלה אז אסביר את המורכבות שבפיתרון זה..
ובכן:מלבד הבעיה שצריך תמיד לחפש ולספור ידנית (בראש יותר נכון...) מהו התא שהספירה נגמרת בו..(וכל זאת בהנחה שמספר 1 נכתב בשורה הראשונה כמו a1)
מה יקרה במקרה שהספירה מ1 ומעלה לא התחילה בשורה 1 אלא משורה 20 כמו כאן?

אז ,בהנחה שאנו מעוניינים ב9 ספרות "קדימה" .אם נבצע הפנייה לתא a29 בפונקציית row
נקבל את המספר 29 ולא 10.
ולמה?
אסביר: כי מהות הפונקצייה היא להחזיר את מספר השורה האמיתית באקסל בלבד!
ללא שום תלות מהיכן הטבלה/טווח התחיל/ה ,וחשוב לזכור זאת...

ואם הייתה כוונתך לעצם טכניקת המספור...אז אני רק אסביר זאת לתועלת הכלל...כי באמת מדובר בחידוש מעניין ופחות מוכר בדרך כלל...
תראו: בגרירה ידנית "המסורתית" שהזכרתי לעיל בתשובתי.יש לסמן תחילה 2 תאים סמוכים.
מה הטעם לכך?למה דווקא שניים?
כי אנו מציגים לאקסל שני תאים עם הפרש בינהם כל שהוא .האקסל "מספיק חכם ונבון" להבין ולתרגם מהו ההפרש הזה.ואז בפעולת הגרירה האקסל "משמר" את ההפרש והרווח הזה בין כל שני תאים בטווח שנגרר.
והנה חידה: איך יתכן שנגרור תא אחד בלבד ויווצר לנו מספור רץ?
התשובה היא:כאשר מדובר בהפרש של מספר אחד (אני מדגיש) זה ניתן בהחלט בעזרת פונקציית row
תראו בדוגמא פה שהכינותי מראש

באמצע "שומקום" בגיליון בתא a10 החלטתי ליצור מספור רץ מ1 עד 10.למה דווקא שם?ככה!...
וגם החלטתי להשתמש בגרירה של תא אחד בלבד.למה?שוב ככה...סתם!
אבל הנה זה אפשרי...תראו את הפונקציות שמועתקות בעמודה B ותבינו את הסיבה...
הפונקצייה נכתבה בתא אחד שסומן בצהוב ובוצעה גרירה למטה.
לא היה כאן צורך לכתוב בשני תאים סמוכים 1 ו2.(ואז לבצע גרירה)
כי המספרים האלה (כולל ההפרש בינהם) כתובים וחקוקים בכל גיליון אקסל שהוא ...כך בנויים מספרי השורות באקסל...
זו חידה מעניינת עם פיתרון מבריק...
וכאמור לעיל...אפשר "לחסוך" למשתמש גם את פעולת הגרירה מטה על ידי שימוש בפונקציית sequence המופלאה!

 

[צירקי]

יש לי 2 טבלאות עם תאריכים וסכום לכל תאריך
התאריכים חוזרים על עצמם ואני צריכה לעשות תרשים עוגה ולצמצם תאריכים שלא יחזרו על עצמם פעמיים
איך עושים את זה?
תודה!

 

[יעקב ויצמן]

יש לי 2 טבלאות עם תאריכים וסכום לכל תאריך
התאריכים חוזרים על עצמם ואני צריכה לעשות תרשים עוגה ולצמצם תאריכים שלא יחזרו על עצמם פעמיים
איך עושים את זה?
תודה!

עם פונקציית unique (יוניק) שהזכרתי ממש לעיל
https://www.prog.co.il/threads/רוצה-לעזור-לכם-באקסל.499341/post-13702813
אם משהו לא מסתדר.נא שלחי גיליון דוגמא
בהצלחה

 

[יהודית גמליאל]

אומנם זה שאלה בגוגל שיטס, אבל אולי תדעו לעזור לי, אחרי הכל זה די דומה...
יש לי טבלת גוגל שיטס מתעדכנת שבתאים שלה נכתב 'כן' או 'לא' ואני צריכה שברגע שנכנס 'כן' הוא יהפוך אוטומטית ל1 (אם נכנס 'לא' הוא יכול להשאר ככה, לא אכפת לי).
ניסיתי עם הנוסחא של

אבל הוא מחזיר לי שגיאה
אז איך כן עושים את זה?

תודה

 

[יעקב ויצמן]

אומנם זה שאלה בגוגל שיטס, אבל אולי תדעו לעזור לי, אחרי הכל זה די דומה...
יש לי טבלת גוגל שיטס מתעדכנת שבתאים שלה נכתב 'כן' או 'לא' ואני צריכה שברגע שנכנס 'כן' הוא יהפוך אוטומטית ל1 (אם נכנס 'לא

רק לחידוד הדברים.אסביר שמבחינת if תנאי שלא מתקיים הכוונה לכל טקסט חוץ מהמילה כן.זה יכול להיות המילה לא או whatever

' הוא יכול להשאר ככה

מה זה ככה? התכוונת שישאר ריק?
בפונקציית if יש לכתוב מה אנו רוצים שהאקסל/שיטס יכתבו אם התנאי לא מתקיים.
אחרת(בהיעדר הזנת ארגומנט שלישי) יוחזר בתא המילה false

, לא אכפת לי).
ניסיתי עם הנוסחא של

אבל הוא מחזיר לי שגיאה
אז איך כן עושים את זה?

תודה

את הטקסט כמו בדוגמא אצלך שכתוב כן.
יש להקיף בגרשיים כפולים כאלה " ולא גרש בודד כזה '

 

[יעקב ויצמן]

@יהודית גמליאל
בקיצור.תתקני את הפונקצייה כפי שמוצג כאן בצילום מסך.

אם תרצי שכאשר לא מתקיים התנאי יוחזר מה שכתוב בתא u2.אז צריך בארגומנט 3 לכתוב u2 במקום ""(הכוונה לריק/כלום)
באגב, ניתן באותה מידה להשתמש במקרה הזה בפונקציית substitute . אבל גם עם if התוצאה שווה ומצוינת
בהצלחה

 

[יהודית גמליאל]

@יהודית גמליאל
בקיצור.תתקני את הפונקצייה כפי שמוצג כאן בצילום מסך.
צפה בקובץ המצורף 1671936
אם תרצי שכאשר לא מתקיים התנאי יוחזר מה שכתוב בתא u2.אז צריך בארגומנט 3 לכתוב u2 במקום ""(הכוונה לריק/כלום)
באגב, ניתן באותה מידה להשתמש במקרה הזה בפונקציית substitute . אבל גם עם if התוצאה שווה ומצוינת
בהצלחה

ראשית, תודה רבה!!!
שנית, זה יוצר לי בעיה, כי אם אני משביתה את החזרה המעגלית- הוא נותן שגיאה, ואם אני מפעילה- הוא מאפס את התוצאה, כי בפעם השניה כבר אין שם כן אלא 1....

 

[יעקב ויצמן]

ראשית, תודה רבה!!!
שנית, זה יוצר לי בעיה, כי אם אני משביתה את החזרה המעגלית- הוא נותן שגיאה, ואם אני מפעילה- הוא מאפס את התוצאה, כי בפעם השניה כבר אין שם כן אלא 1....

אני מכיר את סוגיית ההפנייה מעגלית.
צריך לראות מקרוב את סדר הפונקציות בעמודות ואת שורש התקלה.
אשמח אם תשלחי לי את הגיליון במייל.ואטפל בנושא בל"נ עד תום ובשמחה רבה
yv0528174820 בגימייל

 

[ה. שלמה]

לא מצליח לייבא נתוני דיבידנדים בנוסחת גוגל פיננס בשיטס בפרמטר "yieldpct",
זה מחזיר "שגיאה הערך של פונקציה GOOGLEFINANCE פרמטר 2 לא חוקי עבור התו שצוין."

יש למישהו דרך לפתור את הבעיה, או דרך אחרת לייבא נתונים אלו לשיטס?
@יעקב ויצמן

 

[יעקב ויצמן]

לא מצליח לייבא נתוני דיבידנדים בנוסחת גוגל פיננס בשיטס בפרמטר "yieldpct",
זה מחזיר "שגיאה הערך של פונקציה GOOGLEFINANCE פרמטר 2 לא חוקי עבור התו שצוין."

יש למישהו דרך לפתור את הבעיה, או דרך אחרת לייבא נתונים אלו לשיטס?
@יעקב ויצמן

לפונקצייה זו דרוש לימוד רב וידע מקדים בתחום שוק ההון ועוד
אינני מתעסק בכך ביום יום, ולא למדתי קורס כזה ואחר בענף הזה.כך שהידע שלי מסתכם בגרגיר מכאן וגרגיר מכאן..
ועל משהו שאיני יודע, אז אני משתדל לקיים דברי חז"ל "למד לשונך לומר איני יודע וכו.."
באופן כללי.בחיפוש במספר פורומים באנגלית ראיתי שישנם תלונות רבות על פרמטר "yieldpct" שאינו עובד ומוחזר בו שגיאת n/a כמו בלינק כאן.
בעבר, גם כתבתי על הבעיה המוכרת בפונקצייה זו בכל הקשור למשיכת נתונים בשוק ההון/המסחר הישראלי.
תראה,איני רוצה גם לפתור ללא כלום...
באופן כללי, אם תניח את הפונקצייה הזו בצד.אז יהיה ניתן למשוך נתונים בפונקצייה importhtml (וישנן עוד מספר אפשרויות.ובאקסל ניתן לייבא בעזרת power query) ואותה אני מכיר היטב..
אני מציע-שתנסה לבדוק באיזה אתר שנאמן עליך (בעברית או באנגלית)שמציג ערכים של נתוני אמת.
ותעדכן אותי במייל (לאחר שליחת לינק של גיליון שיטס דוגמא) ואשתדל כמיטב יכולתי בל"נ לפתור את הנושא, שבסופו של דבר יוצג לך בתא מסוים בגיליון- ערכים עם נתוני אמת ומעודכנים אונליין.
המייל שלי yv0528174820 בגימייל
בהצלחה!
נ.ב אם למישהו מחברי הפורום יש פיתרון אחר לשאלה זו.אשמח גם לשמוע...

 

[מועד לשמחה]

היי יש לי עמודה כלשהיא עם תוצאות, ואני צריכה לעשת לה את החישוב הבא:
10 כפול [לוג 10 של (ממוצע של התוצאות בעמודה)]
היות ואין לי כל כך ידע עם אקסל, אשמח לעזרה
תודה מראש