كيفية ربط الرسوم البيانية في إكسل. مصنف اكسل

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

يتم إدراج كافة الجداول الموجودة في المصنف في قائمتي حقول PivotTable وPower View.

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

إذا كان الفريق علاقةلا يمكن الوصول إليه، مما يعني أن المصنف يحتوي على جدول واحد فقط.

فى الشباك إدارة العلاقاتانقر فوق الزر يخلق.

فى الشباك إنشاء اتصالانقر فوق السهم الموجود بجوار الحقل طاولةوحدد الجدول من القائمة المنسدلة. في علاقة رأس بأطراف، يجب أن يكون هذا الجدول جزءًا يحتوي على عناصر متعددة. في مثال منطق العملاء والوقت، يجب عليك أولاً تحديد جدول مبيعات العملاء لأنه من المحتمل أن يكون هناك العديد من المبيعات التي تحدث كل يوم.

للعنصر عمود (غريب)حدد العمود الذي يحتوي على البيانات المتعلقة بالعنصر. على سبيل المثال، إذا كان لديك عمود تاريخ في كلا الجدولين، فستحتاج إلى تحديد هذا العمود الآن.

في الميدان الجدول ذو الصلةحدد جدولاً يحتوي على عمود واحد على الأقل من البيانات المرتبطة بالجدول المحدد في الحقل طاولة.

في الميدان العمود المرتبط ( المفتاح الأساسي) حدد العمود الذي يحتوي على قيم فريدةوالتي تتوافق مع القيم الموجودة في العمود المحدد في الحقل عمود.

انقر فوق الزر نعم.

تعرف على المزيد حول العلاقات بين الجداول في Excel

ملاحظات حول الاتصالات

مثال. ربط بيانات المنطق الزمني مع بيانات رحلات الطيران

يمكنك التعرف على كيفية ارتباط كل من الجداول والمنطق بالوقت باستخدام البيانات المجانية المتوفرة في Microsoft Azure Marketplace. بعض مجموعات البيانات هذه كبيرة جدًا، ويتطلب تنزيلها فترة زمنية معقولة اتصال سريعإلى شبكة الإنترنت.

يمكنك الآن فصل تأخيرات الوصول حسب السنة والشهر وقيم التقويم الأخرى.

نصيحة:بشكل افتراضي، يتم إدراج الأشهر بالترتيب الأبجدي. باستخدام الوظيفة الإضافية Power Pivot، يمكنك تغيير ترتيب الفرز بحيث تظهر بترتيب زمني.

طاولة BasicCalendarUSيجب أن يكون مفتوحًا في نافذة Power Pivot.

في الجدول الرئيسي، انقر فوق الترتيب حسب العمود.

في الحقل "فرز"، حدد شهر في التقويم.

في الحقل حسب، حدد شهرالسنة.

يقوم الجدول المحوري الآن بفرز مجموعة كل شهر وسنة (أكتوبر 2011، نوفمبر 2011) حسب رقم الشهر في السنة (10، 11). يعد تغيير ترتيب الفرز أمرًا سهلاً لأن القناة DateStreamيوفر كافة الأعمدة اللازمة لهذا البرنامج النصي للعمل. إذا كنت تستخدم جدولًا منطقيًا زمنيًا مختلفًا، فستكون أفعالك مختلفة.

"قد تكون العلاقات بين الجداول مطلوبة"

كما تتم إضافة الحقول إلى جدول محوريسيتم إعلامك بأن العلاقة بين الجداول مطلوبة لفهم الحقول المحددة في PivotTable.

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

الخطوة 1: تحديد الجداول التي سيتم الارتباط بها

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

ملحوظة:يمكنك إنشاء علاقات غامضة غير صالحة عند استخدامها في تقرير PivotTable أو Power View. قد تكون كافة الجداول الخاصة بك مرتبطة بطريقة ما بجداول أخرى في النموذج، ولكن عند محاولة ضم حقول من جداول مختلفة، ستتلقى الرسالة "قد تكون علاقات الجداول مطلوبة". معظم سبب محتملهو أنك تواجه علاقة متعدد إلى متعدد. إذا اتبعت سلسلة علاقات الجدول التي تتصل بالجداول التي تحتاجها، فمن المحتمل أن تجد أن هناك علاقتين أو أكثر من علاقات رأس بأطراف بين الجداول. لا يوجد حل بديل سهل يعمل في كل موقف، ولكن يمكنك محاولة إنشاء أعمدة محسوبة لدمج الأعمدة التي تريد استخدامها في جدول واحد.

