سؤال كيف يمكنني ضم ورقتين عمل في Excel كما أقوم باستخدام SQL؟


لدي ورقة عمل في ملفين Excel مختلفين. كلاهما يحتوي على قائمة من الأسماء وأرقام المعرفات والبيانات المرتبطة بها. أحدهما هو قائمة رئيسية تحتوي على مجالات ديموغرافية عامة ، والآخر هو قائمة تتضمن فقط الاسم والمعرف ، والعنوان. تم إيقاف هذه القائمة من القائمة الرئيسية بواسطة مكتب آخر.

أرغب في استخدام القائمة الثانية لتصفية أول. بالإضافة إلى ذلك ، أريد أن تتضمن النتائج حقولًا أخرى من ورقة العمل الرئيسية إلى جانب حقول العناوين من ورقة العمل الثانية. أعلم كيف يمكنني القيام بذلك بسهولة بالاقتران الداخلي لقاعدة البيانات ، لكني أقل وضوحا بشأن كيفية القيام بذلك بفعالية في Excel. كيف يمكن ضم ورقتين عمل في Excel؟ نقاط المكافأة لإظهار كيفية القيام الصلات الخارجية كذلك ، وأنا أفضل كثيرا معرفة كيفية القيام بذلك دون الحاجة إلى ماكرو.


110
2018-05-04 16:20


الأصل


أجد الصفحة التالية مفيدة جدًا: randomwok.com/excel/how-to-use-index-match - Tommy Bravo
randomwok.com/excel/how-to-use-index-match => خاصة "التذكير" السهل في النهاية: =INDEX ( Column_I_want_a_return_value_from , ( MATCH ( My_Lookup_Value , Column_I_want_to_Lookup_against , 0 )) - Tommy Bravo


الأجوبة:


لاستخدام 2007+ Data > From Other Sources > From Microsoft Query:

  1. أختر Excel File وحدد التفوق الأول الخاص بك
  2. اختر الأعمدة
     (إذا لم تر أي قائمة من الأعمدة ، فتأكد من التحقق Options > System Tables)
  3. اذهب إلى Data > Connections > [اختر الاتصال الذي تم إنشاؤه للتو]> Properties > Definition > Command text

يمكنك الآن تحرير هذا Command text كما SQL. لست متأكدا ما هو suported بناء الجملة ، ولكن حاولت الصلات الضمنية ، "الانضمام الداخلي" ، "اليسار الانضمام" والنقابات التي تعمل كل. هذا مثال لاستعلام عينة:

SELECT *
FROM `C:\Users\Peter\Documents\Excel-to-excel\Source_1.xlsx`.`Sheet1$` a
LEFT JOIN `C:\Users\Peter\Documents\Excel-to-excel\Source_2.xlsx`.`Sheet1$` b
ON a.col2 = b.col2

151
2018-05-07 09:37



هل هناك طريقة لتجنب hardcoding المسار؟ هل يعمل المسار النسبي؟ - Rekin
كمكافأة إضافية ، يتيح لك Microsoft Query تجربة عناصر تحكم Common ذات نمط 16 بت لفتح ملف Excel ولمربعات الرسائل. بهذه الطريقة يمكنك أن تتذكر عندما كنت صغيرا. :-) - Edward Brey
هل يعمل هذا مع ملفات CSV؟ أنا أستخدم MS Office Professional Plus 2010 ولا أرى كيفية اتباع الخطوة 3 - هل أترك مربع الحوار في الخطوة 2 معلقًا؟ لا يوجد "اختر اتصالك الجديد" في مربع حوار "الاتصالات". - John Freeman
JohnFreeman لأنه يجب عليك أختر أحد الخيارات المتاحة وفقًا لاحتياجاتك (على سبيل المثال "إضافة ...") - Aprillion
لاحظ أنه قد تحصل على مربع حوار يقول "مصدر البيانات هذا لا يحتوي على جداول مرئية" ، متبوعًا بمربع حوار "معالج استعلام - اختيار أعمدة". إنها من هذه مربع الحوار الذي تريد النقر فوق الزر "خيارات" ، ثم حدد مربع الاختيار "جداول النظام" لمشاهدة البيانات التي تريد الاستعلام عنها. - Tola Odejayi


