ماهیت درخواست انتخاب این است که یک رشته را از جدول منبع انتخاب کنید که معیارهای خاصی را برآورده می کند (شبیه به استفاده از استاندارد). ما مقادیر را از جدول منبع با استفاده از آن انتخاب خواهیم کرد. در مقایسه با استفاده از ( Ctrl + Shift + L یا داده ها / مرتب سازی و فیلتر / فیلتر) خطوط انتخاب شده در یک جدول جداگانه قرار می گیرند.

در این مقاله، درخواست های رایج را در نظر بگیرید، به عنوان مثال: انتخاب ردیف های جدول، که در آن مقدار از ستون عددی به محدوده مشخص شده (فاصله) می رسد؛ انتخاب خطوط، که در آن تاریخ به یک دوره مشخص وارد می شود؛ وظایف با 2 معیار متن و دیگران. بیایید با پرسش های ساده شروع کنیم.

1. یک معیار عددی (کالایی را انتخاب کنید که قیمت بالاتر از حداقل)

مثال فایل، ورق یک معیار - شماره ).

شما باید در یک جدول جداگانه فقط این سوابق (خطوط) را از جدول منبع نمایش دهید، که در آن قیمت بالاتر از 25 سال است.

حل این و وظایف بعدی را می توان به راحتی با. برای انجام این کار، سرفصل های جدول منبع را انتخاب کنید و روی کلیک کنید Ctrl + Shift + L. از طریق فهرست کشویی عنوان، را انتخاب کنید فیلترهای عددی ...، سپس شرایط فیلتر لازم را تنظیم کنید و روی OK کلیک کنید.

سوابق نمایش داده خواهد شد رضایت شرایط انتخاب.

رویکرد دیگر استفاده است. بر خلاف خطوط انتخاب شده در یک جدول جداگانه قرار می گیرد - یک عجیب و غریب، که به عنوان مثال، می تواند در سبک غیر از جدول منبع فرمت شود یا تغییرات دیگر آن را تولید کند.

معیار (حداقل قیمت) در سلول قرار می گیرد e6 ، جدول برای داده های فیلتر شده - در محدوده D10: E19 .

حالا محدوده را انتخاب کنید D11: D19. (محصول ستون) و در مقدمه:

فهرست (A11: A19؛
کوچکترین (اگر ($ 6 $ 6<=B11:B19;СТРОКА(B11:B19);"");СТРОКА()-СТРОКА($B$10))
سکته مغزی ($ B $ 10))

بجای وارد ترکیب کلید را فشار دهید Ctrl + Shift + Enter(فرمول ماسیف).

E11: E19 (قیمت ستون) کجا و وارد همان:

فهرست (B11: B19؛
کوچکترین (اگر ($ 6 $ 6<=B11:B19;СТРОКА(B11:B19);"");СТРОКА()-СТРОКА($B$10))
سکته مغزی ($ B $ 10))

در نتیجه، ما یک جدول جدید دریافت می کنیم که شامل تنها کالاهایی است که قیمت ها در سلول کمتر مشخص نشده اند e6 .

برای نشان دادن پویایی درخواست انتخاب دریافت شده، ما را معرفی می کنیم e6 ارزش 55. فقط 2 ورودی به جدول جدید می افتد.

اگر جدول منبع یک محصول جدید را با قیمت 80 اضافه کنید، یک ورودی جدید به طور خودکار به جدول جدید اضافه می شود.

توجه داشته باشید. همچنین می توان آن را نیز برای خروجی داده های فیلتر شده استفاده کرد. انتخاب یک ابزار خاص بستگی به وظیفه کاربر دارد.

اگر راحت نیستید فرمول Massivaکدام مقادیر متعددی را باز می گرداند، می توانید از رویکرد دیگری استفاده کنید که در بخش های زیر در نظر گرفته شده است: 5.a، 7، 10 و 11. در این موارد استفاده می شود.

2. دو معیار عددی (کالایی را انتخاب کنید که قیمت در محدوده کاهش می یابد)

اجازه دهید یک جدول منبع با لیستی از کالاها و قیمت ها وجود داشته باشد (نگاه کنید به مثال فایل، ورقمحدوده اعداد).

معیارهای (مرزهای پایین تر و قیمت بالا) در محدوده قرار می گیرند E5: E6. .

کسانی که. اگر قیمت کالاها به فاصله زمانی مشخص می شود، چنین رکوردی در جدول جدید داده های فیلتر شده ظاهر می شود.

در مقایسه با وظیفه قبلی، ما دو را ایجاد خواهیم کرد: کالاها و قیمت ها (بدون آنها می توانید انجام دهید، اما هنگام نوشتن فرمول ها راحت هستند). فرمول های مربوطه باید مانند یک مدیر نام ( فرمول / نام خاص / نام مدیر) به شرح زیر است (نگاه کنید به شکل زیر).

حالا محدوده را انتخاب کنید D11: D19. و در مقدمه:

فهرست (کالاها؛
کمترین(
اگر (($ e $ 5<=Цены)*($E$6>\u003d قیمت ها)؛ ردیف (قیمت)؛ "")؛

بجای وارد ترکیب کلید را فشار دهید Ctrl + Shift + Enter.

دستکاری های مشابه با محدوده تولید می شوند E11: E19 کجا و معرفی مشابه

فهرست (قیمت؛
کمترین(
اگر (($ e $ 5<=Цены)*($E$6>\u003d قیمت ها)؛ ردیف (قیمت)؛ "")؛
ردیف (قیمت) -Strook ($ B $ 10)) - خط ($ B $ 10))

به عنوان یک نتیجه، ما یک جدول جدید دریافت می کنیم که تنها شامل کالاهایی است که قیمت ها به فاصله زمانی مشخص شده در سلول ها می رسد E5 و e6 .

برای نشان دادن پویایی گزارش دریافت شده (درخواست انتخاب) قرار دهید e6 مقدار 65. یک ورودی دیگر از جدول منبع به جدول جدید اضافه می شود، رضایت معیار جدید را برآورده می کند.

اگر یک محصول جدید را با قیمت در جدول منبع از 25 تا 65 اضافه کنید، یک ورودی جدید به جدول جدید اضافه می شود.

فایل نمونه نیز حاوی فرمول های دستکاری خطا است، زمانی که ستون قیمت حاوی مقدار خطا است، به عنوان مثال # موارد / 0! (برگ را ببینید پردازش خطا).

وظایف زیر به شیوه ای مشابه حل می شود، بنابراین ما آنها را به طور دقیق در نظر نمی گیریم.

3. یک معیار تاریخ (محصولاتی را انتخاب کنید که تاریخ تحویل مشخص شده باشد)

مثال فایل، ورقیک معیار - تاریخ).

برای انتخاب ردیف ها، فرمول های یک آرایه، شبیه به task1 (به جای معیار<= используется =):

=شاخص (A12: A20؛ کوچکترین ($ 6 \u003d B12: B20؛ ردیف (B12: B20)؛ "")؛ رشته (B12: B20) - رشته ($ B $ 11)) - خط ($ B $ 11))

شاخص (B12: B20؛ کوچکترین ($ 6 \u003d B20: B20؛ ردیف (B12: B20)؛ "")؛ رشته (B12: B20) - رشته ($ B $ 11)) - خط ($ B $ 11))

4. دو معیار تاریخ (کالایی را انتخاب کنید که تاریخ تحویل در محدوده سقوط می کند)

اجازه دهید یک جدول منبع با لیستی از تاریخ های کالا و تحویل وجود داشته باشد (نگاه کنید به مثال فایل، ورقمحدوده زمانی).

لطفا توجه داشته باشید که ستون تاریخ مرتب نشده است.

راه حل 1: برای انتخاب ردیف، می توانید استفاده کنید.

وارد سلول شوید D12. فرمول آرایه:

فهرست (12 دلار: 20 دلار؛
بزرگترین (($ e 6 دلار<=$B$12:$B$20)*($E$7>\u003d $ B $ 12: $ B $ 20) * (خط ($ B $ 12: $ B $ 20) -Strokok ($ $ 11))؛
$ J $ 12 خط (A12) + خط ($ B $ 11) +1))

توجه داشته باشید: پس از ورود به فرمول، به جای کلید Enter، شما باید Ctrl + Shift + را فشار دهید + ترکیب کلید را وارد کنید. این ترکیب کلیدی برای وارد کردن فرمول آرایه استفاده می شود.

فرمول Massif را به تعداد مورد نظر سلول ها کپی کنید. این فرمول تنها مقادیری کالاها را که در محدوده این تاریخ ها تحویل داده می شود، باز می گرداند. بقیه سلول ها شامل خطاهای # شماره! خطاهای B. مثال فایل (لیست تاریخ) .

فرمول مشابه باید برای تاریخ در ستون E معرفی شود

در سلول J12 تعداد ردیف جدول منبع رضایت بخش معیارها محاسبه می شود:

جمع آوری (B12: B20؛ "\u003e \u003d" & $ e $ 6؛ B12: B20؛ "<="&$E$7)

ردیف جدول منبع که معیارها را برآورده می کنند.

راه حل 2: برای انتخاب ردیف ها، می توانید از فرمول های آرایه ای شبیه به task2 استفاده کنید (I.E.):

=شاخص (A12: A20؛ کوچکترین (اگر (($ 6 $ 6)<=B12:B20)*($E$7>\u003d B12: B20)؛ خط (B12: B20)؛ "")؛ رشته (B12: B20) -Strkok ($ $ 11)) - خط ($ B $ 11))

