עזרה באקסל יצירת טבלת אקסל מתוך טבלה אחרת

צביזון

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

מתמצא

משתמש פעיל
לכאורה אין אפשרות לבצע זאת על ידי נוסחה רגילה מכיון שאתה מבקש לעשות נוסחה שתהיה קשובה אוטמטית ל-2 הטווחים, הן לטווח שבו אתה מזין את הערים, ובמקביל לעדכן את הטווח שמכיל את קיבוץ כל הערים
מכיון שלאקסל אין חיבור מוטמע ל SQL
לדעתי הכי פשוט זה קוד VBA שאחרי כל שינוי בעמודה מעתיק את העמודה ומדביק אותה בגליון המקביל
ואז מריץ על העמודה המקבילה 'הסר כפיליות'
זה הקוד (כמובן שצריך להתאים אותו לטווחים שלך)
מצו"ב גם דוגמא
קוד:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
ActiveSheet.Range("a1", ActiveSheet.Range("a" & ActiveSheet.Rows.Count).End(xlUp)).Copy
Sheets(2).Range("a1", Sheets(2).Range("a" & Sheets(2).Rows.Count).End(xlUp)).PasteSpecial Paste:=xlPasteValues
Sheets(2).Range("a1", Sheets(2).Range("a" & Sheets(2).Rows.Count).End(xlUp)).RemoveDuplicates Columns:=1
End If
End Sub
 

קבצים מצורפים

  • דוגמא.rar
    KB 14.9 · צפיות: 12
נערך לאחרונה ב:

צביזון

משתמש מקצוען
עיצוב גרפי
תודה רבה!

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

כעת אחרי כל עדכון שורה זה חושב כ-2 שניות. וחוששני שככל שהקובץ יגדל, יגדל הזמן הזה. האם זה אכן כך.
 

מתמצא

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

לגבי שינוי הטווחים
בקוד המצו"ב בשורה מספר 3 מופיע התא "a1" זה בעצם מורה לקוד להעתיק משם ומטה
אם אתה רוצה טווח אחר, תחליף, לדוגמא אם הטווח שלך נמצאת בעמודה D ומתחיל (אחרי הכותרת) בשורה 2, תכניס שם "D2"
אם תרצה לשנות גם את הטווח בגליון המקביל תעשה אותו דבר בשורה 3 ו-4 בקוד
 

אפר

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

קבצים מצורפים

  • הדגמה.xlsx
    KB 10.5 · צפיות: 19

צביזון

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

צביזון

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

לגבי שינוי הטווחים
בקוד המצו"ב בשורה מספר 3 מופיע התא "a1" זה בעצם מורה לקוד להעתיק משם ומטה
אם אתה רוצה טווח אחר, תחליף, לדוגמא אם הטווח שלך נמצאת בעמודה D ומתחיל (אחרי הכותרת) בשורה 2, תכניס שם "D2"
אם תרצה לשנות גם את הטווח בגליון המקביל תעשה אותו דבר בשורה 3 ו-4 בקוד
תודה רבה.
אם אני אכן רוצה נאמר מטווח 2 ועד 10000 במקום עד 999999999, איך אני עושה את זה?
 

מתמצא

משתמש פעיל
תודה רבה.
אם אני אכן רוצה נאמר מטווח 2 ועד 10000 במקום עד 999999999, איך אני עושה את זה?
פשוט תכניס את הטווח במקום ה A1 ככה
קוד:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
ActiveSheet.Range("a1:a5").Copy
Sheets(2).Range("a1", Sheets(2).Range("a" & Sheets(2).Rows.Count).End(xlUp)).PasteSpecial Paste:=xlPasteValues
Sheets(2).Range("a1", Sheets(2).Range("a" & Sheets(2).Rows.Count).End(xlUp)).RemoveDuplicates Columns:=1
End If
End Sub
 

אפר

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

מתמצא