الخطوة 2: ابحث عن الأعمدة التي يمكن استخدامها لإنشاء مسار من جدول إلى آخر

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

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

بالإضافة إلى مطابقة القيم، هناك العديد من المتطلبات الإضافية لإنشاء العلاقات.

إذا كنا بحاجة إلى تم نقل البيانات من جدول Excel تلقائيًا إلى جدول آخر، تحتاج إلى ربط هذه الجداول. هناك عدة طرق لربط البيانات من جدولين في Excel. طريقة واحدة - باستخدام صيغة، راجع المقالة "كيفية ربط الجداول في Excel".
وهنا سننظر، كيفية ربط جدولين في مصنفات Excel مختلفةمن خلال إنشاء علاقة بين الجداول. بمجرد إعداد كل شيء، يكفي فتح جدول واحد بحيث يتم نقل البيانات إلى هذا الجدول من جدول آخر. لا تحتاج إلى فتح جميع الكتب حتى يعمل الاتصال. سيعمل مع مصنف واحد مفتوح.
لذلك، لدينا مصنفان في Excel - Book1 وBook2. يوجد في الكتاب الأول جدول مملوء بالفعل. الجدول 1.انتباه!
إذا كان الجدول يحتوي على خلايا مدمجة، فلن يعمل الاتصال فيها أو سيتم فقده.
في الكتاب الثاني، قمنا بعمل نفس الجدول، لكن لم نملأه.

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

إذا كانت إحدى أوراق العمل تستخدم بيانات من ورقة عمل أخرى، فسيتم اعتبار الأوراق مرتبطة. باستخدام الارتباط، يمكنك دمج قيم الخلايا من عدة جداول مختلفة في ورقة عمل واحدة.

تغيير محتويات خلية في نفس ورقة العمل أو الجدول ( مصدر) للمصنف يؤدي إلى تغيير الخلايا المقترنة به في الأوراق أو الجداول ( أجهزة الاستقبال). يميز هذا المبدأ ربط الأوراق عن مجرد نسخ محتويات الخلايا من ورقة إلى أخرى.

اعتمادا على تقنية التنفيذ، يمكن أن يكون الربط "مباشر"ومن خلال الفريق إدراج خاص.

الطريقة الأولى - "الربط المباشر للخلايا"

الربط المباشريتم استخدام الأوراق مباشرة عند إدخال صيغة في خلية، عند استخدام مرجع إلى خلية في ورقة أخرى كأحد عناصر الصيغة. على سبيل المثال، إذا كانت خلية الجدول B4 في ورقة العمل2 تحتوي على صيغة تستخدم رابطًا للخلية A4 في ورقة عمل أخرى (على سبيل المثال، الورقة1) وتم تحميل كلا الورقتين بالبيانات، فإن ربط الأوراق هذا يسمى "مباشر".

شرط ملزمة "مباشرة".يعني أن المستخدم نفسهمباشرة عند إدخال الصيغة يشيراسم الورقةو عنوان الخلية المطلق، مفصولة بعلامة تعجب "!".

أمثلة على الصيغ: = C5*Sheet1! A4

الورقة 3! ب2*100%

الورقة 1! A1- ورقة 2! أ1

ملحوظة.

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

الطريقة الثانية - ربط الخلايا باستخدام أمر "لصق خاص".

الارتباط عبر الأمر إدراج خاصيتم تنفيذه إذا كانت أي خلية جدول في ورقة عمل واحدة تحتوي على قيمة خلية من ورقة عمل أخرى.

للتفكير في الخلية C4 في ورقة العمل سعرقيمة الخلية H4 على الورقة الأصلية شراء، تحتاج إلى وضع المؤشر على الخلية H4 من الورقة المصدر وتنفيذ الأمر تحرير-نسخ. على ورقة سعرضع المؤشر على الخلية C4، التي يجب ربطها بالخلية الأصلية، وقم بتنفيذ الأمر تحرير – لصق خاص – إدراج رابط(انظر الشكل 8). ثم على الورقة سعرستظهر إشارة إلى خلية الورقة المصدر شراء، على سبيل المثال: = شراء!$H$4