شاخص (B12: B20؛ کوچکترین (اگر (($ e 6 دلار)<=B12:B20)*($E$7>\u003d B12: B20)؛ خط (B12: B20)؛ "")؛ رشته (B12: B20) -Strkok ($ $ 11)) - خط ($ B $ 11))

برای ورود به فرمول اول، محدوده سلول ها را برجسته کنید G12: G20 . پس از ورود به فرمول، به جای کلید Enter، شما باید Ctrl + Shift + ترکیب کلید را فشار دهید.

راه حل 3: اگر ستون تاریخ مرتب شده باشد، نمیتوانید از فرمول های آرایه استفاده کنید.

اول، لازم است که اولین و آخرین موقعیت خطوطی را که معیارها را برآورده می کنند محاسبه کنید. سپس رشته های خروجی.

این مثال یکبار دیگر به وضوح نشان می دهد که فرمول های نوشتن آسان تر است.

5. یک معیار تاریخ (آن محصولات را انتخاب کنید که تاریخ تحویل زودتر نیست / نه بعد از مشخص شده)

اجازه دهید یک جدول منبع با لیستی از تاریخ های کالا و تحویل وجود داشته باشد (نگاه کنید به مثال فایل، ورق یک معیار - تاریخ (نه بعد) ).

برای انتخاب ردیف، تاریخ آن پیشتر نیست (از جمله تاریخ خود)، فرمول آرایه استفاده می شود:

=شاخص (A12: A20؛ کوچکترین (اگر ($ e 7 دلار)<=B12:B20;СТРОКА(B12:B20);"");СТРОКА(B12:B20)-СТРОКА($B$11))-СТРОКА($B$11))

همچنین در فایل مثال، فرمول ها داده می شود: نه زودتر (از جمله)؛ نه بعد (از جمله)؛ نه بعدا (از جمله).

7. یک معیار متن (نوع خاصی از کالاها را انتخاب کنید)

اجازه دهید یک جدول منبع با لیستی از کالاها و قیمت ها وجود داشته باشد (نگاه کنید به مثال فایل، ورقیک معیار - متن).

8. دو معیار متن (کالای یک نوع خاص را در ماه مشخص انتخاب کنید)

اجازه دهید یک جدول منبع با لیستی از کالاها و قیمت ها وجود داشته باشد (نگاه کنید به مثال فایل، ورق 2 معیار - متن (ها) ).

فهرست ($ 11 $: $ 19 $؛
کوچکترین (($ 6 \u003d $ 6 \u003d $ $ 11: $ $ 19) * ($ f $ 7 \u003d $ $ 11: $ B $ 19)؛ ردیف ($ 11 $ 11: $ 19 $ 19 ) -STRK ($ 10 $)؛ 30)؛ ردیف (DVSSL ("A1: A" & Estext ($ $ 11: $ 19 دلار)))))))))

اصطلاح ($ f $ 6 \u003d $ $ 11: $ $ 19) * ($ f $ 7 \u003d $ 11 $ 11: $ b $ 19) هر دو شرایط (کالاها و ماه) را مشخص می کند.

اصطلاح ردیف (DVSSL ("A1: A" & Crekok ($ $ 11: $ $ 19))) فرم ها (1: 2: 3: 4: 5: 6: 7: 8: 9)، I.E. اعداد ردیف در جدول.

9. دو معیار متن (محصولات خاصی را انتخاب کنید)

اجازه دهید یک جدول منبع با لیستی از کالاها و قیمت ها وجود داشته باشد (نگاه کنید به مثال فایل، ورق2 معیار - متن (یا)).

در مقایسه با این کار، 7 رشته را با 2 گونه () انتخاب می کند.

برای انتخاب ردیف، فرمول آرایه استفاده می شود:

فهرست (11 دلار: 19 دلار؛
بزرگترین (($ e $ 6 \u003d $ $ 11: $ $ 19) + ($ e $ 7 \u003d $ $ 11: $ $ 19)) * (خط ($ $ 11: $ $ a $ 19) -Strook ($ 10 $)) شمارش شده ($ $ 11: $ $ 19؛ $ E $ 6) + شمارش معکوس ($ $ 11: $ $ 19 $، $ e 7 دلار) -cons ( $ $ 11: A11) +1))

وضعیت ($ e $ 6 \u003d $ $ 11: $ $ 19) + ($ e $ 7 \u003d $ $ 11: $ 19 $) این تضمین می کند که کالاها تنها گونه های مشخص شده از سلول های زرد (محصولات 2 و کالاها 3) انتخاب خواهند شد. A + (افزودن) برای کار مورد استفاده قرار می گیرد (حداقل 1 معیار باید انجام شود).

بیان بالا یک آرایه را باز می کند (0: 0: 0: 0: 1: 1: 1: 0: 0). ضرب او را ردیف ($ $ 11: $ $ 19) -strook ($ 10 $). در (1: 2: 3: 4: 5: 6: 7: 8: 9)، ما مجموعه ای از موقعیت ها (تعداد خطوط جدول) را به دست می آوریم. در مورد ما، این یک آرایه خواهد بود (0: 0: 0: 0: 5: 6: 0: 0).

به عنوان مثال، یک راه حل برای کار زیر را ایجاد کنید: محصولاتی را انتخاب کنید که قیمت آن در محدوده خاصی قرار دارد و تعداد مشخصی از زمان ها یا بیشتر را تکرار می کند.

به عنوان یک اولیه، جدول احزاب کالا را بردارید.

فرض کنید که ما علاقه مند به چند نفر هستیم و کالاهای احزاب با قیمت 1000R عرضه شد. تا سال 2000 (معیار 1). علاوه بر این، احزاب با همان قیمت باید حداقل 3 (معیار 2) باشد.

راه حل فرمول ماسیف است:

کوچکترین ($ A 14: $ $ 27 $) * ($ C $ 14: $ 27 $\u003e \u003d $ B $ 7) * ($ C $ 14: $ 27 $<=$C$7)*($D$14:$D$27>\u003d $ b $ 10)؛ F14 + ($ g $ 8- $ 9))

این فرمول تعداد ردیف را باز می کند که هر دو معیار را برآورده می کنند.

فرمول \u003d SUMMICE (($ C $ 14: $ $ 27\u003e \u003d $ B $ 7) * ($ C $ 14: $ 27 $<=$C$7)*($D$14:$D$27>\u003d $ b $ 10)) تعداد ردیف هایی را که معیارها را برآورده می کنند محاسبه می کند.

11. ما از ارزش معیار (هر) یا (همه) استفاده می کنیم

که در مثال مثال فایل بر روی ورق "11. معیار هر یا (همه)" این گزینه اجرا شده است

فرمول در این مورد باید شامل یک تابع باشد. اگر مقدار انتخاب شود (همه)، فرمول برای خروجی مقادیر بدون توجه به این معیار استفاده می شود. اگر ارزش دیگری انتخاب شود، معیار به طور معمول کار می کند.

اگر ($ c $ 8 \u003d "(همه)"؛
کوچکترین (خط ($ ($ b $ 13: $ 26) -STROK ($ b $ 12)) * ($ d $ 13: $ D $ 26\u003e \u003d $ D $ 8)؛ F13 + ($ g $ 6- $ G $ 7))؛
کوچکترین (خط ($ ($ b $ 13: $ 26) -Strook ($ b $ 12)) * ($ D $ 13: $ D $ 26\u003e \u003d $ D $ 8) * ($ c $ 13: $ C 26 \u003d $ C $ 8)؛ F13 + ($ g $ 6- $ 7)))

بقیه فرمول مشابه آنچه که در بالا ذکر شد، شبیه است.

نمونه برداری از داده ها

یک گزارش نمونه از یک ورق 5 بر روی ستون "عملکرد کیفی، درصد" ایجاد کنید. (از ورق 8، جدول 7)

برای ایجاد یک انتخاب داده، باید مراحل زیر را انجام دهید:

با وارد کردن یک متغیر با استفاده از اپراتور ورودی، تعداد عناصر آرایه جدید را بر روی یک وضعیت مشخص تعیین کنید

اعلام و تجدید آرایه جدید

برای تشکیل یک آرایه جدید. برای انجام این کار، تعدادی از عنصر اول آرایه جدید U \u003d 1 را مشخص کنید. سپس چرخه انجام می شود، که وضعیت نمونه را بر روی ستون "عملکرد کیفیت، درصد" ثبت می کند. اگر نتیجه تأیید حقیقت، عنصر آرایه تجزیه و تحلیل شده عنصر یک آرایه جدید تبدیل شود.

یک عنصر جدید در ورق 8 را بردارید

زیر کودک دفاع ()

ورق ("list8"). انتخاب کنید

کم نور a () به عنوان نوع

redim a (1 تا n1، 1 تا متر)

vvod "sheet5"، a، n1، m، 4

c \u003d inputbox ("وضعیت را وارد کنید")

ورق ("list8"). سلول ها (5.11) \u003d C

برای I \u003d 1 تا N1

اگر یک (من،

8) >

d \u003d d + 1

ورق ("list8"). سلول ها (5.10) \u003d D

کمبود B () به عنوان نوع

Redim B (1 تا D، 1 تا M)

برای I \u003d 1 تا N1

اگر یک (من،

8)\u003e ورق ("list8"). سلول ها (5.11) سپس

برای j \u003d 1 به m

B (U، J) \u003d a (i، j)

u \u003d U + 1

برای من \u003d 1 تا د

برای j \u003d 1 به m

