במאמר זה נציג נוסחאות מתקדמות לחישוב גימטריא של מחרוזות טקסט באקסל ובגוגל שיטס, נסביר את הרציונל מאחורי הנוסחה וננתח את מרכיביה.

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

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

האתגר העיקרי הוא לבצע ספירה מדויקת של כל אות במחרוזת, גם כאשר האות מופיעה מספר פעמים, ולהכפיל זאת בערך המתאים לה.

גימטריא מאותיות למספרים​


קוד: 
=SUMPRODUCT( (LEN(C1) - LEN(SUBSTITUTE(C1, A1:A27, ""))) * B1:B27 )

נוסחה זו מעט מורכבת, בוא נפשט אותה:
בתאים A1:A27 מופיעים כלל האותיות מ-א עד ת, הטווח הזה כולל אותיות סופיות, כי גם הם בגימטריא, א, ב, ג וכו'.
בתאים B1:B27 מופיע טווח ערכים מקביל לכל אות, בהתאם לערכי הגימטריא שלהם, 1, 2, 3 וכו'.
בתא C1 נכתוב את המילה שאנו רוצים למצוא את הגימטריא שלה. אנחנו בינתיים הולכים על המילים "אברהם אבינו", למה? כי יש פה אות סופית (ם) ואותיות כפולות (א,ב).
את הנוסחה עצמה נמקם בכל מקום שנרצה, באותו מקום נמצא גם ערך הגימטריא, מקובל להשים בתא סמוך.

עכשיו נפרק את הנוסחה, נתחיל מפנים הנוסחה:
קוד: 
=SUBSTITUTE(C1, A1:A27, "")

בגדול, פונקציית SUBSTITUTE עושה החלפות של תווים בתווים אחרים, יש בה 3 ארגומנטים:
C1 - התא שאנו רוצים לפעול עליו.
A1:A27 - טווח האותיות הקיימות בעברית.
"" - מחליפה ב- כלום. כלומר, מוחקת.

שימו לב שיש מערך של תאים A1:A27, כלומר הנוסחה בונה טבלה פנימית בעלת 27 שורות וכל פעם סופרת את התאים, המערך הפנימי נראה (או לא) כך:
אברהם אבינו
האות שמחפשים:תוצאה אחרי סינון:
אברהם בינו
בארהם אינו
האברם אבינו
ואברהםאבינ
יאברהם אבנו
םאברה אבינו
נאברהם אביו
ראבהם אבינו

לפני שנתרגש, אם ננסה לכתוב את זה לבד, הפונקצייה תחזיר רק את השורה הראשונה במערך, אבל בהמשך זה יעזור.
שלב הבא:
קוד: 
=LEN(SUBSTITUTE(C1, A1:A27, ""))

פונקציית LEN כידוע סופרת את התווים הקיימים במילה הנמצאת בתא C1 ומסננת את התווים הקיימים, הנוסחה פועלת מספר פעמים כפי אורך המערך וכל פעם מקבלים משפר שונה.
לדוגמא:
אברהם אבינו
האות שמחפשים:תוצאה אחרי סינון:מספר התווים שנשארו:
אברהם בינו9
בארהם אינו9
האברם אבינו10
ואברהם אבינ10
יאברהם אבנו10
םאברה אבינו10
נאברהם אביו10
ראבהם אבינו10

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