باستخدام هذا الارتباط، يستخدم EXCEL تلقائيًا العنوان المطلق لكل خلية، لأنه يمكن أن يؤدي عنوان الوصول النسبي إلى حدوث أخطاء، خاصة في حالة الوصول إلى الملفات غير المحملة (المصنفات).

يمارس.ربط الخلايا C4، C5، C6، C7، C8 في الجدول تكاليف المشترياتعلى ورقة سعرمع الخلايا المقابلة على الورقة شراءباستخدام طرق مختلفة لربط الخلايا (الشكل 8).

أرز. 8 ربط الخلايا من أوراق عمل مختلفة

! عند ربط الخلايا، حدد الخلايا التي هي الخلايا الأصلية.

! بالنسبة لجدول مرتبط واحد، يمكن أن تكون الخلايا المصدر من جداول مختلفة في أوراق عمل مختلفة أو في ورقة العمل الحالية.

مهام للعمل المستقل.

قبل إكمال مهمة مستقلة، دعونا نلقي نظرة على مثال الجداول الورقية سعرربط الخلايا.

على سبيل المثال:

على ورقة سعرفي الطاولة تكاليف المشترياتالخلايا A4:A8 مرتبطة بخلايا الجدول كمية المنتجات المشتراةعلى ورقة شراء;

الخلايا B4:B8 هي الخلايا الأصلية، لأن تحتوي على معلومات أولية عن أسعار البضائع المشتراة؛

ترتبط الخلايا C4:C8 بالخلايا H4:H8 في ورقة العمل شراء;

تحتوي الخلايا D4:D8 على صيغ لحساب الأموال التي تم إنفاقها على البضائع المشتراة وتشير إلى خلايا الجدول الخاص بها (على سبيل المثال، تبدو الصيغة في الخلية D4 مثل =B4*C4، مما يعني ضرب سعر المنتج بكميته );

الخلية D9 هي مجموع الخلايا D4:D8؛

في الجدول الثاني حساب السعرعلى نفس الورقة، يتم توصيل الخلايا A14:A18 بشكل مشابه للخطوة 1؛

ترتبط الخلايا B14:B18 بالخلايا المصدر للورقة الحالية B4:B8؛

الخلايا C4:C8 هي الخلايا الأصلية، لأن تحتوي على معلومات أولية حول علامة الصالون على البضائع المشتراة؛

تحتوي الخلايا D14:D18 على صيغ لحساب سعر بيع البضائع وتشير إلى خلايا الجدول الخاص بها (على سبيل المثال، تبدو الصيغة في الخلية D14 مثل =B14*C14+B14، مما يعني ضرب سعر الشراء في العلامات المحددة النسبة المئوية، التي تعطي مقدار هامش الربح الذي يجب إضافته إلى سعر الشراء)؛

بعد إجراء جميع العمليات على هذه الجداول، تحقق من "قابليتها للتشغيل".

يتغيراسم المنتج - كنبةفي الخلية A4 على الورقة شراءإلى شيء آخر - على سبيل المثال كنبة.

يتغيركمية البضائع المشتراة كنبةفي يونيو (في الخلية G4 على الورقة شراءأدخل الرقم 11).

يتغيرسعر شراء الأريكة في الخلية B4 على الورقة سعرلآخر – 2500.00 فرك.

يتغيرالنسبة المئوية لترميز الأريكة في الخلية C14 على الورقة سعرمن 50% إلى 32%.

يفحص هل حدثت تغييرات في الجداول ذات الصلة أم لا؟

ملحوظة، ما هي خلايا الجداول المختلفة التي تأثرت بالتغييرات التي تم إجراؤها.

انتباه! عند ربط الخلايا عبر PASTE SPECIAL... يصبح النسخ إلى الخلايا المجاورة مشكلة بسبب العنونة المطلقة للخلايا.

التمرين 1.ربط خلايا الجداول المتبقية في المصنف باستخدام طرق مختلفة.

ملحوظة.تستخدم جداول حساب الإيرادات والدخل للربع الأول صيغة لجمع الخلايا المصدر للربع الأول فقط.