ورق ("list8"). سلول ها (i + 4، j) \u003d b (i، j)

شکل 6. داده های جدول پس از نمونه برداری

ایجاد یک ماکرو نمونه اتوماتیک

ضبط ماکرو را روشن کنید خدمات\u003e ماکرو\u003e شروع ضبط\u003e OK. مربع به نظر می رسد که در آن دکمه توقف. بر روی ورق 5 (گزارش)، ما جدول را بدون هدر و نتایج برجسته، کپی به لیست 10 (خودکار جمع کننده). ما جدول را بدون هدر و در آیتم منو برجسته می کنیم، داده ها را انتخاب کنید\u003e Filter\u003e Auto Filter\u003e Service\u003e OK را انتخاب کنید. ما ستون را که ما مرتب می کنیم جشن می گیریم. کار ماکرو را کامل کنید.

مقدار ماکرو 2 ()

"ماکرو 22 ماکرو ماکرو

ورق ("list5"). انتخاب کنید

انتخاب. کپی 🀄.

ورق ("list9"). انتخاب کنید

ActiveSheet. چسباندن

محدوده ("H5: H17"). انتخاب کنید

کاربرد. cutcopymode \u003d نادرست

انتخاب. autofilter

ActiveSheet. محدوده ("$ H $ 5: $ H $ 17"). فیلد autofilter: \u003d 1، معیارها 1: \u003d "\u003e 80"، _

اپراتور: \u003d Xland

محدوده ("G22"). انتخاب کنید

شکل 7 داده های جدول پس از نمونه برداری خودکار

تعیین حداکثر و حداقل مقدار

مقادیر حداکثر و دقیقه را در ستون "مجموع"، "عملکرد مطلق، درصد"، "عملکرد با کیفیت بالا" (جدول 9، ورق 10) تعیین کنید

برای تعیین مقادیر حداکثر و حداقل، شما باید مراحل زیر را انجام دهید:

متغیر استاندارد را تنظیم کنید که حداقل حداقل (حداکثر) باشد

در اوایل هر عنصر مجموعه را با حداقل حداقل (حداکثر) مقایسه می کند و اگر این عنصر شرایط جستجو را برآورده نکند (در موارد حداقل، بیشتر، و در مورد حداکثر - کمتر)، سپس استاندارد مقدار با مقدار عنصر مقایسه شده جایگزین می شود

پس از مشاهده کامل تمام عناصر در متغیر حداقل حداقل (حداکثر) حداقل معتبر (حداکثر)

مقدار حداقل (حداکثر) در سلول های مربوطه نمایش داده می شود.

Sub Minmax ()

کم نور a () به عنوان نوع

n1 \u003d ورق ("sheet4"). سلول ها (5،12)

m \u003d ورق ("list2"). سلول ها (5،12)

redim a (1 تا n1، 1 تا متر)

vvod "sheet5"، a، n1، m، 4

vivod "list10"، a، n1، m، 4

vvod "list10"، a، n1، m، 4

برای j \u003d 3 تا متر

maxa \u003d 0.00001

مینا \u003d 1000000.

برای I \u003d 1 تا N1

اگر a (i، j)\u003e maxa thatn

maxa \u003d a (i، j)

اگر a (i، j)< minA Then

mina \u003d a (i، j)

ورق ("list10"). سلول ها (I + 4 + 2، J) \u003d Maxa

ورق ("list10"). سلول ها (I + 4 + 3، J) \u003d مینا

ماکرو یک دنباله ای از اقداماتی است که برای استفاده بیشتر ثبت شده و ذخیره می شود. ماکرو ذخیره شده را می توان با یک تیم ویژه بازتولید کرد. به عبارت دیگر، شما می توانید اقدامات خود را در ماکرو ضبط کنید، آن را ذخیره کنید، و سپس به کاربران دیگر اجازه بازی در ماکرو عمل را به سادگی با فشار دادن کلید. این به خصوص در هنگام توزیع گزارش های جداول خلاصه مناسب است.

فرض کنید شما می خواهید مشتریان خود را با این فرصت برای گروه های گزارش شده از جداول تلفیقی ماهها، ماه ها و سال ها ارائه دهید. از لحاظ فنی، فرآیند گروه بندی می تواند هر کاربر را انجام دهد، اما برخی از مشتریان شما لازم نیست که این را درک کنند. در یک مورد مشابه، شما می توانید یک MACRO از گروه بندی را برای چند ماه، دیگر - در محله ها و سوم - به سال. سپس سه دکمه ایجاد کنید - یکی بر روی هر ماکرو. سپس مشتریانی که تجربه را با جداول تلفیقی ندارند، فقط باید روی دکمه کلیک کنید تا جدول خلاصه را به درستی گروه بندی کنید.

مزیت اصلی استفاده از ماکروها در گزارش های جداول تلفیقی، ارائه مشتریان با توانایی به سرعت انجام چنین عملیاتی در جداول تلفیقی است که نمی توانند در وضعیت معمول انجام دهند. با توجه به این، اثربخشی تجزیه و تحلیل داده های ارائه شده به طور قابل توجهی افزایش می یابد.

یک یادداشت را در فرمت دانلود کنید یا با نمونه هایی دانلود کنید (در داخل فایل اکسل با ماکرو؛ سیاست ارائه دهنده اجازه نمی دهد به طور مستقیم آپلود فایل این فرمت به سایت).

رکورد ماکرو

نگاهی به جدول تلفیقی نشان داده شده در شکل. 1. شما می توانید این جدول تلفیقی را به صورت راست کلیک کنید و دستور را انتخاب کنید. تازه کردن. اگر در هنگام به روز رسانی جدول محوری، اقدامات را در قالب یک ماکرو ضبط کردید، پس از آن شما یا هر کاربر دیگر می توانید این اقدامات را تولید کنید و جدول تلفیقی را به عنوان یک نتیجه از شروع ماکرو به روز کنید.

شکل. 1. ضبط اقدامات در هنگام به روز رسانی این جدول خلاصه، داده ها را به صورت یک نتیجه از راه اندازی ماکرو به روز می کند

مرحله اول در رکورد ماکرو، تماس جعبه محاوره ای است رکورد ماکرو. روی برگه کلیک کنید توسعه دهنده Ribbons و روی دکمه کلیک کنید رکورد ماکرو. (اگر شما نمی توانید برگه را روی نوار پیدا کنید توسعه دهنده، برگه را انتخاب کنید فایلو بر روی دکمه کلیک کنید مولفه های. در کادر محاوره ای که گفتگو را نشان می دهد تنظیمات اکسل یک دسته را انتخاب کنید نوار تنظیم و در لیست سمت راست، کادر را انتخاب کنید توسعه دهنده. به عنوان یک نتیجه، نوار بر روی نوار ظاهر می شود توسعه دهنده.) راه جایگزین برای شروع ضبط ماکرو - روی دکمه کلیک کنید (شکل 2).

در کادر محاوره ای رکورد ماکرو اطلاعات ماکرو زیر را وارد کنید (شکل 3):

نامماکرو. این نام باید اقدامات انجام شده توسط ماکرو را توصیف کند. نام باید با حرف یا حروف زیر شروع شود؛ نباید شامل فضا و سایر نشانه های غیر قابل قبول باشد؛ این نباید با نام Expedded Excel یا نام یک شی دیگر در کتاب همخوانی داشته باشد.

ترکیبیکلیدها. در این زمینه، شما می توانید هر نامه را وارد کنید. این بخشی از ترکیب کلیدی است که برای بازی ماکرو استفاده می شود. ترکیبی کلید اختیاری است. به طور پیش فرض، فقط CTRL به عنوان یک شروع ترکیبی ارائه می شود. اگر می خواهید ترکیب را نیز تغییر دهید، همچنین در حالی که کلید Shift را نگه دارید، نامه را در پنجره شماره گیری کنید

صرفه جوییکه در. این نشان دهنده محل ذخیره سازی ماکرو است. اگر قصد دارید گزارش جدول خلاصه ای را در میان سایر کاربران توزیع کنید، یک پارامتر را انتخاب کنید اینکتاب. اکسل همچنین به شما اجازه می دهد تا ماکرو را ذخیره کنید کتاب جدید یا در کتاب شخصی ماکرو.

شرح. این فیلد توصیف ماکرو ایجاد شده را توصیف می کند.

شکل. 3. راه اندازی پنجره رکورد ماکرو

از آنجا که ماکرو جدول تلفیقی را به روز می کند، نام را انتخاب کنید ارتقاء. شما همچنین می توانید ترکیبی کلید CTRL + Shift + Q را اختصاص دهید. به یاد داشته باشید که پس از ایجاد ماکرو، شما از این ترکیب کلیدی برای شروع آن استفاده خواهید کرد. یک پارامتر را به عنوان مکان ذخیره سازی ماکرو انتخاب کنید این کتاب و کلیک کنید خوب.

پس از کلیک کردن در کادر محاوره ای رکورد ماکرو بر روی دکمه خوب ماکرو شروع می شود شروع می شود. در این مرحله، تمام اقداماتی که در اکسل انجام می دهید ثبت می شود.

راست کلیک در منطقه جدول تلفیقی و دستور را انتخاب کنید. تازه کردن (همانطور که در شکل 1، اما در حال حاضر در حالت ضبط ماکرو). پس از به روز رسانی جدول محوری، می توانید فرایند ضبط ماکرو را با استفاده از دکمه متوقف کنید. ضبط را متوقف کن زبانه ها توسعه دهنده. یا دوباره بر روی دکمه نشان داده شده در شکل کلیک کنید. 2

