كل أسرار من Excel وظيفة CDF (VLOOKUP) للبحث عن البيانات في جدول واخراجها الى آخر
نصائح تكنولوجيا / / December 19, 2019
Batyanov دينيس حقوق المؤلف ضيف يوضح في هذا المنصب حول كيفية العثور على البيانات في أحد جداول البيانات إكسل واستخراجها إلى آخر، ويكتشف كل أسرار وظيفة عرض عمودي.
عند العمل في تفوق في كثير من الأحيان هناك حاجة لإيجاد البيانات في جدول وإزالتها إلى آخر. إذا كنت لا تعرف كيفية القيام بذلك، ثم قرأت هذه المادة، فسوف لا تعلم فقط، ولكن أيضا معرفة تحت أي ظروف سوف تكون قادرة على اخراج الحد الأقصى لأداء النظام. تعتبر التقنيات الأكثر فعالية للغاية التي ينبغي أن تستخدم جنبا إلى جنب مع وظيفة CDF.
حتى إذا كان لديك سنوات من الاستخدام وظيفة CDF، مع احتمال كبير أن يكون هذا المقال مفيدا لك، وسوف لا تترك غير مبال. أنا، على سبيل المثال، كما IT-متخصص، ثم وزعيه في مجال تكنولوجيا المعلومات، وتستخدم VLOOKUP 15 عاما، ولكن إلى صفقة مع جميع الفروق الدقيقة حدث للتو، عندما أكون على أساس المهنية بدأت تعليم الناس التفوق.
CDF - اختصار ل فيعمودي، إلخالتفتيش. وبالمثل، VLOOKUP - بحث عمودي. اسم جدا من وظيفة يوحي لنا أنه يبحث في صفوف الجدول (العمودي - فرز خط وتحديد محور)، وليس في الأعمدة (أفقي - فرز الأعمدة وإصلاح خط). وتجدر الإشارة إلى أن CDF ديه شقيقة - القبيحة التي لن تصبح بجعة - هي وظيفة من الموارد الوراثية النباتية (HLOOKUP). PGR، خلافا لCDF، وتنتج بحث الأفقي، ولكن مفهوم اكسل (بل ومفهوم تنظيم) وسائل البيانات التي الجداول الخاصة بك لديها عدد قليل من الأعمدة وغيرها الكثير خطوط. هذا هو السبب في البحث عن خيوط، ونحن بحاجة عدة مرات أكثر من الأعمدة. إذا كنت
تفوق أيضا غالبا ما تستخدم HLOOKUP، فمن المحتمل جدا أن هناك شيئا أنت لا تفهم في هذه الحياة.بناء الجملة
VLOOKUP أربعة معايير:
PPS = ( . .
- القيمة المطلوبة (نادرة) أو إشارة إلى الخلية التي تحتوي على القيمة المطلوبة (الغالبية العظمى من الحالات).
(!) - في إشارة إلى مجموعة من الخلايا (صفيف ثنائي الأبعاد)، والذي هو في العمود الأول سيتم البحث قيمة المعلمة .
- هذا هو معيار مهم جدا، والذي يجيب على السؤال، كما لو تصاعدي العمود الأول من النطاق يتم فرز . إذا تم فرز مجموعة، تبين لنا حقيقي (صحيح) أو 1، وإلا - FALSE (FALSE) أو 0. إذا قمت بحذف هذه المعلمة، افتراضيا، وهو يساوي 1.
أراهن أن كثيرا من أولئك الذين يعرفون وظيفة CDF كما تقشير، وقراءة وصف المعلمة الرابع قد يشعرون بعدم الارتياح لأنها اعتدنا أن نرى ذلك بطريقة مختلفة قليلا: عادة ما يكون هناك كلمة حول بالضبط وفقا للبحث (FALSE أو 0)، أو عن نفس نطاق عرض (TRUE أو 1).
الآن لا بد من تشديد وقراءة الفقرة التالية عدة مرات حتى تحصل على شعور لمعنى ما قيل حتى النهاية. هناك أهمية لكل كلمة. وأمثلة تساعد على فهم.
كما نفس الصيغة يعمل CDF خاصة
- اكتب الصيغة I. إذا تم حذف المعلمة الأخيرة أو على النحو المحدد 1، ويتوقع أن CDF العمود الأول يتم فرز في ترتيب تصاعدي، وبالتالي فإن البحث سوف تتوقف عند الخط الذي تسبق مباشرة الصف الذي زادت قيمة من المطلوب. إذا لم يتم العثور على هذا الخط، فإنه يعود في الصف الأخير من النطاق.
- اكتب الصيغة II. إذا تم تحديد الخيار الأخير ك 0، ثم CDF بمسح بالتتابع العمود الأول من الصفيف وتوقف فورا بحث عندما وجدت لأول مرة على تطابق تام مع المعلمة ، وإلا يتم إرجاع رمز الخطأ # N / A (# N / A).
الصيغ المخطط
CDF النوع الأول
CDF نوع II
الآثار المترتبة على هذا النوع من صيغة I
- الصيغ يمكن استخدامها لتوزيع مجموعة من القيم.
- إذا كان العمود الأول يحتوي على التكرارات وفرزها بشكل صحيح، فإنه سيتم إرجاع الأخير من صفوف مع قيم مكررة.
- وإذا نظرنا لقيمة الواضح أكثر مما كنت يمكن أن تحتوي على العمود الأول، يمكنك بسهولة العثور على الصف الأخير من الجدول، والتي يمكن أن تكون ذات قيمة للغاية.
- وهذا الرأي يعود الخطأ على # N / A فقط عندما يجد قيمة أقل من أو يساوي المطلوب.
- أفهم أن صيغة بإرجاع قيمة غير صحيحة، إذا لم يتم فرز مجموعة الخاصة بك، صعبة للغاية.
الآثار المترتبة على هذا النوع من صيغة II
إذا تم العثور على قيمة في العمود الأول من الصفيف عدة مرات، فإن صيغة تحديد الصف الأول لاسترجاعها في وقت لاحق.
الإنتاجية VLOOKUP
لقد وصلت إلى ذروة مكان من هذه المادة. على ما يبدو، حسنا، ما هو الفرق إذا أشرت كمعلمة الأخير صفر أو واحد؟ أساسا، كل نقطة، وبطبيعة الحال، الصفر، كما هو عملي جدا: لا داعي للقلق حول فرز العمود الأول من الصفيف، يمكن للمرء أن يرى، والعثور على القيمة أم لا. ولكن إذا كان لديك قائمة من عدة آلاف من الصيغ CDF (VLOOKUP)، ستلاحظ أن نوع CDF أشواط II ببطء. ومن عادة كل نبدأ في التفكير:
- أنا في حاجة الى جهاز الكمبيوتر أكثر قوة.
- أنا في حاجة الى صيغة سريعة، على سبيل المثال، كثير من الناس يعرفون عن INDEX + MATCH (INDEX + MATCH)، التي يفترض أنها أسرع بائسة 5-10٪.
وقليل من الناس يعتقدون أنه بمجرد البدء في استخدام CDF النوع الأول وتقديم أي وسيلة لفرز العمود الأول لأن سرعة العملية CDF سوف تزيد 57 مرات. أنا أكتب كلمات - في الخمسين سبع مرات! في 57٪ و 5700٪. هذه الحقيقة I فحص موثوق للغاية.
سر لمثل هذه الأكاذيب عمل سريعة في حقيقة أنك يمكن أن تنطبق على مجموعة فرزها هو غاية خوارزمية بحث كفاءة، والذي يعرف باسم طريقة بحث ثنائي (التنصيف، الانقسام). لذلك النوع من CDF يستخدم ذلك، ونوع CDF II يسعى دون أي الأمثل بشكل عام. وينطبق الشيء نفسه على MATCH (MATCH)، والذي يتضمن نفس المعلمة، وأيضا إلى وظيفة مشاهد (LOOKUP)، والذي يعمل فقط على صفائف فرزها وتفعيلها في Excel من أجل التوافق مع لوتس 1-2-3.
صيغة عيوب
عيوب CDF واضحة: أولا، أنها تسعى فقط العمود الأول من هذه المجموعة، وثانيا، لمجرد أن من حق هذا العمود. وكما تعلمون، قد يكون جيدا أن العمود الذي يحتوي على المعلومات اللازمة لليسار العمود الذي نسعى إليه. تخلو من هذا القصور التي سبق ذكرها مجموعة من الصيغ INDEX + MATCH (INDEX + MATCH)، مما يجعله الحل الأكثر مرونة لاستخراج البيانات من الجداول في مقارنة مع CDF (VLOOKUP).
بعض جوانب تطبيق الصيغة في الحياة الحقيقية
بحث مجموعة
التوضيح الكلاسيكي من نطاق البحث - مشكلة تحديد حجم الخصومات النظام.
البحث عن سلاسل نصية
بطبيعة الحال، فإن CDF تسعى الأرقام فحسب، بل أيضا النص. وينبغي أن يوضع في الاعتبار أن لا يميز الصيغة لحالة الأحرف. إذا كنت تستخدم أحرف البدل، فمن الممكن لتنظيم عملية بحث غامض. هناك نوعان من البدل "؟" - استبدال أي حرف واحد في السلسلة النصية "*" - استبدال أي رقم من أي حرفا.
حارب مع المساحات
كثيرا ما تثار قضية، وكيفية حل مشكلة مسافات إضافية في البحث. إذا كان الجدول نظرة المتابعة لا يزال ممكنا لتنظيفها، المعلمة الأولى من صيغة CDF لا تعتمد دائما على لك. لذلك، إذا كان خطر انسداد خلايا مساحة إضافية موجودا، فإنه من الممكن استخدام وظيفة TRIM (TRIM) للتنظيف.
وشكل بيانات مختلفة
إذا تشير وظيفة المعلمة الأولى CDF إلى خلية التي تحتوي على العدد، ولكن التي يتم تخزينها في الخلية كنص، والعمود الأول من الصفيف يحتوي على الأرقام بالشكل الصحيح، والبحث سيكون غير ناجحة. الوضع العكسي. يتم حل المشكلة بسهولة عن طريق نقل المعلمة 1 إلى الشكل المطلوب:
PPS = (- D7. المنتجات $ A $ 2: $ C $ 5 ؛! 3; 0) - إذا يحتوي D7 النص والجدول - أرقام.
= CDF (D7 و«»)؛ المنتجات $ A $ 2: $ C $ 5 ؛! 3; 0) - والعكس بالعكس.
بالمناسبة، لترجمة النص في عدد يمكن أن يكون بعدة طرق، اختيار:
- -D7 سلبي مزدوجة.
- وحدة الضرب D7 * 1.
- بالإضافة إلى ذلك مع صفر D7 + 0.
- بناء من الدرجة الأولى D7 ^ 1.
ترجمة النص التي تنتجها اقتران مع سلسلة فارغة، والذي يسبب Excel لتحويل نوع البيانات.
كيفية منع صدور # N / A
ومن المريح جدا أن تفعل مع وظيفة IFERROR (IFERROR).
على سبيل المثال: = IFERROR (CDF (D7. المنتجات $ A $ 2: $ C $ 5 ؛! 3; 0); «»).
إذا كان CDF سيعود رمز الخطأ # N / A، بعد ذلك سوف اعتراض IFERROR بديلا والمعلمة 2 (في هذه الحالة، سلسلة فارغة)، و إذا حدث أي خطأ، وهذه الوظيفة يتظاهر أنه ليس في كل شيء، ولكن فقط في CDF، استعادة وضعها الطبيعي ينتج عن ذلك.
مجموعة
كثيرا ما يغفل يتم إجراء إشارة مجموعة المطلق، وعندما سحب مجموعة من "العوامات". تذكر أنه بدلا من A2: C5 ينبغي أن تستخدم $ A $ 2: $ C $ 5.
هي فكرة جيدة لوضع مجموعة إشارة على ورقة منفصلة في المصنف. انها لا تحصل على الأقدام، وسوف بتخزينها.
حتى أكثر جيدة فكرة إلى الإعلان عن مجموعة كما نطاق مسمى.
وتستخدم العديد من المستخدمين عند تحديد هيكل نوع مصفوفة A: C، مما يدل على الأعمدة تماما. هذا النهج لديه الحق في الوجود، منذ كنت بمنأى عن الحاجة إلى تتبع حقيقة أن مجموعة الخاصة بك تحتوي على كافة السلاسل. إذا قمت بإضافة خط على قطعة من مجموعة الأصلي، ومجموعة تحديد مثل A: C، لن تضطر إلى ضبط. وبطبيعة الحال، بناء الجملة هذا يسبب Excel لقضاء أكثر قليلا من العمل مع الإشارة الدقيق للمجموعة، ولكن أحمال البيانات لا يكاد يذكر. نحن نتحدث عن المئات من الثانية.
حسنا، على حافة العبقرية - إلى المسألة في شكل مجموعة الجدول الذكية.
باستخدام وظيفة العمود لتحديد العمود استخراج
إذا كان الجدول الذي قمت استخراج البيانات باستخدام CDF، لديه نفس بنية الجدول نظرة، ولكن فقط تضم أقل عدد ممكن من الصفوف في المجال WRT يمكن استخدام عمود () وظيفة لحساب أعداد استرداد تلقائيا الأعمدة. في هذه الحالة فقط سوف CDF-صيغة تكون هي نفسها (تعديل للمعلمة الأولى، والذي يتغير تلقائيا)! لاحظ أن المعلمة الأولى هو تنسيق المطلق العمود.
إنشاء مفتاح مركب من قبل و»|» و
إذا كان هناك حاجة للنظر في أكثر من عمود واحد في وقت واحد، فمن الضروري لجعل مفتاح مركب للبحث. إذا كانت القيمة التي تم إرجاعها ليست النص (كما هنا في حالة من حقل "كود")، ورقمية، ل جاء ذلك على SUMIFS مريحة صيغة (SUMIFS) وعمود أساسي لا يتجزأ لن تكون هناك حاجة على الإطلاق.
وهذا هو أول مقالة لي عن Layfhakera. إذا كنت تحب ذلك، ثم أدعوكم لزيارة موقعيوبكل سرور أقرأ تعليقاتكم عن أسرار باستخدام VLOOKUP وما شابه ذلك. شكرا لك. :)