دعم الجواب المقبول. أريد فقط التأكيد على "اختيار الأعمدة (إذا لم تر أي قائمة بالأعمدة ، فتأكد من تحديد الخيارات> جداول النظام)"

بمجرد تحديد ملف اكسل ، من المرجح جدا سترى this data source contains no visible tables مطالبة ، وعلامات التبويب والأعمدة المتوفرة لا شيء. مايكروسوفت اعترف أن هذا هو علة  يتم التعامل مع علامات التبويب في ملفات excel كـ "جداول النظام" ، ولا يتم تحديد الخيار "جداول النظام" بشكل افتراضي. لذلك لا داعي للذعر في هذه الخطوة ، تحتاج فقط إلى النقر فوق "الخيار" وتحقق "جداول النظام" ، ثم تشاهد الأعمدة المتاحة.


11
2017-12-09 18:20





يمكن استخدام VLOOKUP و HLOOKUP للبحث عن المفاتيح الأساسية المطابقة (المخزنة عموديًا أو أفقيًا) وإرجاع القيم من أعمدة / أعمدة "السمة".


8
2018-05-04 16:22



مفيدة جدا لأوراق على نفس المصنف (ولكن أجد وظائف INDEX + MATCH أكثر فائدة) ، قليلا أكثر تعقيدا عند تحديث البيانات من المصنفات الخارجية المغلقة ... - Aprillion


لا يمكنك تهيئة صلات نمط SQL على جداول Excel من داخل Excel. ومع ذلك ، هناك طرق متعددة لإنجاز ما تحاول القيام به.

في Excel ، كما يقول Reuben ، فإن الصيغ التي ستعمل على الأرجح هي الأفضل VLOOKUP و HLOOKUP. في كلتا الحالتين ، تتطابق مع صف فريد وترجع قيمة العمود \ العمود المحدد إلى اليسار \ للأسفل من المعرف الذي تم العثور عليه.

إذا كنت تريد فقط إضافة حقلين إضافيين إلى القائمة الثانية ، فقم بإضافة الصيغ إلى القائمة الثانية. إذا كنت تريد جدول نمط "صلة خارجية" ، فقم بإضافة VLOOKUP صيغة إلى القائمة الأولى مع ISNA لاختبار ما إذا تم العثور على البحث. إذا لم تعطك تعليمات Excel تفاصيل كافية حول كيفية استخدام هذه في حالة معينة ، فأخبرنا بذلك.

إذا كنت تفضل استخدام SQL ثم ربط البيانات في برنامج قاعدة البيانات ، قم بإنشاء استعلامك ، وقم بتصدير النتائج مرة أخرى إلى Excel. (في Access يمكنك استيراد Excel Worksheets أو Named Ranges في جدول مرتبط.)


4
2018-05-04 17:29



في الواقع ، يمكنك - يطلق عليه Microsoft Query - Aprillion


يمكنك استخدام Microsoft Power Query ، وهي متوفرة للإصدارات الأحدث من Excel (مشابهة للإجابة المقبولة ، ولكنها أبسط وأسهل). يستدعي Power Query "الدمج" للارتباطات.

أسهل طريقة هي أن يكون لديك أوراق Excel 2 كجداول Excel. ثم في Excel ، انتقل إلى علامة تبويب الشريط Power Query ، وانقر فوق الزر "من Excel". بمجرد استيراد كل من الجدولين إلى Power Query ، حدد واحدًا وانقر فوق "دمج".


4
2018-05-17 14:09



أسهل بكثير من أي من الخيارات المقترحة الأخرى. هذا يجب أن يكون الجواب! استفسار الطاقة يتم تضمينها الآن في Excel 2016 تحت البيانات التبويب. - SliverNinja - MSFT
ميزة أخرى لا يبدو أنها موجودة على نظام التشغيل Mac - juandesant