بنابراین، شما فقط اولین ماکرو خود را ثبت کردید. در حال حاضر شما می توانید ماکرو را با استفاده از CTRL + Shift + Q ترکیبی کلید را اجرا کنید.

هشدار امنیت ماکرو. لازم به ذکر است که در صورت ضبط ماکروها توسط کاربر، بدون هیچ گونه محدودیتی در بخش زیرسیستم امنیتی انجام می شود. با این وجود، هنگام توزیع یک کتاب کاری که حاوی ماکرو هستند، لازم است که کاربران دیگر فرصت ارائه دهند تا اطمینان حاصل شود که هیچ خطر در باز کردن فایل های کاری وجود ندارد و اجرای ماکروها منجر به عفونت سیستم ویروس نمی شود. به طور خاص، شما بلافاصله متوجه خواهید شد که فایل مثال مورد استفاده در این فصل به طور کامل عملکرد نخواهد کرد، اگر به طور خاص مجاز به اکسل برای انجام ماکرو در آن نیست.

ساده ترین راه برای اطمینان از ایمنی ماکرو، ایجاد یک مکان قابل اعتماد است - پوشه ای که تنها کتاب های "قابل اعتماد" که حاوی ویروس ها نیستند، قرار می گیرند. مکان قابل اعتماد به شما و مشتریان شما اجازه می دهد تا ماکروها را در مشاغل بدون هیچ محدودیتی در بخشی از زیر سیستم امنیتی انجام دهند (چنین رفتاری ذخیره می شود تا زمانی که کتاب های کاری در پوشه اعتماد باشند).

برای پیکربندی یک مکان قابل اطمینان، این مراحل را دنبال کنید.

برگه Ribbon را انتخاب کنید توسعه دهنده و بر روی دکمه کلیک کنید ماکرو ایمنی. یک کادر محاوره ای روی صفحه نمایش ظاهر می شود. مرکز مدیریت امنیت.

روی دکمه کلیک کنید اضافه کردن مکان جدید.

روی دکمه کلیک کنید بررسی اجمالیبرای مشخص کردن یک پوشه برای فایل های کاری که به آن اعتماد دارید مشخص کنید.

پس از مشخص کردن یک مکان قابل اطمینان، ماکروهای دلخواه به طور پیش فرض برای تمام کتابهای کاری در آن انجام می شود.

در اکسل 2013، مدل امنیتی بهبود یافت. در حال حاضر فایل های کاری که قبلا "اعتماد" به یاد می آورد، به یاد می آورد، به یاد داشته باشید پس از باز کردن یک کتاب اکسل و بر روی دکمه کلیک کنید محتوای را فعال کنید اکسل این دولت را به یاد می آورد. در نتیجه، این کتاب به تخلیه اعتماد می رسد، و سوالات غیر ضروری در طول باز شدن بعدی آن مشخص نشده است.

ایجاد یک رابط کاربر با استفاده از کنترل های فرم

راه اندازی یک ماکرو با استفاده از ترکیب کلید Ctrl + Shift + Q در مورد زمانی که یک گزارش جدول تلفیقی تنها یک ماکرو دارد. (علاوه بر این، کاربران باید این ترکیب را بدانند.)، فرض کنید شما می خواهید مشتریان خود را با چندین ماکرو انجام اقدامات مختلف ارائه دهید. در این مورد، شما باید مشتریان را با یک روش روشن و آسان برای راه اندازی هر ماکرو، بدون توسل به حفظ ترکیبات کلیدی ارائه دهید. راه حل کامل یک رابط کاربر ساده در قالب مجموعه ای از کنترل هایی مانند دکمه ها، نوارهای پیمایش و سایر ابزارهایی است که به شما اجازه می دهد ماکرو را با کلیک های ماوس انجام دهید.

اکسل در اختیار شما قرار می دهد مجموعه ای از ابزارهایی طراحی شده برای ایجاد یک رابط کاربر به طور مستقیم در صفحه گسترده. این ابزار عناصر کنترل فرم نامیده می شود. ایده اصلی این است که شما می توانید کنترل فرم را در صفحه گسترده قرار دهید و یک ماکرو را به او اختصاص دهید که قبلا ثبت شده است. پس از انتصاب، ماکرو بر روی این عنصر اجرا می شود.

کنترل های فرم را می توان در گروه یافت عناصر کنترل فرم نوارهای نوار توسعه دهنده. برای باز کردن پالت کنترل، در این گروه روی دکمه کلیک کنید قرار دادن (شکل 4).

شکل. 4. عنصر کنترل فرم دکمه

توجه: علاوه بر کنترل های فرم، پالت نیز وجود دارد عناصر ActiveX. اگر چه آنها مشابه هستند، آنها از لحاظ برنامه های کاربردی متفاوت هستند. عناصر کنترل فرمبا ویژگی های محدود آن و تنظیمات ساده، مخصوصا برای اقامت در ورق های کار طراحی شده است. در همان زمان عناصر ActiveX به طور عمده در فرم های سفارشی اعمال می شود. یک قانون را برای ارسال در ورق های کار به طور انحصاری کنترل کنید.

شما باید کنترل هایی را انتخاب کنید که برای حل این کار مناسب است. در این مثال، مشتریان باید توانایی به روز رسانی جدول تلفیقی را با کلیک بر روی دکمه ارائه دهند. روی عنصر کنترل کلیک کنید دکمه، اشاره گر ماوس را به محل کار ورق، که در آن دکمه باید قرار داشته باشد، حرکت دهید و روی ماوس کلیک کنید.

پس از قرار دادن دکمه در جدول، کادر محاوره ای باز می شود. ماکرو را تعیین کنید هدف - شی (شکل 5). ماکرو مورد نظر را انتخاب کنید (در مورد ما - ارتقاء، قبلا ثبت شده) و روی دکمه کلیک کنید خوب.

شکل. 5. ماکرو را انتخاب کنید تا به دکمه اختصاص داده شود و روی دکمه کلیک کنید. خوب. در این مورد، ماکرو باید اعمال شود ارتقاء

پس از قرار دادن تمام کنترل های لازم در جدول محوری، می توانید جدول را برای ایجاد یک رابط اساسی فرمت کنید. در شکل 6 گزارش خلاصه ای از جدول پس از قالب بندی را نشان می دهد.

تغییر ماکرو ثبت شده

به عنوان یک نتیجه از ضبط ماکرو، برنامه اکسل یک ماژول را ایجاد می کند که فروشگاه شما را تکمیل کرده اند. تمام اقدامات ثبت شده توسط رشته های VBA-Code نشان داده شده است که از آن ماکرو متشکل است. شما می توانید قابلیت های مختلف را به گزارش های جدول تلفیقی اضافه کنید، پیکربندی کد VBA برای به دست آوردن نتایج مورد نیاز. برای درک آسان تر شدن چگونگی این همه کارها، ایجاد یک ماکرو جدید، اخراج پنج سوابق اول در مورد مشتریان را ایجاد کنید. روی برگه کلیک کنید توسعه دهنده و بر روی دکمه کلیک کنید رکورد ماکرو. جعبه محاوره ای که در شکل نشان داده شده است. 7. نام ماکرو ایجاد شده اولینnsakazchikov و تعیین محل صرفه جویی این کتاب. کلیک خوببرای شروع یک ماکرو

پس از شروع ضبط، روی فلش کنار میدان کلیک کنید نام مشتری، انتخاب کنید فیلتر بر اساس ارزش و گزینه 10 سال اول (شکل 8a). در کادر محاوره ای که ظاهر می شود، تنظیمات را همانطور که در شکل نشان داده شده تنظیم کنید. 8b این تنظیمات نشان می دهد که داده های پنج مشتری را نشان می دهد، بهترین از لحاظ فروش. کلیک خوب.

شکل. 8. فیلتر (A) را انتخاب کنید و پارامترها را پیکربندی کنید (ب) برای نمایش پنج مشتری برتر برای فروش.

پس از موفقیت موفقیت آمیز تمام اقدامات مورد نیاز برای استخراج پنج مشتری برتر در فروش، به برگه بروید توسعه دهنده و بر روی دکمه کلیک کنید ضبط را متوقف کن.

در حال حاضر شما یک ماکرو دارید که جدول تلفیقی را برای استخراج پنج مشتری برتر در فروش فیلتر می کند. باید انجام شود به طوری که ماکرو واکنش نشان می دهد به حالت نوار اسکرول، I.E. با استفاده از نوار اسکرول، شما باید قادر به مشخص کردن تعداد ماکرو مشتریانی که داده ها در گزارش جدول تلفیقی نمایش داده می شود. بنابراین، با استفاده از نوار پیمایش، کاربر قادر خواهد بود تا پنج بهترین، هشت بهترین یا سی و دو بهترین مشتری را به عهده بگیرد.

برای اضافه کردن پیمایش به صفحه گسترده به صفحه گسترده، به برگه بروید. توسعه دهندهروی دکمه کلیک کنید قرار دادنکنترل روی پالت را انتخاب کنید نوارچرخ و موقعیت آن را در ورق کار قرار دهید. راست کلیک بر روی عنصر کنترل نوارچرخ شیء فرمت. جعبه محاوره ای باز می شود فرمت کنترل (شکل 9). در آن، تغییرات زیر را در تنظیمات ایجاد کنید: پارامتر حداقل مقدار نکته 1، پارامتر حداکثر ارزش - ارزش 200، و در زمینه ارتباط با سلول مقدار $ M $ 2 را وارد کنید تا در سلول M2 نوار پیمایش را نمایش دهد. روی دکمه کلیک کنید خوببرای اعمال تنظیمات قبلا مشخص شده.