المهمة 2.إنشاء على الأوراق ربحو دخلجداول الحسابات للربع الثاني. ربط هذه الجداول بالبيانات المصدر المقابلة.

ملحوظة.

المهمة 3.رسم مخطط دائري على ورقة عمل دخلوتحليل توزيع الدخل حسب نوع المنتج.

المهمة 4.أضف ورقة عمل إلى نهاية المصنف البيان الموحد.قم بإنشاء جدول ملخص عليه، يعكس حسب اسم المنتج كمية الشراء والبيع، والعلامات التجارية، وأسعار الشراء والبيع، وإيرادات المبيعات للربع الأول والربع الثاني. اربط هذا الجدول بالبيانات المصدر المقابلة في أوراق العمل الأخرى.

ملحوظة.في جداول حساب الإيرادات والدخل للربع الثاني، استخدم الخلايا المصدر للربع الثاني فقط.

المعلومات في جدول البيانات مايكروسوفت اكسليمكن دمجها مع البيانات المستخرجة من الرسم.

عن طريق إنشاء اتصال مع الإلكترونية جدول اكسليمكنك تضمين معلومات من الورقة بأكملها أو جزء منها فقط، على سبيل المثال. النطاق المسمى. في مربع الحوار ارتباط البيانات الخارجية، يمكنك القيام بما يلي:

• قم بتعيين ارتباط بيانات عن طريق تحديد ملف XLS الذي تريد ربط البيانات المستخرجة من الرسم به.
• قم بتعيين التعيين بين بيانات الرسم المستخرجة وبيانات جدول البيانات.
• حدد الأعمدة الموجودة في ملف XLS التي تريد دمجها مع بيانات الرسم المستخرجة.

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

لإنشاء رابط إلى جدول بيانات، يجب أن يكون لدى الكمبيوتر الذي يقوم باستخراج البيانات تطبيق مايكروسوفتاكسل. إذا لم يتم تثبيته برنامج مايكروسوفتاكسل أو ملف XLSلم يتم حفظها محليًا على الكمبيوتر الذي يتم استرداد البيانات عليه، فلا يمكن ربط البيانات به.

بعد تغيير جدول بيانات مرتبط، مثل إضافة صف أو عمود، يمكنك تحديث الجدول في الرسم ليعكس هذا التغيير باستخدام أمر LINK UPDATE. وبالمثل، إذا قمت بتغيير جدول في رسم، فيمكن تحديث جدول البيانات المرتبط باستخدام نفس الأمر.

ملاحظة: لإزالة ارتباط بيانات، استخدم الأمر LINK. روابط للبيانات من اللوحة روابط خارجية"لا يمكن حذفها.

حول ربط البيانات ومطابقتها

يتضمن ربط البيانات تحديد جدول بيانات Microsoft Excel لدمج الجدول بأكمله أو جزء منه مع بيانات الرسم المستخرجة. لمزيد من المعلومات حول ربط البيانات، راجع إنشاء رابط جدول للبيانات الخارجية.

تقوم عملية تعيين البيانات بإنشاء علاقة بين مجموعتين من البيانات: البيانات الموجودة في عمود جدول بيانات الرسم المستخرج والبيانات الموجودة في عمود جدول بيانات Excel.

يفحص المثال التالي مواصفات بسيطة. يتم استرداد ثلاثة أعمدة من البيانات من الرسم. تحتوي هذه الأعمدة على البيانات التالية: الجزء رقم., افتتاحيةو كمية. يمكنك تضمين عمودين إضافيين من البيانات من جدول بيانات في المواصفات الخاصة بك. تحتوي هذه الأعمدة على البيانات التالية سعرو مزود.

في في هذا المثاليحتوي جدول البيانات على عمود من أرقام الأجزاء التي تحتوي على رأس رقم الكتالوجويتوافق مع العمود رقم التفاصيلفي جدول البيانات المستخرجة. في مربع الحوار ارتباط البيانات الخارجية، حدد "رقم التفاصيل"كعمود لرسم البيانات و "رقم الكتالوج"كعمود للبيانات الخارجية. نظرًا لوجود خلية بيانات مقابلة واحدة على الأقل في كل من عمود بيانات الرسم وعمود البيانات الخارجية، فإن وظيفة استخراج البيانات تعرف كيفية ارتباط المصدرين.

