8 طرق متسلسلة في Excel يمكنها تحسين بياناتك

كيف

بواسطةريان دوبي

تاريخ آخر تحديث13 نوفمبر 2019

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

كيف يعمل Concatenate في Excel

وظيفة Concatenate سهلة الاستخدام للغاية. يعمل بناء جملة الدالة كما يلي:

CONCATENATE (text1، text2، text3، ...)

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

في حين أنه قد يكون بسيطًا ، فإن استخداماته وفيرة.

في هذه المقالة ، سنستعرض بعض الأمثلة لبعض الطرق الأكثر فائدة التي يمكنك من خلالها استخدام وظيفة Concatenate في مشاريعك المختلفة.

1. تنسيق الأسماء

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

بناء الجملة لذلك هو:

CONCATENATE (B2، ""، A2)

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

سيؤدي ذلك إلى ملء العمود تلقائيًا ببقية الأسماء.

2. قم بتحديث الحالة بلغة بسيطة

يمكنك استخدام جدول بيانات لتجميع كمية كبيرة من البيانات لإجراء سلسلة معقدة من الحسابات كلما فتحت الورقة. ولكن كل ما يهمك حقًا هو الحساب النهائي.

وفر الوقت باستخدام Concatenate لصياغة حالة مع النتيجة النهائية في أعلى الورقة. إذا كانت النتيجة محسوبة في الخلية G47 ، فستستخدم الصيغة التالية لإنشاء حالة محدثة في الخلية A1:

= CONCATENATE ("إجمالي النفقات حتى الآن: $" ، G47)

سينتج عن ذلك البيان الموجود أعلى جدول البيانات كما هو موضح أدناه.

يمكنك الآن توفير بعض الوقت بمجرد فتح الورقة والحصول على النتيجة النهائية في لمحة.

3. تحليل عنوان بريدي

أحد الاستخدامات الأكثر شيوعًا لوظيفة Concatenate هو أخذ الحقول المختلفة في قاعدة بيانات مليئة المعلومات من المستخدمين أو العملاء أو أي شخص آخر ، وجمع عنوانهم الكامل معًا في مكان واحد حقل.

وبهذه الطريقة يمكنك الحصول على العنوان بالكامل بنقرة زر ونسخ سريعة.

قد يحتوي جدول البيانات الخاص بك على أجزاء من العنوان مقسمة بين الحقول المنتشرة عبر الجدول بأكمله.

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

ستقوم بتجميع العنوان باستخدام الوظيفة التالية:

= CONCATENATE (G2 ، CHAR (10) ، H2 ، CHAR (10) ، K2 ، "،" ، L2 ، "" ، M2)

ينتقل رمز إرجاع الحرف بعد الاسم والشارع ، ويجب تنسيق المدينة والشارع والرمز البريدي بفاصلة ومسافة.

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

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

سترى الآن أن جميع العناوين منسقة بالشكل المتوقع.

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

4. تحليل عنوان URL معًا

استخدام آخر جيد لوظيفة Concatenate هو تجميع كل العناصر لـ a سلسلة URL كاملة. على سبيل المثال ، في جدول بيانات المنتج ، قد يتكون رابط عنوان URL للمنتج الذي تم شراؤه من نطاق الموقع والقسم ومعرف المنتج.

لكل صف ، يمكنك إنشاء عنوان URL باستخدام عنونة ثابتة وديناميكية للخلايا.