حالا شما باید کلان تازه ثبت شده را مقایسه کنید اولینnsakazchikov با عنصر کنترل نوارچرخواقع در ورق کار راست کلیک بر روی عنصر کنترل نوارچرخ و در منوی زمینه، دستور را انتخاب کنید ماکرو را تعیین کنیدبرای باز کردن کادر محاوره ای MACRO. نوار اسکرول را ثبت کنید ماکرو ضبط شده firstnenzakazchikov. ماکرو هر بار پس از کلیک روی نوار پیمایش انجام می شود. تست نوار پیمایش ایجاد شده را آزمایش کنید. پس از کلیک کردن بر روی خط، ماکرو شروع خواهد شد firstnenzakazchikov و شماره در سلول M2 تغییر خواهد کرد تا وضعیت نوار اسکرول را نمایش دهد. تعداد سلول M2 نقش مهمی ایفا می کند، زیرا برای اتصال به ماکرو به نوار پیمایش استفاده می شود.

تنها چیزی که باید انجام شود این است که ماکرو را مجبور کنیم تا عدد را در سلول M2 پردازش کنیم، آن را با یک نوار پیمایش متصل کنیم. برای انجام این کار، به کد VBA کلان بروید. برای انجام این کار، به برگه بروید توسعه دهنده و بر روی دکمه کلیک کنید ماکرو. جعبه محاوره ای باز می شود ماکرو (شکل 10). شما می توانید ماکرو انتخاب شده را حذف، حذف و ویرایش کنید. برای نمایش کد ماکرو VBA بر روی صفحه نمایش، ماکرو را انتخاب کنید و روی دکمه کلیک کنید. تغییر دادن.

شکل. 10. برای دسترسی به کد ماکرو VBA اولینnsakazchikov، ماکرو را انتخاب کنید و روی دکمه کلیک کنید تغییر دادن

ویرایشگر ویژوال بیسیک با کد VBA از ماکرو بر روی صفحه نمایش (شکل 11). هدف شما این است که جایگزین شماره 5 به شدت مشخص شده در کد، نصب شده در طول ضبط ماکرو، مقدار در سلول M2، که به نوار پیمایش گره خورده است. در ابتدا، یک ماکرو ثبت شد، طراحی شده برای نمایش پنج مشتری اول که بیشترین درآمد را دارند.

شماره 5 را از کد حذف کنید و به جای آن عبارت زیر را وارد کنید:

ActiveSheet.Range ("M2") .Value

برای تمیز کردن فیلترها دو رشته را به ماکرو اضافه کنید:

محدوده ("A4") .select
ActiveSheet.pivottables ("Pivottable1") .pivotfields ("نام مشتری") .clearallfilters

در حال حاضر کد ماکرو باید مانند شکل نشان داده شده در شکل. 12

ویرایشگر ویژوال بیسیک را ببندید و به جدول تلفیقی بازگردید. تست نوار اسکرول، کشیدن لغزنده به مقدار 11. ماکرو باید شروع و فیلتر 11 سوابق در مورد بهترین مشتریان فروش.

هماهنگ سازی دو جداول تلفیقی با استفاده از یک لیست کشویی

گزارش نشان داده شده در شکل. 13، شامل دو جداول خلاصه است. هر یک از آنها دارای یک فیلد صفحه ای است که به شما اجازه می دهد یک بازار فروش را انتخاب کنید. مشکل این است که هر بار که یک بازار را در زمینه صفحه یک میز پیلون انتخاب می کنید، باید همان بازار را در قسمت صفحه یک جدول محوری دیگر انتخاب کنید. همگام سازی فیلترهای دو جدول در مرحله تجزیه و تحلیل داده ها یک مشکل بزرگ را نشان نمی دهد، اما شانس وجود دارد که شما یا مشتریان خود هنوز آن را فراموش کنید.

شکل. 13. دو جداول خلاصه شامل زمینه های صفحه ای هستند که فیلتر کردن داده ها را در بازارها انجام می دهند. برای تجزیه و تحلیل داده های یک بازار جداگانه، شما باید هر دو جداول خلاصه را همگام سازی کنید.

یک روش هماهنگ سازی این جداول خلاصه این است که از لیست کشویی استفاده کنید. ایده این است که ماکروها را ضبط کنیم، که بازار مورد نظر را از میدان انتخاب می کند بازار فروش در هر دو جداول سپس شما باید یک لیست کشویی ایجاد کنید و آن را با نام بازارهای فروش از دو جداول خلاصه پر کنید. در نهایت، ماکرو ضبط شده باید تغییر داده شود تا هر دو جداول خلاصه را با استفاده از مقادیر از لیست کشویی فیلتر کند. برای حل این کار، شما باید مراحل زیر را انجام دهید.

1. یک ماکرو جدید ایجاد کنید و یک نام را به آن اختصاص دهید. synchmarkets.. هنگامی که ورود شروع می شود، در فیلد انتخاب کنید فروش بازار هر دو فروش جداول فروش بازار کالیفرنیا و رکورد ماکرو را متوقف کنید.

2. کنترل های فرم بر روی صفحه نمایش را نمایش دهید و لیست کشویی را روی صفحه نمایش اضافه کنید.

3. یک لیست دقیق مشخص شده از تمام بازارهای جدول تلفیقی ایجاد کنید. توجه داشته باشید که اولین عنصر لیست نشان دهنده مقدار (همه) است. شما باید این عنصر را فعال کنید اگر می خواهید بتوانید تمام بازارها را در لیست کشویی انتخاب کنید.

4. در این مرحله گزارش جدول تلفیقی باید مانند شکل نشان داده شده در شکل. چهارده.

شکل. 14. در اختیار شما تمام بودجه مورد نیاز است: ماکرو تغییر میدان بازار فروش هر دو جداول محوری، یک لیست کشویی و فهرست تمام بازارهای فروش موجود در جدول تلفیقی

5. راست کلیک بر روی لیست کشویی و فرمان را در منوی زمینه انتخاب کنید. شیء فرمتبرای پیکربندی کنترل

6. اول، محدوده منبع مقادیر مورد استفاده برای پر کردن لیست کشویی را تنظیم کنید، همانطور که در شکل نشان داده شده است. 15. در این مورد، ما در مورد لیست بازارهای فروش ایجاد شده توسط شما در پاراگراف 3 صحبت می کنیم، سپس سلول را مشخص کنید که تعداد توالی مورد انتخاب شده را نمایش می دهد (در این مثال، سلول H1 است). پارامتر تعداد خطوط لب تعیین می کند که چند ردیف به طور همزمان در لیست کشویی منعکس می شود. روی دکمه کلیک کنید خوب.

شکل. 15. تنظیمات لیست کشویی باید فهرست بازارهای فروش را به عنوان محدوده اولیه مقادیر، و به عنوان نقطه اتصال - برای تعیین سلول H1 نشان می دهد

7. حالا شما این فرصت را دارید که بازار فروش را در لیست کشویی انتخاب کنید، همچنین تعیین تعداد توالی مرتبط در سلول H1 (شکل 16). سوال مطرح می شود: چرا ارزش شاخص آن به جای نام واقعی بازار است؟ از آنجا که لیست کشویی هیچ نامی را باز می گرداند، اما شماره. به عنوان مثال، هنگامی که لیست کشویی کالیفرنیا را در سلول H1 انتخاب می کنید، مقدار به نظر می رسد 5. این بدان معنی است که کالیفرنیا عنصر پنجم لیست است.

شکل. 16. لیست کشویی در حال حاضر با نام بازارها پر شده است و تعداد انتخاب شده از بازار انتخاب شده در سلول H1 نمایش داده می شود.

8. برای استفاده از شماره توالی به جای نام بازار، باید با استفاده از تابع شاخص آن را ارسال کنید.

9. تابع شاخص را وارد کنید که شماره توالی را از سلول H1 به یک مقدار روشن تبدیل می کند.

10. ویژگی شاخص دو استدلال را انجام می دهد. اولین استدلال نشان دهنده طیف وسیعی از مقادیر لیست است. در بیشتر موارد، شما از همان محدوده استفاده می کنید که منوی کشویی پر شده است. استدلال دوم یک شماره توالی است. اگر شماره توالی در سلول وارد شده باشد (به عنوان مثال، در سلول H1، همانطور که در شکل 17)، شما می توانید به سادگی به این سلول مراجعه کنید.

شکل. 17. شاخص تابع در سلول I1 تعداد توالی ذخیره شده در سلول H1 را به مقدار تبدیل می کند. شما از یک مقدار در سلول I1 برای تغییر ماکرو استفاده می کنید

11. ویرایش ماکرو synchmarkets.با استفاده از مقدار در سلول I1 به جای یک مقدار مشخص شده مشخص شده. روی برگه کلیک کنید توسعه دهنده و بر روی دکمه کلیک کنید ماکرو. کادر محاوره ای روی صفحه نمایش نشان داده شده در شکل نشان داده شده است. 18. ماکرو را در آن انتخاب کنید synchmarkets. و بر روی دکمه ویرایش کلیک کنید.

شکل. 18. برای دسترسی به کلان کد VBA، ماکرو را انتخاب کنید synchmarkets. و کلیک کنید تغییر دادن

