دمج بيانات من عده اوراق

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

If you receive information in multiple sheets or workbooks that you want to summarize, the Consolidate command can help you pull data together onto one sheet. For example, if you have a sheet of expense figures from each of your regional offices, you might use a consolidation to roll up these figures into a corporate expense sheet. That sheet might contain sales totals and averages, current inventory levels, and highest selling products for the whole enterprise.

To decide which type of consolidation to use, look at the sheets you are combining. If the sheets have data in inconsistent positions, even if their row and column labels are not identical, consolidate by position. If the sheets use the same row and column labels for their categories, even if the data is not in consistent positions, consolidate by category.

Combine by position

For consolidation by position to work, the range of data on each source sheet must be in list format, without blank rows or blank columns in the list.

  1. Open each source sheet and make sure that your data is in the same position on each sheet.

  2. In your destination sheet, click the upper-left cell of the area where you want the consolidated data to appear.

    ملاحظة: Make sure that you leave enough cells to the right and underneath for your consolidated data.

  3. On the Data tab, in the Data Tools group, click Consolidate.

    الزر كونسوليداتيد علي علامه التبويب "بيانات"

  4. In the Function box, click the function that you want Excel to use to consolidate the data.

  5. In each source sheet, select your data.

    The file path is entered in All references.

  6. When you have added the data from each source sheet and workbook, click OK.

Combine by category

For consolidation by category to work, the range of data on each source sheet must be in list format, without blank rows or blank columns in the list. Also the categories must be consistently labeled. For example, if one column is labeled Avg. and another is labeled Average, the Consolidate command will not sum the two columns together.

  1. Open each source sheet.

  2. In your destination sheet, click the upper-left cell of the area where you want the consolidated data to appear.

    ملاحظة: Make sure that you leave enough cells to the right and underneath for your consolidated data.

  3. On the Data tab, in the Data Tools group, click Consolidate.

    الزر كونسوليداتيد علي علامه التبويب "بيانات"

  4. In the Function box, click the function that you want Excel to use to consolidate the data.

  5. To indicate where the labels are located in the source ranges, select the check boxes under Use labels in: either the Top row, the Left column, or both.

  6. In each source sheet, select your data. Make sure to include either the top row or left column information that you previously selected.

    The file path is entered in All references.

  7. When you have added the data from each source sheet and workbook, click OK.

    ملاحظة: Any labels that don't match labels in the other source areas cause separate rows or columns in the consolidation.

Combine by position

For consolidation by position to work, the range of data on each source sheet must be in list format, without blank rows or blank columns in the list.

  1. Open each source sheet and make sure that your data is in the same position on each sheet.

  2. In your destination sheet, click the upper-left cell of the area where you want the consolidated data to appear.

    ملاحظة: Make sure that you leave enough cells to the right and underneath for your consolidated data.

  3. On the Data tab, under Tools, click Consolidate.

    علامة التبويب "البيانات"، المجموعة "أدوات"

  4. In the Function box, click the function that you want Excel to use to consolidate the data.

  5. In each source sheet, select your data, and then click Add.

    The file path is entered in All references.

  6. When you have added the data from each source sheet and workbook, click OK.

Combine by category

For consolidation by category to work, the range of data on each source sheet must be in list format, without blank rows or blank columns in the list. Also the categories must be consistently labeled. For example, if one column is labeled Avg. and another is labeled Average, the Consolidate command will not sum the two columns together.

  1. Open each source sheet.

  2. In your destination sheet, click the upper-left cell of the area where you want the consolidated data to appear.

    ملاحظة: Make sure that you leave enough cells to the right and underneath for your consolidated data.

  3. On the Data tab, under Tools, click Consolidate.

    علامة التبويب "البيانات"، المجموعة "أدوات"

  4. In the Function box, click the function that you want Excel to use to consolidate the data.

  5. To indicate where the labels are located in the source ranges, select the check boxes under Use labels in: either the Top row, the Left column, or both.

  6. In each source sheet, select your data. Make sure to include either the top row or left column information that you previously selected, and then click Add.

    The file path is entered in All references.

  7. When you have added the data from each source sheet and workbook, click OK.

    ملاحظة: Any labels that don't match labels in the other source areas cause separate rows or columns in the consolidation.

تعزيز مهارات Office
استكشاف التدريب
الحصول على الميزات الجديدة أولاً
الانضمام إلى Office Insider

هل كانت المعلومات مفيدة؟

نشكرك على ملاحظاتك!

شكراً لك على الملاحظات! يبدو أنه من المفيد إيصالك بأحد وكلاء دعم Office لدينا.

×