يمكنك استخدام Microsoft Query لاسترداد البيانات من مصادر خارجية. باستخدام Microsoft Query لاسترداد البيانات من قواعد بيانات الشركة وملفاتها، لن تحتاج إلى إعادة كتابة البيانات التي تريد تحليلها في Excel. يمكنك أيضا تحديث تقارير Excel وملخصاته تلقائيا من قاعدة البيانات المصدر الأصلية كلما تم تحديث قاعدة البيانات بمعلومات جديدة.
تعرف على المزيد حول Microsoft Query
باستخدام Microsoft Query، يمكنك الاتصال بمصادر بيانات خارجية، وتحديد بيانات من تلك المصادر الخارجية، واستيراد تلك البيانات إلى ورقة العمل، وتحديث البيانات كما تقتضي الحاجة للحفاظ على مزامنة بيانات ورقة العمل مع البيانات الموجودة في المصادر الخارجية.
أنواع قواعد البيانات التي يمكنك الوصول إليها يمكنك استرداد البيانات من أنواع متعددة من قواعد البيانات، بما في ذلك Microsoft Office Access وMicrosoft SQL Server وMicrosoft SQL Server OLAP Services. تستطيع أيضا استرداد البيانات من مصنفات Excel ومن الملفات النصية.
يوفر Microsoft Office برامج التشغيل التي يمكنك استخدامها لاسترداد البيانات من مصادر البيانات التالية:
- Microsoft SQL Server Analysis Services (موفر OLAP)
- Microsoft Office Access
- dBASE
- Microsoft FoxPro
- Microsoft Office Excel
- Oracle
- مفارقة
- قواعد بيانات الملفات النصية
يمكنك أيضا استخدام برامج تشغيل ODBC أو برامج تشغيل مصادر البيانات من الشركات المصنعة الأخرى لاسترداد المعلومات من مصادر البيانات غير المدرجة هنا، بما في ذلك الأنواع الأخرى من قواعد بيانات OLAP. للحصول على معلومات حول تثبيت برنامج تشغيل ODBC أو برنامج تشغيل مصدر بيانات غير مدرج هنا، راجع الوثائق الخاصة بقاعدة البيانات، أو اتصل بمورد قاعدة البيانات.
تحديد البيانات من قاعدة بيانات يمكنك استرداد البيانات من قاعدة بيانات عن طريق إنشاء استعلام، وهو سؤال تطرحه حول البيانات المخزنة في قاعدة بيانات خارجية. على سبيل المثال، إذا كانت البيانات مخزنة في قاعدة بيانات Access، فقد ترغب في معرفة أرقام المبيعات لمنتج معين حسب المنطقة. يمكنك استرداد جزء من البيانات عن طريق تحديد بيانات المنتج والمنطقة التي تريد تحليلها فقط.
باستخدام Microsoft Query، يمكنك تحديد أعمدة البيانات التي تريدها واستيراد تلك البيانات فقط إلى Excel.
تحديث ورقة العمل في عملية واحدة عندما يكون لديك بيانات خارجية في مصنف Excel، فبإمكانك تحديث البيانات لتحديث تحليلك كلما تغيرت قاعدة البيانات — من دون الحاجة إلى إعادة إنشاء المخططات والتقارير الملخصة. على سبيل المثال، يمكنك إنشاء ملخص مبيعات شهري وتحديثه كل شهر عند ظهور أرقام المبيعات الجديدة.
كيف يستخدم Microsoft Query مصادر البيانات بعد إعداد مصدر بيانات لقاعدة بيانات معينة، يمكنك استخدامه كلما أردت إنشاء استعلام لتحديد البيانات واستردادها من قاعدة البيانات هذه — دون الحاجة إلى إعادة كتابة كافة معلومات الاتصال. يستخدم Microsoft Query مصدر البيانات للاتصال بقاعدة البيانات الخارجية وعرض البيانات المتوفرة لك. بعد إنشاء الاستعلام وإرجاع البيانات إلى Excel، يوفر Microsoft Query لمصنف Excel معلومات الاستعلام ومصدر البيانات بحيث يمكنك إعادة الاتصال بقاعدة البيانات عندما تريد تحديث البيانات.
باستخدام Microsoft Query لاستيراد البيانات لاستيراد البيانات الخارجية إلى Excel باستخدام Microsoft Query، اتبع الخطوات الأساسية التالية، ويتم وصف كل منها بمزيد من التفصيل في الأقسام التالية.
الاتصال بمصدر بيانات
ما هو مصدر البيانات؟ إن مصدر البيانات عبارة عن مجموعة معلومات مخزنة تسمح لبرنامج Excel وMicrosoft Query بالاتصال بقاعدة بيانات خارجية. عند استخدام Microsoft Query لإعداد مصدر بيانات، يجب أن تمنح مصدر البيانات اسما، ثم توفر اسم قاعدة البيانات أو الخادم وموقعه ونوع قاعدة البيانات ومعلومات تسجيل دخولك وكلمة المرور. تتضمن المعلومات أيضا اسم برنامج تشغيل OBDC أو برنامج تشغيل مصدر البيانات، وهو برنامج يقوم بإجراء اتصالات بنوع معين من قاعدة البيانات.
لإعداد مصدر بيانات باستخدام Microsoft Query:
على علامة التبويب " بيانات "، في المجموعة "إحضار بيانات خارجية "، انقر فوق "من مصادر أخرى"، ثم انقر فوق "من Microsoft Query".
ملاحظة
نقل Excel 365 Microsoft Query إلى مجموعة قوائم المعالجات القديمة . لا يتم عرض هذه القائمة بشكل افتراضي. للتمكين، انتقل إلى الملفاتوالخياراتوالبيانات والتمكين في القسم إظهار معالجات استيراد البيانات القديمة .
قم بتنفيذ أحد الإجراءات التالية:
- لتحديد مصدر بيانات لقاعدة بيانات أو ملف نصي أو مصنف Excel، انقر فوق علامة التبويب " قواعد البيانات ".
- لتحديد مصدر بيانات مكعب OLAP، انقر فوق علامة التبويب مكعبات OLAP . تتوفر علامة التبويب هذه فقط إذا قمت بتشغيل Microsoft Query من Excel.
انقر نقرا مزدوجا فوق <"مصدر> بيانات جديد".
-أو-
انقر فوق <"مصدر> بيانات جديد"، ثم فوق "موافق".
يتم عرض مربع الحوار "إنشاء مصدر بيانات جديد ".في الخطوة 1، اكتب اسما لتعريف مصدر البيانات.
في الخطوة 2، انقر فوق برنامج تشغيل لنوع قاعدة البيانات التي تستخدمها كمصدر بيانات.
ملاحظة
- إذا كانت قاعدة البيانات الخارجية التي تريد الوصول إليها غير مدعومة من قبل برامج تشغيل ODBC المثبتة مع Microsoft Query، فستحتاج إلى الحصول على برنامج تشغيل ODBC متوافق مع Microsoft Office وتثبيته من مورد خارجي، مثل الشركة المصنعة لقاعدة البيانات. اتصل بمورد قاعدة البيانات للحصول على إرشادات التثبيت.
- لا تتطلب قواعد بيانات OLAP برامج تشغيل ODBC. عند تثبيت Microsoft Query، يتم تثبيت برامج التشغيل لقواعد البيانات التي تم إنشاؤها باستخدام Microsoft SQL Server Analysis Services. للاتصال بقواعد بيانات OLAP أخرى، ستحتاج إلى تثبيت برنامج تشغيل وعميل لمصدر البيانات.
انقر فوق اتصال، ثم قدم المعلومات المطلوبة للاتصال بمصدر البيانات. بالنسبة لقواعد البيانات ومصنفات Excel والملفات النصية، تعتمد المعلومات التي توفرها على نوع مصدر البيانات الذي حددته. قد يطلب منك توفير اسم تسجيل الدخول أو كلمة المرور أو إصدار قاعدة البيانات التي تستخدمها أو موقع قاعدة البيانات أو أي معلومات أخرى خاصة بنوع قاعدة البيانات.
هام
- استخدم كلمات مرور قوية تجمع ما بين أحرف كبيرة وأحرف صغيرة وأرقام ورموز. فلا تحتوي كلمات المرور الضعيفة على هذه العناصر المختلطة. من أمثلة كلمات المرور القوية: Y6dh!et5. ومن أمثلة كلمات المرور الضعيفة: House27. يجب أن تكون كلمات المرور مؤلفة من 8 أحرف أو أكثر. ومن الأفضل استخدام عبارة مرور مؤلفة من 14 حرفًا أو أكثر.
- كما أنه من المهم أن تتذكر كلمة المرور الخاصة بك. فإذا نسيت كلمة المرور الخاصة بك، لن تتمكن Microsoft من استردادها. لذلك، اعمل على تخزين كلمات المرور التي تسجلها في مكان آمن بعيداً عن المعلومات التي تساعد في حمايتها.
بعد إدخال المعلومات المطلوبة، انقر فوق موافق أو إنهاء للعودة إلى مربع الحوار إنشاء مصدر بيانات جديد .
إذا كانت قاعدة البيانات تحتوي على جداول وتريد عرض جدول معين تلقائيا في "معالج الاستعلامات"، انقر فوق المربع الخاص بالخطوة 4، ثم انقر فوق الجدول الذي تريده.
إذا كنت لا تريد كتابة اسم تسجيل الدخول وكلمة المرور عند استخدام مصدر البيانات، فحدد خانة الاختيار حفظ معرف المستخدم وكلمة المرور في تعريف مصدر البيانات . كلمة المرور المحفوظة غير مشفرة. إذا لم تكن خانة الاختيار متوفرة، فراجع مسؤول قاعدة البيانات لتحديد ما إذا كان هذا الخيار متوفرا أم لا.
ملاحظة
تجنب حفظ معلومات تسجيل الدخول عند الاتصال بمصادر البيانات. قد يتم تخزين هذه المعلومات كنص عادي، وقد يتمكن مستخدم ضار من الوصول إلى المعلومات لتعريض أمان مصدر البيانات للخطر.
بعد إكمال هذه الخطوات، يظهر اسم مصدر البيانات في مربع الحوار "اختيار مصدر البيانات ".
استخدام "معالج الاستعلامات" لتعريف استعلام
استخدام "معالج الاستعلامات" لمعظم الاستعلامات يسهل "معالج الاستعلامات" تحديد البيانات وتجميعها من جداول وحقول مختلفة في قاعدة البيانات. باستخدام "معالج الاستعلامات"، يمكنك تحديد الجداول والحقول التي تريد تضمينها. يتم إنشاء الصلة الداخلية (عملية استعلام تحدد دمج صفوف من جدولين استنادا إلى قيم حقل متطابقة) تلقائيا عندما يتعرف المعالج على حقل مفتاح أساسي في جدول واحد وعلى حقل بالاسم نفسه في جدول ثان.
يمكنك أيضا استخدام المعالج لفرز مجموعة النتائج والقيام بتصفية بسيطة. في الخطوة الأخيرة من المعالج، يمكنك اختيار إعادة البيانات إلى Excel، أو إجراء مزيد من التنقيح في Microsoft Query. بعد إنشاء الاستعلام، يمكنك تشغيله في Excel أو في Microsoft Query.
لبدء تشغيل "معالج الاستعلامات"، نفذ الخطوات التالية.
- على علامة التبويب " بيانات "، في المجموعة "إحضار بيانات خارجية "، انقر فوق "من مصادر أخرى"، ثم انقر فوق "من Microsoft Query".
- في مربع الحوار "اختيار مصدر البيانات "، تأكد من تحديد خانة الاختيار استخدام معالج الاستعلامات لإنشاء وتحرير الاستعلامات .
- انقر نقرا مزدوجا فوق مصدر البيانات الذي تريد استخدامه.
-أو-
انقر فوق مصدر البيانات الذي تريد استخدامه، ثم انقر فوق موافق.
العمل مباشرة في Microsoft Query عن أنواع أخرى من الاستعلامات إذا كنت تريد إنشاء استعلام أكثر تعقيدا مما يسمح به "معالج الاستعلامات"، يمكنك العمل مباشرة في Microsoft Query. يمكنك استخدام Microsoft Query لعرض الاستعلامات التي تبدأ في إنشائها في معالج الاستعلامات وتغييرها، أو يمكنك إنشاء استعلامات جديدة دون استخدام المعالج. استخدم Microsoft Query مباشرة عندما تريد إنشاء استعلامات تقوم بما يلي:
- تحديد بيانات معينة من حقل في قاعدة بيانات كبيرة، قد تحتاج إلى اختيار بعض البيانات الموجودة في حقل وحذف البيانات التي لا تحتاج إليها. على سبيل المثال، إذا كنت بحاجة إلى بيانات لمنتجين في حقل يحتوي على معلومات حول عدة منتجات، يمكنك استخدام المعايير لتحديد بيانات للمنتجين المطلوبين فقط.
- استرداد البيانات استنادا إلى معايير مختلفة في كل مرة تقوم فيها بتشغيل الاستعلام إذا كنت بحاجة إلى إنشاء نفس تقرير Excel أو الملخص لعدة مناطق في نفس البيانات الخارجية — مثل تقرير مبيعات منفصل لكل منطقة — يمكنك إنشاء معلمة استعلام. عند تشغيل معلمة استعلام، تتم مطالبتك بقيمة لاستخدامها كمعيار عندما يحدد الاستعلام السجلات. على سبيل المثال، قد يطالبك استعلام معلمة بإدخال منطقة معينة، ويمكنك إعادة استخدام هذا الاستعلام لإنشاء كل تقرير من تقارير المبيعات الإقليمية.
- ضم البيانات بطرق مختلفة الصلات الداخلية التي يقوم "معالج الاستعلامات" بإنشائها هي النوع الأكثر شيوعا من الصلات المستخدمة في إنشاء الاستعلامات. ومع ذلك، في بعض الأحيان ترغب في استخدام نوع مختلف من الصلات. على سبيل المثال، إذا كان لديك جدول يتضمن معلومات مبيعات المنتجات وجدولا يتضمن معلومات العملاء، فإن الصلة الداخلية (النوع الذي تم إنشاؤه بواسطة معالج الاستعلامات) ستمنع استرداد سجلات العملاء للعملاء الذين لم يقوموا بعملية شراء. باستخدام Microsoft Query، يمكنك ربط هذه الجداول حتى يتم استرداد كل سجلات العملاء، بالإضافة إلى بيانات المبيعات لهؤلاء العملاء الذين قاموا بعمليات شراء.
لبدء تشغيل Microsoft Query، نفذ الخطوات التالية.
- على علامة التبويب " بيانات "، في المجموعة "إحضار بيانات خارجية "، انقر فوق "من مصادر أخرى"، ثم انقر فوق "من Microsoft Query".
- في مربع الحوار "اختيار مصدر البيانات "، تأكد من إلغاء تحديد خانة الاختيار استخدام معالج الاستعلامات لإنشاء وتحرير الاستعلامات .
- انقر نقرا مزدوجا فوق مصدر البيانات الذي تريد استخدامه.
-أو-
انقر فوق مصدر البيانات الذي تريد استخدامه، ثم انقر فوق موافق.
إعادة استخدام الاستعلامات ومشاركتها في كل من معالج الاستعلامات وMicrosoft Query، يمكنك حفظ الاستعلامات كملف .dqy يمكنك تعديله وإعادة استخدامه ومشاركته. يمكن أن يفتح Excel ملفات .dqy مباشرة، مما يسمح لك أو للمستخدمين الآخرين بإنشاء نطاقات بيانات خارجية إضافية من نفس الاستعلام.
لفتح استعلام محفوظ من Excel:
- على علامة التبويب " بيانات "، في المجموعة "إحضار بيانات خارجية "، انقر فوق "من مصادر أخرى"، ثم انقر فوق "من Microsoft Query". يتم عرض مربع الحوار "اختيار مصدر البيانات ".
- في مربع الحوار "اختيار مصدر البيانات "، انقر فوق علامة التبويب "استعلامات ".
- انقر نقرا مزدوجا فوق الاستعلام المحفوظ الذي تريد فتحه. يتم عرض الاستعلام في Microsoft Query.
إذا كنت تريد فتح استعلام محفوظ وكان Microsoft Query مفتوحا بالفعل، فانقر فوق القائمة "ملف Microsoft Query"، ثم انقر فوق "فتح".
إذا نقرت نقرا مزدوجا فوق ملف dqy.، فسيفتح Excel الاستعلام ويقوم بتشغيله، ثم إدراج النتائج في ورقة عمل جديدة.
إذا كنت تريد مشاركة ملخص أو تقرير Excel يستند إلى بيانات خارجية، فيمكنك أن تقدم للمستخدمين الآخرين مصنفا يحتوي على نطاق بيانات خارجية، أو يمكنك إنشاء قالب. يسمح لك القالب بحفظ الملخص أو التقرير بدون حفظ البيانات الخارجية بحيث يكون الملف أصغر حجما. يتم استرداد البيانات الخارجية عندما يفتح المستخدم قالب التقرير.
استخدام البيانات في Excel
بعد إنشاء استعلام إما في "معالج الاستعلامات" أو Microsoft Query، يمكنك إعادة البيانات إلى ورقة عمل Excel. ثم تصبح البيانات نطاق بيانات خارجية أو تقرير PivotTable يمكنك تنسيقه وتحديثه.
تنسيق البيانات المستردة في Excel، يمكنك استخدام أدوات، مثل المخططات أو الإجماليات الفرعية التلقائية، لتقديم البيانات التي يتم استردادها بواسطة Microsoft Query وتلخيصها. يمكنك تنسيق البيانات، وسيتم الاحتفاظ بتنسيقك عند تحديث البيانات الخارجية. يمكنك استخدام تسميات الأعمدة الخاصة بك بدلا من أسماء الحقول، وإضافة أرقام الصفوف تلقائيا.
بإمكان Excel أن يقوم تلقائيا بتنسيق البيانات الجديدة التي تكتبها في نهاية نطاق بحيث تتطابق مع الصفوف السابقة. بإمكان Excel أيضا نسخ الصيغ التي تم تكرارها في الصفوف السابقة تلقائيا وتوسيعها لتشمل صفوفا إضافية.
ملاحظة
لكي يتم توسيعها لتشمل صفوفا جديدة في النطاق، يجب أن تظهر التنسيقات والصيغ في ثلاثة على الأقل من الصفوف الخمسة السابقة.
يمكنك تشغيل هذا الخيار (أو إيقاف تشغيله مرة أخرى) في أي وقت:
- انقر فوقخيارات>الملف>خيارات متقدمة.
- في المقطع خيارات التحرير ، حدد التحقق من توسيع تنسيقات نطاق البيانات والصيغ . لإيقاف تشغيل التنسيق التلقائي لنطاق البيانات مرة أخرى، قم بإلغاء تحديد خانة الاختيار هذه.
تحديث البيانات الخارجية عند تحديث البيانات الخارجية، فإنك تقوم بتشغيل الاستعلام لاسترداد أي بيانات جديدة أو متغيرة تتطابق مع مواصفاتك. يمكنك تحديث استعلام في كل من Microsoft Query وExcel. يوفر Excel العديد من الخيارات لتحديث الاستعلامات، بما في ذلك تحديث البيانات كلما فتحت المصنف وتحديثه تلقائيا في فواصل زمنية محددة. يمكنك متابعة العمل في Excel أثناء تحديث البيانات، ويمكنك أيضا التحقق من الحالة أثناء تحديث البيانات. لمزيد من المعلومات، راجع "تحديث اتصال بيانات خارجية في Excel".