12. هنگام ضبط یک ماکرو، شما یک بازار فروش کالیفرنیا را از میدان در هر دو جداول خلاصه انتخاب کرده اید بازار فروش. همانطور که در شکل دیده میشود. 19، بازار کالیفرنیا در حال حاضر به شدت در کد VBA از ماکرو تنظیم شده است.

13. ارزش "کالیفرنیا" را جایگزین عبارات ActiveSheet.Range ("I1"). ارزش گذاری، که به مقدار در سلول I1 اشاره دارد. در این مرحله، کد ماکرو باید مانند شکل نشان داده شود. 20. پس از تغییر ماکرو، ویرایشگر ویژوال بیسیک را ببندید و به صفحه گسترده بازگردید.

شکل. 20. مقدار "کالیفرنیا" را با یک عبارت ActiveSheet.Range ("I1") جایگزین کنید. ارزش و ویرایشگر ویژوال بیسیک را ببندید

14. تنها برای اطمینان از اجرای ماکرو در هنگام انتخاب یک بازار فروش در لیست کشویی، باقی می ماند. راست کلیک بر روی لیست کشویی و پارامتر را انتخاب کنید ماکرو را تعیین کنید. ماکرو را انتخاب کنید synchmarket و بر روی دکمه کلیک کنید خوب.

15. مخفی کردن ردیف ها و ستون ها با زمینه های صفحه در جداول تلفیقی، و همچنین فهرست بازارهای و فرمول های شاخصی که ایجاد کرده اید.

در شکل 21 نتیجه نهایی را نشان می دهد. شما یک رابط کاربری دریافت کرده اید که به مشتریان اجازه می دهد تا بازار فروش را در هر دو جداول خلاصه با استفاده از یک لیست کشویی انتخاب کنند.

هنگامی که عنصر جدید را در لیست کشویی انتخاب می کنید، اندازه ستون ها به طور خودکار تغییر می کند تا تمام داده های نمایش داده شده در آنها را جایگزین کند. رفتار مشابهی از برنامه با قالب بندی قالب ورق کار خسته شده است. شما می توانید از آن جلوگیری کنید اگر بر روی خلاصه ای از دکمه راست راست کلیک کنید و دستور را انتخاب کنید تنظیمات جدول ارسال شده. یک کادر محاوره ای روی صفحه نمایش ظاهر می شود که در آن شما می خواهید چک کنید. به طور خودکار عرض ستون ها را در هنگام به روز رسانی تغییر دهید.

این یادداشت بر اساس کتاب ژله، الکساندر نوشته شده است. . فصل 12

با استفاده از ابزارهای اکسل، می توانید یک داده خاص را از محدوده تصادفی، یک وضعیت یا بیشتر انتخاب کنید. برای حل چنین مشکلات، آرایه یا فرمول های ماکرو یا ماکرو استفاده می شود. در نمونه ها را در نظر بگیرید.

چگونه یک نمونه را در اکسل بسازیم

هنگام استفاده از فرمول آرایه، داده های انتخاب شده در یک جدول جداگانه نشان داده شده است. مزیت این روش در مقایسه با فیلتر معمولی چیست؟

جدول منبع:

ابتدا یاد بگیرید که یک نمونه را بر روی یک معیار عددی انجام دهید. وظیفه این است که از جدول کالاها با قیمت بالاتر از 200 روبل انتخاب کنید. یک راه حل برای حل فیلتر کردن است. در نتیجه، تنها محصولاتی که پرس و جو را برآورده می کنند، در جدول منبع باقی خواهند ماند.

راه حل دیگری این است که از فرمول آرایه استفاده کنید. ردیف های پرس و جو مرتبط در یک جدول گزارش جداگانه قرار می گیرند.

ابتدا یک جدول خالی را در کنار اصلی ایجاد می کنیم: سرصفحه های تکراری، تعداد ردیف ها و ستون ها. جدول جدید، محدوده E1: G10.Tube را اشغال می کند E2: E10 (ستون "تاریخ") و فرمول زیر را معرفی کنید: ( }.

به منظور به دست آوردن فرمول Massif، Ctrl + Shift + کلید ترکیبی را وارد کنید. در ستون بعدی - "محصول" - ما یک فرمول جامد مشابه را معرفی می کنیم: ( ) تنها اولین استدلال عملکرد تابع تغییر کرده است.

در ستون "قیمت"، ما همان فرمول آرایه را با تغییر اولین استدلال تابع شاخص معرفی می کنیم.

در نتیجه، ما یک گزارش کالا را با قیمت بیش از 200 روبل دریافت می کنیم.

این نمونه پویا است: هنگامی که درخواست یا ظاهر را در جدول منبع محصولات جدید تغییر دهید، این گزارش به طور خودکار تغییر خواهد کرد.

شماره کار 2 - انتخاب از جدول منبع کالای که به فروش رسید 09/20/2015. به عبارت دیگر، معیار انتخاب تاریخ است. برای راحتی، تاریخ مورد نظر را به یک سلول جداگانه، I2 معرفی می کنیم.

برای حل مشکل، یک فرمول آرایه ای مشابه استفاده می شود. فقط به جای معیار).

چنین فرمول ها به ستون های دیگر (CM بالا) معرفی می شوند.

حالا ما از یک معیار متن استفاده می کنیم. به جای تاریخ در سلول I2، ما متن "محصول 1" را معرفی می کنیم. کمی فرمول آرایه را تغییر دهید: ( }.

چنین تابع نمونه برداری بزرگ در اکسل.



نمونه برداری برای چندین شرایط در اکسل

اول، دو معیار عددی را مصرف کنید:

وظیفه این است که کالاهای کمتر از 400 و بیش از 200 روبل را انتخاب کنید. ما اصطلاحات را ترکیب می کنیم "*". فرمول آرایه به شرح زیر است: ( }.!}

این برای ستون اول جدول گزارش است. برای دوم و سوم، اولین شاخص تابع استدلال را تغییر دهید. نتیجه:

برای ایجاد یک نمونه در چندین تاریخ یا معیارهای عددی، از فرمول های مشابه مشابه استفاده می کنیم.

نمونه تصادفی در اکسل

هنگامی که کاربر با مقدار زیادی از داده ها کار می کند، برای تجزیه و تحلیل بعدی، ممکن است نمونه تصادفی مورد نیاز باشد. هر ردیف را می توان یک عدد تصادفی اختصاص داد و سپس مرتب سازی برای نمونه برداری را اعمال کرد.

مجموعه داده منبع:

ابتدا دو ستون خالی را در سمت چپ قرار دهید. در سلول A2، ما فرمول چسبنده را بیان می کنیم (). چرخش آن را بر روی کل ستون:

در حال حاضر یک ستون را با اعداد تصادفی کپی کنید و آن را به یک ستون V وارد کنید. این لازم است به طوری که این اعداد در هنگام ایجاد اطلاعات جدید به سند تغییر نمی کنند.

برای قرار دادن مقادیر، و نه فرمول، دکمه سمت راست ماوس را روی ستون کلیک کنید و ابزار "Insert ویژه" را انتخاب کنید. در پنجره ای که باز می شود، ما یک علامت را در مقابل "ارزش" قرار می دهیم:

حالا شما می توانید داده ها را در ستون به صعود یا نزولی مرتب کنید. روش برای نشان دادن ارزش های اولیه نیز تغییر خواهد کرد. ما هر تعداد ردیف را از بالا یا پایین انتخاب می کنیم - یک نمونه تصادفی دریافت می کنیم.

متفرقه (39)
اشکالات اکسل و زلزله (3)

چگونه می توان یک لیست از ارزش های منحصر به فرد (نه تکرار) را دریافت کرد؟

تصور کنید یک لیست بزرگ از نام های مختلف، نام کامل، شماره جدول، و غیره و لازم است که لیست از همه نام های مشابه از این لیست را ترک کنید، اما آنها تکرار نمی کنند - I.E. حذف تمام سوابق تکراری از این لیست. چگونه در غیر این صورت نامیده می شود: ایجاد یک لیست از عناصر منحصر به فرد، لیستی از غیر تکراری، بدون تکراری. برای این، راه های متعددی وجود دارد: ابزار اکسل جاسازی شده، فرمول های داخلی و، در نهایت، با کد ویژوال بیسیک برای برنامه (VBA) و جداول خلاصه در این مقاله، هر یک از گزینه ها را در نظر بگیرید.

با استفاده از ویژگی های داخلی اکسل 2007 و بالاتر
در اکسل 2007 و 2010 ساده تر است که ساده تر شود - یک تیم خاص وجود دارد که نامیده می شود -. این در برگه واقع شده است داده ها (داده ها) بخش ابزار داده (ابزار داده)

نحوه استفاده از این فرمان. ستون برجسته (یا بیشتر) با آن اطلاعاتی که می خواهید ورودی های تکراری را حذف کنید. به برگه بروید داده ها (داده ها) -حذف تکراری (حذف تکراری).

اگر یک ستون را انتخاب کنید، اما ستون های بیشتری با داده ها (یا حداقل یک ستون) وجود خواهد داشت، سپس Excel پیشنهاد می دهد که انتخاب کنید: برای گسترش محدوده نمونه توسط این ستون یا انتخاب را به عنوان آن را حذف کنید و داده ها را فقط در آن حذف کنید محدوده اختصاصی مهم است که به یاد داشته باشید که اگر محدوده را گسترش ندهید، داده ها تنها در یک ستون تغییر می کنند و داده ها در ستون مجاور بدون کوچکترین تغییرات باقی خواهند ماند.

یک پنجره با پارامترهای حذف تکراری ظاهر می شود