קוד: 
=LEN(C1)
-
LEN(SUBSTITUTE(C1, A1:A27, "")

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

ועכשיו לנוסחה המלאה:
קוד: 
=SUMPRODUCT( (LEN(C1) - LEN(SUBSTITUTE(C1, A1:A27, ""))) * B1:B27 )

הנוסחה פועלת בדרך הבאה:
אברהם אבינו
האות שמחפשים:תוצאה אחרי סינון:מספר התווים שנשארו:מספר ההופעות במילה:אחרי הכפלה בערך:
אברהם בינו921
בארהם אינו924
האברם אבינו1015
ואברהם אבינ1016
יאברהם אבנו10110
םאברה אבינו10140
נאברהם אביו10150
ראבהם אבינו101200
סך הכל בשימוש בפונקצייה SUMPRODUCT:317







התעייפתם?? זה רק ההתחלה...

גימטריא ממספרים לאותיות​



מה עושים את יש לכם מספר כלשהו ואתם רוצים לדעת את ערכו באותיות?
בואו נציץ על הנוסחה הבאה:
קוד: 
=LET(
  digitsOnly, TEXTJOIN("", TRUE, FILTER(MID(C3, SEQUENCE(LEN(C3)), 1), ISNUMBER(--MID(C3, SEQUENCE(LEN(C3)), 1)))),

  CHOOSE(LEFT(TEXT(digitsOnly, "0000"))+1, ,"א","ב","ג","ד","ה","ו","ז","ח","ט") &
  IF(--LEFT(TEXT(digitsOnly, "0000"))>0, D3, "") &
  CHOOSE(MID(TEXT(digitsOnly, "0000"), 2, 1)+1, ,"ק","ר","ש","ת","תק","תר","תש","תת","תתק") &
  CHOOSE(MID(TEXT(digitsOnly, "0000"), 3, 1)+1, , IF(OR(--RIGHT(TEXT(digitsOnly, "0000"))=5, --RIGHT(TEXT(digitsOnly, "0000"))=6), "ט", "י"), "כ", "ל", "מ", "נ", "ס", "ע", "פ", "צ") &
  CHOOSE(RIGHT(TEXT(digitsOnly, "0000"))+1, ,"א","ב","ג","ד", IF(--MID(TEXT(digitsOnly, "0000"), 3, 1)=1, "ו", "ה"), IF(--MID(TEXT(digitsOnly, "0000"), 3, 1)=1, "ז", "ו"), "ז", "ח", "ט")
)

בואו לא ניבהל, נעבור שלב שלב:
פונקציית LET (בשונה מ-LEN), מגדירה משתנים זמניים, היא עובדת בדומה לשפת תכנות רגילה, במקרה שלנו הוגדר משתנה בשם "digitsOnly" ובו נוסחה המסננת את כל התווים מלבד מספרים, לאחר מכן הנוסחה מריצה כמה פונקציות הממירות את המספרים לאותיות.
נתחיל מנוסחת הסינון:
קוד: 
=TEXTJOIN("", TRUE, FILTER(MID(A1, SEQUENCE(LEN(A1)), 1), ISNUMBER(--MID(A1, SEQUENCE(LEN(A1)), 1))))

הנוסחה הזאת בנויה מ-7 שלבים:
א. LEN כמובן סופרת את כמות התווים, במקרה שכתוב "אברהם248" הנוסחה מחזירה 8.
ב. SEQUENCE יוצרת טבלה מ-1 עד - המספר שתקלידו, בנוסחה שלנו הוא כמות התווים שפונקציית LEN ספרה, והתוצאה תהיה:
קוד: 
1,2,3,4,5,6,7,8.
ג. MID בוחרת תו כלשהו מתוך המילה, מספר התו שייבחר תלוי בספרה שתכתבו, במקרה שלנו שהנוסחה יצרה מספרים מ-1 עד 8, בכל פעם ייבחר תו אחד מחדש, ונוצר פה מערך של 8 שורות, עכשיו במקום הספרות 1-8 שמנינו מקודם, יווצרו כאן רשימת אותיות:
קוד: 
"א","ב","ר","ה","ם","2","4","8".
ד. -- מינוס כפול ממיר טקסט למספר, אם בתא יש מספר אך הוא מזוהה כטקסט, שימוש במינוס כפול ימיר אותו למספר בחזרה, אם יש תווים שאינם מספרים המינוס הכפול יחזיר שגיאה, וכך המערך נראה:
קוד: 
#VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, 2, 4, 8.
ה. ISNUMBER בודקת האם התו הוא מספר ומחזירה TRUE או שאינו ספרה ומחזירה FALSE, במקרה שלנו המערך מוחלף ל-:
קוד: 
FALSE, FALSE, FALSE, FALSE, FALSE, TRUE, TRUE, TRUE
ו. FILTER עוברת על המערך שבנוי פעמיים עם שתי פונקצייות MID האמורות לעיל, ובפעם השנייה יש סינון של ספרות, הנוסחה מוחקת מהמערך את מה שאינו ספרות. שני המערכים והסינון נראים כך:
קוד: 
#VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, 2, 4, 8.
FALSE, FALSE, FALSE, FALSE, FALSE, TRUE, TRUE, TRUE
2,4,8
ז. TEXTJOIN מחברת את כל המערך המסונן לטקסט אחד רציף, הגרשיים הם הטקסט בין הערכים ובמקרה שלנו "" = כלום. המילה TRUE אינה נזקקת כ"כ בגלל שאין לנו תאים ריקים, והארגומנט השלישי הוא תוצאת הסינון.
קוד: 
248

כל זה נכנס לתוך המשתנה "digitsOnly", מעתה, כל מה שנכתוב בנוסחה יעבור סינון קפדני של ספרות.

הלאה, כעת בואו נמיר את הספרות לאותיות, במקרה שלנו לא אמור להיות יותר מ-4 ספרות אז הכנתי את זה ל-4 ספרות בלבד.
קוד: 
  CHOOSE(LEFT(TEXT(digitsOnly, "0000"))+1, ,"א","ב","ג","ד","ה","ו","ז","ח","ט") &
  IF(--LEFT(TEXT(digitsOnly, "0000"))>0, D3, "") &
  CHOOSE(MID(TEXT(digitsOnly, "0000"), 2, 1)+1, ,"ק","ר","ש","ת","תק","תר","תש","תת","תתק") &
  CHOOSE(MID(TEXT(digitsOnly, "0000"), 3, 1)+1, , IF(OR(--RIGHT(TEXT(digitsOnly, "0000"))=5, --RIGHT(TEXT(digitsOnly, "0000"))=6), "ט", "י"), "כ", "ל", "מ", "נ", "ס", "ע", "פ", "צ") &
  CHOOSE(RIGHT(TEXT(digitsOnly, "0000"))+1, ,"א","ב","ג","ד", IF(--MID(TEXT(digitsOnly, "0000"), 3, 1)=1, "ו", "ה"), IF(--MID(TEXT(digitsOnly, "0000"), 3, 1)=1, "ז", "ו"), "ז", "ח", "ט")

שלב האלפים:
אלפים בעברית נכתב אות מתוך א-י, יש שכותבים רווח, יש גרש, ויש ללא רווח כלל, אני השארתי את זה פתוח לשינוי בתא D3.
הנוסחה פועלת כך:
קוד: 
CHOOSE(LEFT(TEXT(digitsOnly, "0000"))+1, ,"א","ב","ג","ד","ה","ו","ז","ח","ט")
בחר את הספרה השמאלית (במבנה של 4 ספרות) מהתא המסונן והוסף לה 1, אם בחרת 1, (סימן שהיה כתוב 0 לפני הוספת 1) אל תכתוב כלום, אם נבחר 2 כתוב א וכן הלאה עד האות ט.
קוד: 
IF(--LEFT(TEXT(digitsOnly, "0000"))>0, D3, "")
לאחר מכן בדוק האם הספרה השמאלית ביותר (במבנה של 4 ספרות) גדולה מ-0, להזכירכם, צריך להמיר את הספרה השמאלית לספרה באמצעות מינוס כפול. אם היא גדולה מ-0 כתוב את מה שכתוב בתא D3 ובמקרה שלי "גרש", ואם לא אל תכתוב כלום.

שלב המאות:
קוד: 
CHOOSE(MID(TEXT(digitsOnly, "0000"), 2, 1)+1, ,"ק","ר","ש","ת","תק","תר","תש","תת","תתק")
בחר את הספרה השניה (משמאל במבנה של 4 ספרות) מהתא המסונן והוסף 1 וכתוב על פי זה את האותיות ק,ר, וכו'.

שלב העשרות:
קוד: 
CHOOSE(MID(TEXT(digitsOnly, "0000"), 3, 1)+1, , IF(OR(--RIGHT(TEXT(digitsOnly, "0000"))=5, --RIGHT(TEXT(digitsOnly, "0000"))=6), "ט", "י"), "כ", "ל", "מ", "נ", "ס", "ע", "פ", "צ")
בחר את הספרה השלישית (משמאל במבנה של 4 ספרות) מהתא המסונן והוסף 1 וכתוב על פי זה את האותיות י, כ וכו', יש לציין שאם הספרה הרביעית היא 5 או 6 יש להקליד ט במקום י.

שלב היחידות:
קוד: 
CHOOSE(RIGHT(TEXT(digitsOnly, "0000"))+1, ,"א","ב","ג","ד", IF(--MID(TEXT(digitsOnly, "0000"), 3, 1)=1, "ו", "ה"), IF(--MID(TEXT(digitsOnly, "0000"), 3, 1)=1, "ז", "ו"), "ז", "ח", "ט")
בחר את הספרה הימנית ביותר (במבנה של 4 ספרות) מהתא המסונן והוסף 1 וכתוב על פי זה את האותיות א, ב וכו', יש לציין שאם הספרה השלישית היא 1 יש להקליד ו במקום ה, ו-ז במקום ו.

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




גימטריא מאותיות לאותיות:​


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


קוד: 
=LET(
  txt, E8,
  letters, A3:A29,
  values, B3:B29,
 
  gematria, SUMPRODUCT((LEN(txt) - LEN(SUBSTITUTE(txt, letters, ""))) * values),
 
  gemtxt, TEXT(gematria, "0000"),
 
  a, LEFT(gemtxt, 1),
  m, MID(gemtxt, 2, 1),
  d, MID(gemtxt, 3, 1),
  u, RIGHT(gemtxt, 1),
 
 
  firstPart, CHOOSE(a+1, ,"א","ב","ג","ד","ה","ו","ז","ח","ט"),
  secondPart, CHOOSE(m+1, ,"ק","ר","ש","ת","תק","תר","תש","תת","תתק"),
  thirdPart, CHOOSE(d+1, , IF(OR(--u=5, --u=6), "ט", "י"), "כ", "ל", "מ", "נ", "ס", "ע", "פ", "צ"),
  fourthPart, CHOOSE(u+1, ,"א","ב","ג","ד", IF(--d=1, "ו", "ה"), IF(--d=1, "ז", "ו"), "ז", "ח", "ט"),
 
 
  firstPart & secondPart & thirdPart & fourthPart
)
  • תודה
  • וואו!
  • שכוייח!
Reactions: משפחת ב, משה פפירוב, yyff ועוד 3 משתמשים