= متسلسل (" https://”,D1,”/”,D3,”/”,O3,”.php”)

يؤدي ذلك إلى إرفاق النص الثابت "https:" في البداية و ".php" في نهاية سلسلة عنوان URL لهذا المنتج. ستقوم بإدراج أجزاء عنوان URL عن طريق تحديد الخلايا للنطاق والقسم ومعرف المنتج بين كل خطوط مائلة للأمام. كل هذه مفصولة بفواصل.

والنتيجة هي رابط URL منسق تمامًا لهذا المنتج.

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

5. مخططات العنوان الديناميكي

أحد الاستخدامات المفضلة لوظيفة Concatenate هو عنوان المخططات ديناميكيًا في جدول بيانات. غالبًا ما يكون هذا مطلوبًا لتحديث أشياء مثل التاريخ الحالي لجدول بيانات يتم استخدامه كتقرير متكرر.

على سبيل المثال ، في جدول بيانات المبيعات ، يمكنك إنشاء خلية عنوان تحمل العنوان للرسم البياني. باستخدام وظيفة Concatenate ، يمكنك تحديثها للتاريخ الحالي عند تحديث جدول البيانات.

= CONCATENATE ("إجمالي المبيعات اعتبارًا من:" ، عام (الآن ()))

سيتم تحديث هذه الخلية بالتاريخ الصحيح في كل مرة يتم فيها تحديث المخطط.

بعد ذلك ، عليك فقط تعيين عنوان المخطط لتحديثه مع محتويات الخلية هذه.

لفعل هذا:

1. حدد عنوان المخطط
2. حدد حقل الوظيفة واكتب "= I1" (استبدال I1 حيث قمت ببناء تسلسل عنوان الرسم البياني)
3. صحافة أدخل وسيتطابق عنوان المخطط مع الخلية المحدثة ديناميكيًا

الآن ، كلما تم تحديث جدول البيانات ، سيكون للمخطط التاريخ الصحيح في العنوان. لا داعي للقلق بشأن تذكر تحديثه عند تشغيل التقرير.

6. دمج الأرقام والنصوص

ميزة أخرى مفيدة لوظيفة Concatenate هي أنها تتيح لك دمج كل من خلايا النص وخلايا الأرقام في سلسلة واحدة. يمكنك أيضًا إحضار التواريخ إذا قمت بتحويل حقل التاريخ إلى نص.

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

= CONCATENATE (C4، "باعت وحدات"، E4 "من"، D4 "على"، TEXT (A4، "mm / dd / yyyy"))

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

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

7. إنشاء HTML تلقائيًا

إذا أخذت هذه القدرة على دمج النص والقيم والبيانات الأخرى من جدول البيانات في خلية واحدة من المحتوى ، ثم قم بدمج ذلك مع استخدام وظيفة Char (10) newline ورموز HTML ، يمكنك إنشاء كود HTML الذي يمكنك إدراجه في أي صفحة ويب.

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

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

قم بإنشاء خلية أخرى لمحتوى صفحة الويب ، ثم قم بتسلسل المحتوى الخاص بك باستخدام إدخالات الخلية من الورقة:

= CONCATENATE ("مرحبًا مجددًا بالجميع!

"، CHAR (10) ،" سنقرأ هذا الشهر "، أ 2 ،" بواسطة "، B2".

"، شار (10) ،" تم نشر هذا العنوان في "، C2 ،". "،" أتطلع إلى سماع رأيكم جميعًا! ")

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

مع القليل من الإبداع ، يمكنك إنشاء الكثير من محتوى الصفحة. وما عليك سوى نسخها ولصقها مباشرةً في مشاركات مدونتك.

8. إنشاء رموز

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

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

• أول حرفين من القسم
• آخر حرفين من اسم العميل
• أول رقمين من الرمز البريدي
• آخر 8 أرقام من معرف المنتج

يمكنك القيام بكل هذا من خلال الجمع بين وظيفة Concatenate و وظائف سلسلة اليسار واليمين:

= CONCATENATE (LEFT (D3،2)، RIGHT (H3،2)، LEFT (M3،2)، RIGHT (O3،8))

يؤدي هذا إلى دمج الدالة Left (انتزاع الأحرف من اليسار) والدالة اليمنى (أخذ الأحرف من اليمين) في دالة Concatenate.

هذا مفيد لأي شيء من إنشاء كلمات مرور فريدة إلى إنشاء سبيكة URL فريدة لموقع الويب لصفحات الويب.