کنه ها را در مقابل آن ستون قرار دهید، تکراری که در آن شما باید حذف کنید و روی OK کلیک کنید. اگر هدرهای داده نیز در محدوده اختصاصی قرار داشته باشند، پس بهتر است پرچم را بسازید داده های من حاوی سرفصل ها هستندبه طور تصادفی داده ها را در جدول حذف نکنید (اگر آنها به طور ناگهانی به طور کامل با ارزش عنوان هماهنگ شوند).

روش 1: فیلتر پیشرفته
در مورد اکسل 2003، همه چیز پیچیده تر است. چنین ابزار مانند وجود ندارد حذف تکراری. اما چنین ابزار فوق العاده ای وجود دارد فیلتر پیشرفته. در سال 2003 این ابزار را می توان یافت داده ها -فیلتر کردن -فیلتر پیشرفته. زیبایی این روش، با آن، شما نمی توانید داده های منبع را خراب کنید، اما برای ایجاد یک لیست در محدوده دیگری. در سال 2007-2010 اکسل، او نیز دارد، اما کمی سخت شده است. واقع در برگه داده ها (داده ها)گروه مرتب سازی و فیلتر (مرتب سازی و فیلتر) - پیشرفته)
نحوه استفاده از آن: اجرای ابزار مشخص شده - یک کادر محاوره ای ظاهر می شود:

• رفتار: انتخاب کنید نتیجه را به مکان دیگری کپی کنید (به مکان دیگری کپی کنید).
• محدوده منبع: محدوده را با داده ها انتخاب کنید (در مورد ما، این A1: A51 است).
• محدوده معیار (معیارهای محدوده): در این مورد، ما خالی را ترک می کنیم.
• نتیجه را در محدوده قرار دهید (کپی به): اولین سلول را به داده های خروجی نشان می دهد - هر خالی (در تصویر - E2).
• یک تیک را بگذارید فقط سوابق منحصر به فرد (فقط سوابق منحصر به فرد).
• ژمم خوب.

توجه داشته باشید: اگر می خواهید نتیجه را بر روی ورق دیگری قرار دهید، فقط مشخص کنید که ورق دیگری کار نخواهد کرد. شما می توانید یک سلول را بر روی ورق دیگری مشخص کنید، اما ... alas و ah ... اکسل پیامی را ارائه می دهد که نمی تواند داده ها را به صفحات دیگر کپی کند. اما این می تواند دور از دسترس باشد، و کاملا ساده است. ما فقط باید اجرا کنیم فیلتر پیشرفته از آن ورق که ما می خواهیم نتیجه را قرار دهیم. و به عنوان داده های منبع، داده ها را از هر ورق انتخاب کنید - مجاز است.

شما همچنین می توانید نتیجه را در سلول های دیگر ایجاد نکنید، اما برای فیلتر کردن اطلاعات در نقطه. داده های این امر تحت تاثیر قرار نخواهد گرفت - فیلتر کردن داده های معمول خواهد بود.

برای انجام این کار، فقط باید مورد پردازش را انتخاب کنید لیست فیلتر در سایت (فیلتر کردن لیست، در محل).