משתמש פעיל
לא הבנתי בדיוק איך זה עובד. אבל אני רואה שצריך להוסיף עוד מספר בעמודה נוספת בשביל שזה יעבוד. וגם שם לא כ"כ ברור איך להוסיף מספר. האם שייך שזה יהיה אוטומטי?
הדוגמא של @אפר היא אכן אוטומטית ומאידך לא משתמשת עם קוד VBA (ולא כפי שחשבתי שזה לא אפשרי)
העמודה 'עזר' היא כלי עזר לנוסחאות שהוא הטמיע בתאים, בכך שהיא מעלה את הספירה בכל פעם שהיא 'פוגשת' עיר חדשה בעמודה, וכך הנוסחה בעמודת 'מקוצר' שולפת את מה שמופיע ליד כל מספר
את העמודה 'עזר' אפשר גם להעביר למקום מוסתר בגליון שלא יהיה גלוי לעיני המשתמש, זה בסה"כ 'צומת' בין 2 הטווחים
אין צורך להזין בה כלום, היא פשוט קוראת את העמודה 'עיר' ומשדרת לעמודת 'מקוצר'
 

צביזון

משתמש מקצוען
עיצוב גרפי
אגב, הדבקתי הקוד הזה ב
פשוט תכניס את הטווח במקום ה A1 ככה
קוד:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
ActiveSheet.Range("a1:a5").Copy
Sheets(2).Range("a1", Sheets(2).Range("a" & Sheets(2).Rows.Count).End(xlUp)).PasteSpecial Paste:=xlPasteValues
Sheets(2).Range("a1", Sheets(2).Range("a" & Sheets(2).Rows.Count).End(xlUp)).RemoveDuplicates Columns:=1
End If
End Sub
ניסיתי לעשות כמו הקוד שבחוברת שלך.
הדבקתי בגליון1 קוד זה:
1617885624680.png

ובגיליון2 קוד זה:
1617885655579.png


אבל זה לא עושה את העבודה.
האם מלבד להדביק הנ"ל יש עוד הגדרה שצריך להגדיר?
 

צביזון

משתמש מקצוען
עיצוב גרפי
תעשה שמור בשם לקובץ אקסל המאפשר מאקרוס'
צפה בקובץ המצורף 864170
לא זו היתה הבעיה. תפסתי את הנקודה.
לפי הנוסחה שהדבקתי למעלה, העדכון נעשה כאשר אני כותב משהו בעמודה A, על אף שאת שאיבת הנתונים הוא שואב מעמודה C.
מה צריך עוד לשנות כדי שהעדכון יבוצע בכתיבה בתוך הטווח שעשיתי ב-C3:C100 ?

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

אפר

סתם מתעניין...
מנוי פרימיום
לא זו היתה הבעיה. תפסתי את הנקודה.
לפי הנוסחה שהדבקתי למעלה, העדכון נעשה כאשר אני כותב משהו בעמודה A, על אף שאת שאיבת הנתונים הוא שואב מעמודה C.
מה צריך עוד לשנות כדי שהעדכון יבוצע בכתיבה בתוך הטווח שעשיתי ב-C3:C100 ?

ואגב, נראה לי משום מה, שכעת זה מעדכן יותר לאט מאשר הנוסחה המקורית. אבל אולי זה בגלל שהעדכון נעשה ע"י עמודה שונה מעמודת שאיבת הנתונים?
תחליף ל TARGET.COLUMN = 3
 

xl3391

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

אולי מעניין אותך גם...

הפרק היומי

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


תהילים פרק קיט א'

א אַשְׁרֵי תְמִימֵי דָרֶךְ הַהֹלְכִים בְּתוֹרַת יְהוָה:ב אַשְׁרֵי נֹצְרֵי עֵדֹתָיו בְּכָל לֵב יִדְרְשׁוּהוּ:ג אַף לֹא פָעֲלוּ עַוְלָה בִּדְרָכָיו הָלָכוּ:ד אַתָּה צִוִּיתָה פִקֻּדֶיךָ לִשְׁמֹר מְאֹד:ה אַחֲלַי יִכֹּנוּ דְרָכָי לִשְׁמֹר חֻקֶּיךָ:ו אָז לֹא אֵבוֹשׁ בְּהַבִּיטִי אֶל כָּל מִצְוֹתֶיךָ:ז אוֹדְךָ בְּיֹשֶׁר לֵבָב בְּלָמְדִי מִשְׁפְּטֵי צִדְקֶךָ:ח אֶת חֻקֶּיךָ אֶשְׁמֹר אַל תַּעַזְבֵנִי עַד מְאֹד:
נקרא  21  פעמים

לוח מודעות

למעלה