يمكنك التحقق من تناسق البيانات عن طريق النقر فوق الزر التحقق من الامتثال في مربع الحوار ارتباط البيانات الخارجية. إذا كانت المطابقة غير صحيحة، يتم عرض رسالة تحذيرية تشير إلى السبب. في بعض الحالات الخلق الصحيحلا يوجد اتصال بين بيانات الرسم وبيانات جداول البيانات.

التحقق من صحة البيانات المطابقة

يعني فشل مطابقة البيانات أنه لا يمكن العثور على تطابق صالح بين بيانات الرسم والبيانات الخارجية (في جدول البيانات). يتم عرض رسائل الخطأ التالية.

لم يتم العثور على تطابقات بين القيم الموجودة في بيانات الرسم والبيانات الخارجية.

• إذا تعذر حل المشكلة، فلن يتم دمج البيانات الخارجية. قارن بيانات عمود جدول البيانات ببيانات الرسم وحاول العثور على القيم المقابلة. على سبيل المثال، يمكن ضبط بيانات الرسم والبيانات الخارجية على معان مختلفةدقة.

لا يحتوي عمود البيانات في البيانات الخارجية على قيم فريدة.

• يحتوي عمود البيانات الخارجية على خلية واحدة أو أكثر بنفس البيانات. في هذه الحالة، من المستحيل تحديد المراسلات بشكل لا لبس فيه بين بيانات الرسم والبيانات الخارجية.

أولاً والأهم: ربط الجداول بناءً على الخصائص المشتركة ليس ميزة نموذجية في برنامج MS Excel. لمثل هذه المهام هناك برامج خاصة- ما يسمى بقواعد البيانات. على الرغم من أنه من الأصح أن نطلق عليها "أنظمة إدارة قواعد البيانات" أو DBMS. وتشمل هذه، على سبيل المثال، MS Access، MS خادم قاعدة البياناتوأوراكل وغيرها الكثير. في هذه الأنظمة، قد يكون ربط الجداول بناءً على الحقول الرئيسية المشتركة هو العملية الأكثر شيوعًا. ولحلها هناك قوية و أدوات مريحة. إنها تسمح لك بتنظيم مجموعة واسعة من أنواع العلاقات بين الجداول، ومراقبة تكامل البيانات، وإجراء الحذف المتتالي للسجلات، وما إلى ذلك. ومع ذلك، لاستخدام نظام إدارة قواعد البيانات (DBMS) بشكل كامل، فإنك تحتاج إلى تدريب معين، ومعرفة بلغة الاستعلام، وما إلى ذلك. لذلك، في ممارسة المحاسبين، نادرًا ما يتم استخدام أنظمة إدارة قواعد البيانات في شكلها النقي، وعادةً ما تتم جميع عمليات معالجة البيانات في برنامج اكسل. أما بالنسبة لبرنامج MS Excel، فإن إمكانيات ربط الجداول محدودة. يتضمن هذا الاتصال بشكل أساسي كتابة الصيغ التي تشير إلى أوراق أو مصنفات مختلفة. يمكنك إنشاء تقرير مطبوع أو جدول محوري يعمل على قواعد بيانات متعددة. ولكن لا توجد أدوات خاصة لتنظيم الاتصالات بين مجموعات البيانات بشكل كامل في برنامج Excel، ولا ينبغي أن تكون موجودة.

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

كمثال، سأستخدم جدولين، الشكل الموضح في الشكل. 1 و 2. الجدول الأول (موجود على الورقة "طلبات ") يتكون من أربعة حقول: "الطلب"، "التاريخ"، "العميل"، "المبلغ". "والحقل لا يزال فارغا"تعليق. " هذه نسخة مبسطة من قاعدة بيانات الطلب. في الميدان "تعليق. "سنضع في النهاية رابطًا لعنصر الدليل. ومن الواضح أنه في قاعدة البيانات "طلبات » يمكن أن يظهر أحد الأطراف المقابلة عدة مرات - وهذا يعتمد على عدد الطلبات المقدمة من قبل هذا العميل.

الجدول الثاني اسمه "العملاء " ولم أترك فيه إلا خمسة أعمدة: "اسم "(اسم المؤسسة)، متبوعًا باسم المدير والعنوان القانوني ورقم الهاتف والبريد الإلكتروني.