في XLTools.net قمنا بإنشاء بديل جيد لـ MS Query للعمل بشكل خاص مع جداول SQL لاستعلامات مقابل Excel. يدعي XLTools استعلامات SQL. فمن الأسهل كثيرا استخدام بدلا من MS Query ويعمل بشكل جيد إذا كنت بحاجة فقط لإنشاء وتشغيل SQL - لا VBA ، لا التلاعب المعقدة مع MS Query ...

باستخدام هذه الأداة ، يمكنك إنشاء أي استعلام SQL مقابل الجداول في مصنف (ملفات) Excel باستخدام محرر SQL المضمن وتشغيله على الفور مع خيار وضع النتيجة في ورقة عمل جديدة أو أي ورقة عمل موجودة.

يمكنك استخدام أي نوع من أنواع الانضمام بما في ذلك LEFT OUTER JOIN (لا يتم دعم سوى RIGHT OUTER JOIN و FULL OUTER JOIN).

هنا مثال:

XLTools SQL Queries - Query Builder


3
2017-07-04 22:25





بالنسبة لبرنامج Excel 2007: البيانات> من مصادر أخرى> من Microsoft Query> استعرض إلى ملف Excel

بالنسبة الى هذه المقالة، قد يؤدي الاستعلام من XLS الإصدار 2003 إلى "مصدر البيانات هذا لا يحتوي على جداول مرئية." خطأ لأن أوراق العمل الخاصة بك يتم التعامل معها كجدول SYSTEM. لذلك تحقق من "جداول النظام" في خيارات الحوار "معالج استعلام - اختيار أعمدة" عند إنشاء الاستعلام سوف تعمل مستديرة.

لتحديد الانضمام الخاص بك: مربع حوار Microsoft Query> قائمة الجدول> انضمام ...

لإرجاع البيانات إلى ورقة Excel الأصلية ، اختر "إرجاع البيانات إلى ورقة Excel" من مربع حوار Microsoft Query> القائمة ملف.


2
2017-07-16 02:41





بينما أعتقد أن إجابة Aprillion باستخدام Microsoft Query ممتازة ، فقد ألهمني استخدامها مدخل البرمجيات المرنة للانضمام إلى أوراق البيانات التي وجدتها أسهل بكثير.

تحتاج إلى تثبيت MS Access بالطبع.

خطوات:

  • إنشاء قاعدة بيانات Access جديدة (أو استخدم datch الصفر).
  • استعمال Get External Data لاستيراد بيانات Excel كجداول جديدة.
  • استعمال Relationships لإظهار كيفية انضمام الجداول الخاصة بك.
  • عيِّن نوع العلاقة لمطابقة ما تريده (يمثل انضمامًا إلى اليسار وما إلى ذلك)
  • قم بإنشاء استعلام جديد ينضم إلى الجداول الخاصة بك.
  • استعمال External Data->Export to Excel لتوليد نتائجك.

لم أستطع فعل ذلك بدون إجابة Aprillion الرائعة.


2
2018-02-12 11:00





إذا كنت على دراية كافية بقواعد البيانات ، فيمكنك استخدام SQL Server لربط كل من أوراق العمل كخوادم مرتبطة ثم استخدام T-SQL لتنفيذ عمل البيانات الخلفية. ثم إنهاء عن طريق توصيل Excel إلى SQL وسحب البيانات إلى جدول (عادي أو pivot). يمكنك أيضًا التفكير في استخدام Powerpivot؛ سيسمح بالربط بين أي من مصادر قواعد البيانات - بما في ذلك Excel المستخدم كقواعد بيانات مسطحة.


0
2018-05-04 16:44



أنت على حق ، ولكن السؤال الأولي يستند إلى استخدام جدول بيانات (أو اثنين) للقيام بوظيفة قاعدة بيانات ، لذلك لست متأكداً من أي طريقة فعالة لإنجاز المهمة. - dav


تبحث عن نفس المشكلة جئت عبر RDBMergeوالتي أعتقد أنها طريقة سهلة الاستخدام لدمج البيانات من عدة مصنفات Excel و csv و xml في مصنف مختصر.


0
2018-05-25 17:19