روش 2: فرمول
این روش در درک کاربران بی تجربه دشوارتر است، اما لیستی از مقادیر منحصر به فرد را بدون تغییر داده های اولیه ایجاد می کند. خوب، آن پویا تر است: اگر داده ها را در جدول منبع تغییر دهید، نتیجه تغییر خواهد کرد. گاهی اوقات مفید است من سعی خواهم کرد بر روی انگشتان توضیح دهم و چه چیزی: برای مثال، لیست با داده ها در ستون A واقع شده است (A1: A51، جایی که A1 یک هدر است). نمایش یک لیست ما در ستون با شروع با سلول C2. فرمول C2 به شرح زیر است:
(\u003d index ($ $ 2: $ 51 $؛ کوچکترین (اگر (counted (counted (counted ($ $ 1: c1؛ $ A $ 2: $ $ 51) \u003d 0؛ ردیف ($ 1 $ 1: $ a 50 دلار))؛ 1))
(\u003d index ($ $ 2: $ $ 51؛ کوچک (اگر (countif (countif ($ c $ 1: c1؛ $ A $ 2: $ 51 $) \u003d 0؛ ردیف ($ $ 1: $ $ a $ 50))؛ 1))
تجزیه و تحلیل دقیق از کار این فرمول در مقاله داده شده است:
لازم به ذکر است که این فرمول یک فرمول آرایه است. این را می توان گفت که براکت های فرفری که در آن این فرمول به پایان رسید. و این فرمول به ترکیب سلول معرفی می شود - سیپوش+تغییر مکان.+وارد. پس از وارد شدن به این فرمول در C2، ما باید آن را کپی کنیم و به چندین ردیف وارد شویم تا دقیقا تمام موارد منحصر به فرد را نمایش دهیم. به زودی به عنوان فرمول در سلول های پایین تر بازگشت #عدد! - این بدان معنی است که تمام موارد نمایش داده می شود و در زیر فرمول معنی ندارد. به منظور خطا برای جلوگیری از و ایجاد فرمول جهانی (بدون گسترش هر بار یک خطا رخ می دهد)، شما می توانید از چک غیر صوتی استفاده کنید:
برای اکسل 2007 و بالاتر:
(اگر شاخص (index ($ $ 2: $ $ 51؛ کوچکترین (counted (counted (counted (counted (counted ($ $ 1: c1؛ $ A $ 2: $ $ 51) \u003d 0؛ string ($ a $ 1: $ 50 دلار)؛ 1))؛ ""))
(\u003d IFERError (Index ($ A 2: $ $ 51؛ کوچک (اگر (COUNTIF ($ COUNTIF $ $ 1: C1؛ $ A $ 2: $ 51 $ 51) \u003d 0؛ ردیف ($ $ 1: $ 50 دلار))؛ 1))؛ ""))
برای اکسل 2003:
(\u003d اگر (eosh (eosh (most (most ($ 1 $ 1: c1؛ $ A $ 2: $ $ 51) \u003d 0؛ string ($ $ 1: $ 50 دلار))؛ 1)) ؛ ""؛ شاخص ($ $ 2: $ 51 $؛ کوچکترین (اگر (Countecles ($ C $ 1: C1؛ $ A $ 2: $ $ 51) \u003d 0؛ ردیف ($ 1 $ 1: $ 50 دلار))؛ 1))))
(\u003d اگر (ISERR (iSerR (countif ($ c $ 1: c1؛ $ A $ 2: $ 51 $) \u003d 0؛ ردیف ($ 1 $ 1: $ 50 دلار))؛ 1))؛ "" ؛ شاخص ($ $ 2: $ $ 51؛ کوچک (اگر (countif ($ c $ 1: C1؛ $ A $ 2: $ $ 51) \u003d 0؛ ردیف ($ $ 1: $ 50 دلار )؛ 1))
سپس به جای اشتباه #عدد! (#num!) شما سلول های خالی دارید (البته کاملا خالی نیست، البته - با فرمول :-)).

کمی بیشتر در مورد تفاوت ها و تفاوت های ظریف فرمول فرمول و اگر (EOSH را می توان در این مقاله خواند: همانطور که در یک سلول با فرمول به جای خطا 0

روش 3: کد VBA
این رویکرد نیاز به اجازه ماکروها و دانش پایه ای در مورد کار با آنها دارد. اگر شما در مورد دانش خود مطمئن نیستید، توصیه می کنم این مقالات را بخوانید:

• ماکرو و جایی که به دنبال آن هستید چیست؟ مقاله آموزش تصویری متصل شده است
• یک ماژول چیست؟ ماژول ها چیست؟ لازم است بدانیم کجا کدهای زیر را وارد کنید.

هر دو کد زیر باید قرار داده شود ماژول استاندارد. ماکرو باید مجاز باشد

داده های اولیه به صورت یکسان باقی خواهد ماند - لیست با داده ها در ستون "A" قرار دارد (A1: A51، که در آن A1 - عنوان). فقط یک لیست را برداشتیم، ما در ستون C نخواهیم بود، اما در ستون E2، با شروع از سلول E2:

Sub Extract_Unique () Dim Vitem، Avarr، LI به عنوان طولانی Redim Avarr (1 به ROWS.COUNT، 1 تا 1) با مجموعه جدید در خطا از رزومه بعدی برای هر Vitem در محدوده ("A2"، سلول ها (Rows.Count، 1) .end (xlup)). مقدار "(! lang: سلولها (ردیف.count، 1) .end (XLUP) - آخرین سلول پر شده را در ستون Addd Vitem، CSTR (Vitem) تعیین می کند (Vitem) اگر ERR \u003d 0 سپس Li \u003d Li + 1: Avarr (Li، 1) \u003d ویتم دیگر: Erg.Clear End اگر پایان بعدی با اگر لی پس از آن .Resize (Li) .Value \u003d Avarr End Sub

با استفاده از این کد، شما می توانید منحصر به فرد نه تنها از یک ستون، بلکه از هر طیف وسیعی از ستون ها و خطوط. اگر به جای رشته
دامنه ("A2", Cells(Rows.Count, 1).End(xlUp)).Value !}
انتخاب selection.Value، نتیجه کد کد یک لیست از عناصر منحصر به فرد از محدوده اختصاص داده شده بر روی ورق فعال خواهد بود. فقط پس از آن، می توانید سلول خروجی را تغییر دهید - به جای آن قرار دهید که در آن هیچ اطلاعاتی وجود ندارد.
شما همچنین می توانید محدوده خاصی را مشخص کنید:

محدوده ("C2"، سلول ها (ROWS.COUNT، 3) .end (XLUP)). ارزش

کد جهانی برای انتخاب مقادیر منحصر به فرد
کد زیر را می توان برای هر محدوده استفاده کرد. این به اندازه کافی برای شروع آن است، محدوده را با مقادیر انتخاب تنها غیر تکرار (بیش از یک ستون اختصاص داده شده) و سلول برای خروجی نتیجه را مشخص کنید. سلول های مشخص شده مشاهده خواهند شد، فقط مقادیر منحصر به فرد از آنها انتخاب می شوند (سلول های خالی پرشده می شوند) و لیست نتیجه ثبت می شود، با شروع از سلول مشخص شده.

Sub Extract_Unique () Dim X، Avarr، LI به عنوان Long Dim Avvals Dim Rvals به عنوان محدوده، Rresultcell به عنوان محدوده در خطا در ادامه رزومه بعدی "درخواست آدرس سلول ها را انتخاب کنید تا ارزش های منحصر به فرد را انتخاب کنید تنظیم rvals \u003d application.inputbox ( "محدوده سلول ها را برای نمونه برداری از مقادیر منحصر به فرد مشخص کنید"، "درخواست داده"، "A2: A51"، نوع: \u003d 8) اگر RVALS هیچ چیز پس از آن نیست "اگر دکمه لغو فشار داده شود خروج از زیر پایان اگر "اگر تنها یک سلول مشخص شده باشد - هیچ حس انتخاب نمی کند اگر rvals.count \u003d 1 سپس msgbox "برای انتخاب مقادیر منحصر به فرد شما نیاز به مشخص کردن بیش از یک سلول"، vbinformation، "www.syt" خروج از زیر پایان اگر "برش رشته ها و ستون های خالی خارج از محدوده کاری تنظیم rvals \u003d intersect (rvals، rvals.parent.usedrange) "اگر تنها سلول های خالی خارج از محدوده کاری نشان داده شوند اگر rvals چیزی نیست، msgbox "داده های کافی برای انتخاب مقادیر"، vbinformation، "www.syt" خروج از زیر پایان اگر avvals \u003d rvals.Value "درخواست یک سلول برای خروجی نتیجه تنظیم rresultcell \u003d application.inputbox ( "سلول را مشخص کنید تا مقادیر منحصر به فرد انتخاب شده را وارد کنید"، "درخواست داده"، "E2"، نوع: \u003d 8) اگر rresultcell هیچ چیز پس از آن نیست "اگر دکمه لغو فشار داده شود خروج از زیر پایان اگر "تعیین حداکثر بعد ممکن از آرایه برای نتیجه Redim Avarr (1 به ROWS.COUNT، 1 تا 1) "با استفاده از مجموعه مجموعه (مجموعه) "فقط نوشته های منحصر به فرد را انتخاب کنید، "از آنجا که مجموعه ها نمی توانند حاوی مقادیر مکرر باشند با جمع آوری جدید در خطا بعد از هر X در Avvals در صورت لن (CSTR (X)) پس از آن "ما سلول های خالی را رها می کنیم .add x، cstr (x) "اگر عنصر اضافه شده در حال حاضر در مجموعه است - یک خطا رخ خواهد داد "اگر هیچ خطایی وجود نداشته باشد - این مقدار هنوز ساخته نشده است، "اضافه کردن به آرایه نتیجه اگر err \u003d 0 سپس li \u003d li + 1 avarr (li، 1) \u003d x دیگر "مطمئن شوید که شیء خطا را تمیز کنید Erg.Clear پایان اگر پایان اگر پایان بعدی با "نتیجه را بر روی ورق ضبط کنید، با شروع از سلول مشخص شده اگر Li سپس rresultcell.cells (1، 1) .Resize (Li) .Value \u003d Avarr End Sub

Sub Extract_Unique () Dim X، Avarr، LI به عنوان Dim Dim Avvals Dim Rvals به عنوان محدوده، Rresultcell به عنوان محدوده در خطای رزومه بعدی "درخواست آدرس سلول ها برای انتخاب مجموعه های منحصر به فرد RVALS \u003d application.InputBox (" محدوده سلول ها را مشخص کنید برای نمونه برداری از مقادیر منحصر به فرد "،" درخواست داده ها "،" A2: A51 "، نوع: \u003d 8) اگر RVALS هیچ چیز پس از آن نیست" اگر دکمه خروج از پایان اگر دکمه فشار داده شود، "اگر تنها یک سلول مشخص شده است - آن را هیچ معنایی را انتخاب نمی کنم اگر rvals.count \u003d 1 سپس Msgbox "برای انتخاب مقادیر منحصر به فرد، شما باید بیش از یک سلول را مشخص کنید"، vbinformation، "www.sight" خروج از زیر پایان، اگر "آواز خواندن رشته ها و ستون های خالی خارج از محدوده عملیاتی" تنظیم rvals \u003d intersect \u003d untersect (rvals، rvals.parent.usedrange) "اگر تنها سلول های خالی در خارج از محدوده نشان داده می شود، هیچ چیز پس از آن msgbox نیست" داده های کافی برای انتخاب مقادیر "، vbinformation، www..Value (! lang: درخواست A سلول به خروجی مجموعه rresultcell \u003d application.inputbox ("Укажите ячейку для вставки отобранных уникальных значений", "Запрос данных", "E2", Type:=8) If rResultCell Is Nothing Then "если нажата кнопка Отмена Exit Sub End If "определяем максимально возможную размерность массива для результата ReDim avArr(1 To Rows.Count, 1 To 1) "при помощи объекта Коллекции(Collection) "отбираем только уникальные записи, "т.к. Коллекции не могут содержать повторяющиеся значения With New Collection On Error Resume Next For Each x In avVals If Len(CStr(x)) Then "пропускаем пустые ячейки.Add x, CStr(x) "если добавляемый элемент уже есть в Коллекции - возникнет ошибка "если же ошибки нет - такое значение еще не внесено, "добавляем в результирующий массив If Err = 0 Then li = li + 1 avArr(li, 1) = x Else "обязательно очищаем объект Ошибки Err.Clear End If End If Next End With "записываем результат на лист, начиная с указанной ячейки If li Then rResultCell.Cells(1, 1).Resize(li).Value = avArr End Sub!}

روش 4: جداول خلاصه
چندین روش غیر استاندارد برای استخراج مقادیر منحصر به فرد.

• یک یا چند ستون را در جدول انتخاب کنید، به برگه بروید قرار دادن (قرار دادن) گروه جدول (جدول) -جدول خلاصه (PIVOTTABLE)
• در کادر محاوره ای ایجاد جدول ایجاد Pivottable بررسی صحت محدوده داده ها را بررسی کنید (یا یک منبع داده جدید را نصب کنید)
• قرار دادن جدول تلفیقی را مشخص کنید:
  • در یک ورق جدید (برگه جدید)
  • بر روی ورق موجود (برگه موجود)
• تأیید دکمه را با فشار دادن دکمه تأیید کنید خوب.

زیرا جداول خلاصه هنگام پردازش داده ها که در ناحیه ردیف ها یا ستون ها قرار می گیرند، تنها مقادیر منحصر به فرد از آنها برای تجزیه و تحلیل بعدی گرفته شده است، از ما یک حساب کاربری حتی لازم نیست، به جز ایجاد یک جدول خلاصه و قرار دادن داده های ستون مورد نظر به منطقه یا ستون رشته.
در مثال فایل متصل به مقاله:

ناراحتی کار با کار با هماهنگی در این مورد: هنگام تغییر در داده های منبع، جدول تلفیقی باید به صورت دستی به روز شود: هر سلول جدول تلفیقی را تخصیص دهید - در حالی که دکمه ماوس - تازه کردن) یا برگه داده ها (داده ها) -به روز رسانی همه (تازه کردن همه) -تازه کردن). و اگر داده های منبع به صورت پویا و بدتر به روز شود - لازم است که محدوده داده منبع را دوباره نشان دهید. و یکی دیگر از منهای - داده ها در جدول تلفیقی قابل تغییر نیست. بنابراین، اگر شما نیاز به کار با لیست نتیجه در آینده، پس از ایجاد لیست دلخواه، شما باید آن را با کمک کپی کنید و آن را به ورق دلخواه وارد کنید.

برای درک بهتر تمام اقدامات و یادگیری نحوه رسیدگی به جداول تلفیقی، من به شدت توصیه می کنم خواندن اطلاعات عمومی در مورد جداول خلاصه - آموزش ویدئویی به آن متصل است، که در آن من بصری را نشان می دهد سادگی و راحتی کار با امکانات اصلی جداول تلفیقی

در مثال کاربردی، علاوه بر تکنیک های شرح داده شده، تنوع کمی پیچیده تر از استخراج فرمول عناصر منحصر به فرد و کد ثبت شده است، یعنی: حذف عناصر منحصر به فرد با معیار. سوال این است: اگر در یک ستون از نام خانوادگی، و در دوم (C) برخی از داده ها (در فایل آن ماه ها) و مورد نیاز است برای استخراج مقادیر منحصر به فرد ستون فقط برای نام خانوادگی انتخاب شده . نمونه هایی از استخراج مشابه منحصر به فرد در یک ورق قرار دارند. استخراج بر اساس معیار.

مثال دانلود:

(108.0 KIB، 14 152 دریافت)

مقاله کمک کرد؟ یک لینک با دوستان به اشتراک بگذارید! درس های ویدئویی