مهم!في دليل "العملاء" » يحتوي سطر واحد على بيانات حول مؤسسة واحدة. التكرار غير مسموح به هنا؛ كل عميل موجود في قاعدة البيانات مرة واحدة فقط.

لذلك، سنقوم بإنشاء علاقة بين الجدول "الطلبات" و"العملاء " للقيام بذلك، في الميدان "تعليق. » الجداول « الطلبات "سنضع رابطًا تشعبيًا يشير إلى صف معين في الجدول"العملاء " ستكون خوارزمية الارتباط التشعبي كما يلي. أولا يجب تحليل محتويات الحقل " NaimPredpr " الخامس الخط الحالي الجداول "طلبات " وبعبارة أخرى، تذكر اسم الشركة، والتي وضعت أمرا محددا. يجب أن ينتقل الرابط بعد ذلك إلى ورقة العمل "العملاء " في هذه الورقة، ابحث عن السطر الذي يصف معلمات الشركة التي قدمت الطلب. وأخيرًا - اضبط المؤشر الخلية الحاليةإلى السطر المقابل في الدليل "العملاء " هكذا تبدو بشكل عام.

بالمناسبة، في مصطلحات قاعدة البيانات تسمى هذه العلاقة "واحد لكثير "(إذا تم عرضها من جانب الطاولة"العملاء "). أي أن أحد عناصر دليل العميل يشير إلى عدة سجلات في قاعدة بيانات الطلب. لا توجد أدوات خاصة في MS Excel لإنشاء مثل هذا الاتصال. وعلينا أن نبنيها بأنفسنا. للقيام بذلك، بناءً على الخوارزمية، سنحتاج إلى: وظيفة مدمجة لإنشاء ارتباط تشعبي (تسمى هذه الوظيفة "الارتباط التشعبي() ") ووظيفة للبحث عن البيانات في جدول. أقترح استخدام "مباراة() " ولكن هذا ليس كل شيء. يجب تحديد الارتباط التشعبي العنوان الدقيقللانتقال: اسم الملف واسم ورقة العمل والخلية التي يجب تنشيطها. للحصول على اسم الملف والورقة والخلية، سنستخدم الدالة "خلية() " بالإضافة إلى ذلك، لإنشاء سلسلة بعنوان انتقالي، سيتعين علينا اللجوء إلى الوظائف المضمنة للعمل مع النص: " PSTR ()" و"البحث ()" " هذا كل الأدوات الرئيسية. يمكنك الانتقال إلى التنفيذ العملي.

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

1. اتصل بـ Excel، وقم بتحميل المستند وانتقل إلى القائمة "الصيغ " سيبدو الشريط كما في الشكل 3.

2. في المجموعة " أسماء محددة"انقر على الأيقونة"مدير الاسم " سيتم فتح النافذة الموضحة في الشكل. 4.

3. في هذه النافذة، انقر فوق الزر “يخلق " النافذة "إنشاء اسم "كما هو موضح في الشكل. 5.

4. في هذه النافذة في الحقل "الاسم: "أدخل النص "My_List". في حقل المدى: » طباعة الصيغة «=PSTR(CELL("اسم الملف" ;العملاء!$A$1);بحث(" [ " ;CELL(" filename " ;Clients!$A$1));256)& " ! " " (الشكل 3).

5. في نافذة "إنشاء اسم"، انقر فوق "موافق".

6. في نافذة "مدير الأسماء"، انقر فوق "موافق".

الآن دعونا معرفة ما فعلناه فعلا؟ لقد ظهر نطاق مسمى جديد في المصنف "قائمتي ".

مهم! في حالتنا، يجب أن يكون اسم النطاق المسمى بدون مسافات. وإلا، فإن معالجة الصيغ لن تعمل بشكل صحيح.

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

التعبير "CELL("اسم الملف";العملاء! $A$1) "الوصول إلى الخلية" A1" في الورقة "العملاء "ويعود لهذه الخلية العنوان الكامل ، أي المسار إلى الملف واسم الملف واسم الورقة التي توجد بها هذه الخلية. على سبيل المثال، المستند الذي يحتوي على جداول يسمى " DeanLinksExcel_.xls " يتم تخزينه على القرص " D: "في المجلد" !عامل " ثم ستكون نتيجة الصيغة هكذا: "D:!عامل[DinLinksExcel_.xls]العملاء».

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

يمكنك قص جزء من الخط باستخدام "بستر () " لكن عليها أن تشير إلى: النص الأصلي(بالنسبة لنا هذا هو المسار الكامل للخلية)، موقف البدايةو عدد العلامات، والتي يجب قطعها من السلسلة الأصلية.

من السهل تحديد موضع البداية. للقيام بذلك، استخدم الدالة "يبحث() "نجد أول ظهور لقوس الفتح المربع (""[ ") في النص حيث يتم تخزين المسار إلى الخلية. يبدو جزء الصيغة الذي ينفذ هذه العملية كما يلي: " SEARCH(" [ " ;CELL(" filename " ; Clients!$A$1)) ". للخط " D:!عامل [DinLinksExcel_.xls]العملاء"هذه الصيغة سوف تعود" 12 " لقد اخترت الحد الأقصى لعدد الأحرف التي يجب قطعها من النص المصدر - " 256".

يبقى استبدال القيم التي تم الحصول عليها في الدالة "بستر () " المعلمة الأولى الخاصة بها هي النص الذي يحتوي على عنوان الخلية. المعلمة الثانية هي رقم موضع البداية لقطع جزء من الخط. المعلمة الأخيرة هي الحد الأقصى لعدد الأحرف المسموح به في كل سطر. صيغة هذه العملية ستكون: "=PSTR(CELL("اسم الملف" ;العملاء!$A$1);بحث

(" [ " ;CELL(" اسم الملف " ;العملاء!$A$1));256) " في مثالنا، ستعيد هذه الصيغة النتيجة بالصيغة "[DinLinksExcel_.xls]العملاء" في الأساس، هذا رابط للورقة "العملاء " دفتر العمل " DeanLinksExcel_.xls " التالي قد يكون عنوان الخلية. ولكن حتى الآن هناك عنصر واحد مفقود - بين اسم الورقة وعنوان الخلية يجب أن يكون هناك علامة تعجب (« ! "). يمكننا إرفاق هذا الرمز بالصيغة باستخدام العملية "& "، وفي النسخة النهائية سيبدو التعبير كما يلي: "=PSTR(CELL("اسم الملف" ;العملاء!$A$1);بحث(" [ " ؛ الخلية (" اسم الملف ") ; العملاء!$A$1)); 256)&" ! " " وستكون نتيجة عملها هكذا: "».

نصيحة انسخ أجزاء الصيغة بعناية إلى خلايا منفصلة في ورقة العمل، وبعد ذلك سيكون عمل كل جزء منها في راحة يدك.

بالمناسبة، من السهل التحقق من كيفية عمل الصيغة داخل نطاق مسمى. أدخل التعبير "=My_Sheet " واضغط على المفتاح "يدخل " يجب أن تظهر النتيجة في الخلية: "[DinLinksExcel_.xls]العملاء!».

مهم! يرجى ملاحظة أن ل التشغيل السليميجب ألا تحتوي أسماء أوراق الصيغة على مسافات.

1. اذهب إلى الورقة "طلبات "، قف على الخلية" E2".

2. أدخل الصيغة "=HYPERLINK(My_Sheet&ADDRESS(MATCH(C2,Customers!$A:$A,0); 1);" > " ) ».

3. انسخ هذه الصيغة إلى ارتفاع الجدول بالكامل. وتظهر نتيجة عملنا في الشكل. 6.

دعونا نتحقق مما حصلنا عليه. في الطاولة "طلبات "النقر بزر الماوس الأيسر، على سبيل المثال، على خلية"ه6 " يحتوي هذا السطر على معلومات حول الطلب بالرقم " 5" من شركة "بي إي" كولو. بعد النقر سيقوم Excel بالتبديل إلى ورقة العمل "العملاء "، وسيكون مؤشر الخلية النشط على العنوان "أ6 " في هذا الموضع من الدليل توجد معلومات حول "مؤسسة خاصة "كولو" (الشكل 7).

دعونا نفكر بإيجاز في خوارزمية الصيغة. تعبير "=MATCH(C2,العملاء! $A:$A,0)"يعثر على خلية في العمود"أ" في ورقة "العملاء" "، والذي فيه اسم الطرف المقابل من الخلية " C2" ورقة "الأوامر" " المعلمة الأخيرة للوظيفة "المباراة ()" هي "0" " هذا يعني أنه سيبحث عن القيمة وفقًا للمبدأ دقيقالصدف. دعنا نعود إلى مثالنا. لنفترض أننا نعمل مع السلسلة " 6 » الجداول « الطلبات "(الشكل 6). يحتوي هذا السطر على رقم الطلب " 5" من شركة "بي إي" كولو " بعد نسخ الارتباط التشعبي من الخلية "ه2 "أسفل العمود" E" في الخلية "E6". "ستبدو الصيغة مع وظيفة البحث كما يلي:"MATCH(C6;العملاء!$A:$A;0)" ونتيجة هذا التعبير ستكون مساوية " 6 " وهذا يعني أنه في الجدول "العملاء " وصف الشركة "بي "كولو" " يقع في السادسسطر ورقة العمل.

تفضل. سنقوم باستبدال نتيجة هذا التعبير كمعلمة أولى للوظيفة، العنوان. ستشير المعلمة الثانية إلى " 1 " ثم التعبير "العنوان (مباراة (C2;العملاء!$A:$A;0);1)"(الصف الثاني من الجدول"طلبات ") تعني في الأساس "العنوان(2;1) " ستعيد هذه الوظيفة كنص عنوان الخلية الموجودة في الصف الثاني والعمود الأول من ورقة العمل. للطلب " 1 "(السطر الثاني) سيكون هذا هو العنوان"$A$2 " للطلب في السطر السادس من الجدول (الشركة "مؤسسة خاصة "كولو" ") سيعيد التعبير السلسلة "$A$6"، إلخ.

عنوان الخلية واضح. أنت الآن بحاجة إلى استخدام النتيجة التي تم الحصول عليها في الوظيفة "الارتباط التشعبي ()."

هذا هو الوضع هنا. دعني أذكرك أن الوظيفة "الارتباط التشعبي() "اثنين من المعلمات. الأول هو العنوان الذي يشير إليه الارتباط التشعبي. يتضمن اسم الملف والورقة وعنوان الخلية المراد الانتقال إليها. لدينا بالفعل أسماء الملف والورقة، ويتم تخزين هذه المعلومات في المتغير "قائمتي " لقد تلقينا عنوان الخلية للإشارة إلى الطرف المقابل لأمر معين. ويبقى الجمع بين هذين الجزأين مع العملية "& " وبعد ذلك سيكون التعبير النهائي لعنوان النقل كما يلي: "My_List&ADDRESS(MATCH(C2; Clients!$A:$A;0);1)».

دعونا نرى ما حصلنا عليه. للخلية "ج2 "مثل هذه الصيغة ستعيد النتيجة"[DinLinksExcel_.xls]العملاء!$A$2" - أي رابط للخلية "أ2 "في الدليل"العملاء " هذا صحيح: لرقم الطلب " 1 » نقاط الارتباط بالطرف المقابل «توف "سميث" " إذا كنت تشير إلى الخلية "ج6 "(رقم الأمر " 5" من "PE" كولو ")، ثم في صف الجدول هذا "طلبات "سيبدو التعبير الخاص بعنوان النقل كما يلي: "My_List&ADDRESS(MATCH(C6,Clients!$A:$A,0),1)" وستكون نتيجة الصيغة هكذا: "[DinLinksExcel_.xls]العملاء!$A$6».

دعنا ننتقل إلى المعلمة الثانية للوظيفة "الارتباط التشعبي() " يجب أن يكون هناك نص هنا ذلكسيعرض Excel الارتباطات التشعبية في مكانها. بمعنى آخر، هذا هو اسم الارتباط التشعبي نفسه. لقد اخترت الرمز "> علامة (""المزيد"). "). وبعد ذلك ستكون الصيغة النهائية لإنشاء ارتباط تشعبي هي: "=HYPERLINK(My_List&ADDRESS(MATCH(C2, Clients!$A:$A, 0),1);" > " ) ».

هذا كل ما يتعلق بإنشاء العلاقات بين الجداولاكسل. لقد أكملنا المهمة، جميع الروابط تعمل بشكل صحيح. الشيء الوحيد الذي أود إضافته هو بضع كلمات حول تصميم الطاولة. الآن في العمود "