[حل شده] گزارش گرفتن بدون سطرهای خالی

[2009tv]

با سلام و احترام خدمت دوستان و اساتید عزیز که همواره من را راهنمایی نمودند
به پیوست فایلی بعنوان نمونه قرار داده ام که بداین صورت است:
در شیط 1 نمرات 3 دانش آموز قرار دارد و در شیط 2 بر اساس اینکه هر کدام یک شرط (برابر با 20 بودن) را دارند جمله ای برای آنها نشان می دهد اما اگر این شرط را نداشته باشند خالی می ماند حال در گزارشی قصد دارم به ترتیب هر یک از آنها (همان جمله به ازای هر یک) در یک ستون و زیر هم قرار بگیرند (که خودم یه فرمول ساده نوشتم) اما می خواهم اگر مثلاً دومین مورد شرط را نداشت و خالی بود در این گزارش نیاید و سلول خالی نماند و بعدی را درج کند.
در واقع من کمک می خواهم که فرمولی (در محیط اکسل یا vb) را به من عنوان نمایید که موارد خالی در گزارش نیاید. و اگر تمامی 3 موارد خالی بودن فقط متن چهارم در اولین ردیف گزارش نشان داده شود.
ممنون از وقتی که در اختیار من قرار دادید.

Book1.xlsx

[amir_ts]

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

=iferror(index($d$2:$d$5;small(if(not($d$2:$d$5="");row($d$2:$d$5)-1);row(a1)));"") 

[2009tv]

با تشکر از پاسخگویی شما
آیا امکانش هست فرمول دیگری را عنوان نمایید که به ستون دیگری غیر از ستون d وابسته نباشد!(در برخی حالات دیگر این فرمول برای من بدرستی عمل نمی کند)
* در ضمن من می خواهم عبارت آخر(تمامی دانش آموزان نیاز به تلاش بیشتر دارند) فقط در صورتی نشان داده شود که هیچ کدام از دانش آموزان شرط (نمره 20) را نداشته باشند در اولین سطر نشان داده شود و اگر حتی یکی از دانش آموزان شرط (نمره 20) را داشت این عبارت (تمامی دانش آموزان نیاز به تلاش بیشتر دارند) دیگر نشان داده نشود!
** در ضمن در فرمول در قسمت آخر (row(a1)) به A1 و در سطر های بعد به A2 غیره مراجعه می کند این قسمت یعنی از فرمول های نوشته من کمک می گیرد یا فقط یه محدوده عادی است؟
ممنون

[amir_ts]

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

=IFERROR(IF(AND(COUNTIF(Sheet1!B1:B3;"<"&20)=3;ROW()=2);"تمامی دانش آموزان باید برای ممتاز شدن تلاش بیشتری بکنند";INDEX($D$2:$D$5;SMALL(IF(NOT($D$2:$D$5="");ROW($D$2:$D$5)-1);ROW(A1))));"") 

در مورد row(a1) توضیح بدم که این قسمت مربوط به تابع small هست و باید به همین صورت باشه.(عمدا بصورت نسبی آدرس دهی شده است) و ارتیاطی به فرمول شما نداره.

[2009tv]

خیلی ممنونم از پاسخ شما
اما متاسفانه در صورتی که فقط من فرمول شما در سلول B2 را انتخاب کنم و بدون هرگونه تغیییر دکمه Enter را بزنم فرمول هیچ جوابی نشان نمی دهد!
به نظر شما مشکل کار از کجاست!
البته یه مورد عجیب به وجود دارد: در صورتی که فقط بصورت عادی روی سلول B2 کلیک می کنم (تصویر 1) علامت { در ابتدا و انتهای فرمول نشان داده می شود و پس از کلیک در نوار فرمول و به حالت ویرایش درآمدن (تصویر 2) این علامت ها اصلا در فرمول نشان داده نمی شود و پس از زدن اینتر این کاراکتر حذف و فرمول دیگر هیچ چیز نشان نمی دهد!
تصویر 1

تصویر 2

[amir_ts]

دوست عزیز در فرمول های آرایه ای به جای enter باید کلید های ctrl+shift+enter رو با هم فشار بدید که درستی این عمل شما با نمایان شده علامت {} در ابتدا و انتهای فرمول مشخص میشه.اگر بر روی فرمول کلیک کنید باید مجددا کلید های ترکیبی مذکور رو دوباره تکرار کنید.

[2009tv]

سلام دوست و استاد عزیزم
شرمنده شما خیلی راهنمایی کردید اما متاسفانه من نمی تونم از این نوع فرمول آرایه ای استفاده کنم (البته ممنونم که منو با این نوع فرمولها آشنا کردید)
اما کاش میشد از یه فرمول دیگه و غیر آرایه ای استفاده میکردید تا من نیز بهتر بتوانم از آن استفاده کنم!
ممنونم از شما واقعاً سطح من خیلی پایینه!
شما خیلی محبت کردید!

[amir_ts]

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

با سلام
چرا؟ میشه توضیح بدید....اگر مشکل در ایجاد هست بفرمایید تا دقیق توضیح بدم.
بدون استفاده از فرمول های آرایه ای چنین مسایلی( اگر نگم نشدنی) بسیار پیچیده و سخت میشه.

[2009tv]

خیلی ممنونم
من میخوام این فرمول شما را در فایل دیگری استفاده کنم اما با تغیر محدوده درست عمل نمیکنه!
اما اگه فقط مواد مربوط به محدوده را تغییر بدم می توان در هر سلول و فایل دیگری از این فرمول استفاده کنم؟

=IFERROR(IF(AND(COUNTIF(Sheet1!B1:B3;"<"&20)=3;ROW()=2);"تمامی دانش آموزان باید برای ممتاز شدن تلاش بیشتری بکنند";INDEX($D$2:$D$5;SMALL(IF(NOT($D$2:$D$5="");ROW($D​$2  :$D$5)-1);ROW(A1))));"")

امکانش هست کمی برخی از اجزای آن که دانستنش برای استفاده بدون مشکل از فرمول مهم است را برایم توضیح دهید!

[amir_ts]

بدیهی است که با تغییر ساختار و فرم اطلاعات، فرمول ها هم متناسب با اون باید تغییر کنه....
پیشنهاد میکنم ابتدا با توابع small-index-row به صورت عادی آشنا بشید بعد در فرمول های آرایه ای کارکرد اونها رو ملاحظه کنید.
--------------------------------------------------------
شمارش تعداد کل افراد لیست که نمره کمتر از 20 دارند تعداد کل هم که با توجه با اطلاعات لیست مشخص هست.

COUNTIF(Sheet1!B1:B3;"<"&20)=3 

---------------------------------------------------------------------
ROW()=2
اولین ردیف که قرار هست اگر تمام افراد زیر 20 بودن متن رو در ردیف اول بنویسه باید 2 باشه یعنی تو همین ردیف فقط شرط برقرار هست و تو ردیف های دیگه خیر.
وقتی این دو شرط برقرار بود در سلول متن مشخص درج میشه این قسمت برای اینه که فقط در همین ردیف متن نوشته بشه و در ردیف های دیگه تکرار نشه.
-------------------------------------------------------------------
تا اینجا فرمول ها عادی بود حالا اصل فرمول اینجاست:

INDEX($D$2:$D$5 

در تابع index با دادن شماره ردیف و شماره ستون اطلاعات اون قسمت بدست میاد.

SMALL(IF(NOT($D$2:$D$5="");ROW($D$2:$D$5)-1);ROW(A1)) 

تابع small عدد کوچک رو با مشخص کردن چندمین اون برمیگردونه...
مثلا small(a1:a10;1) در محدوده اولین عدد کوچک رو نشون میده یعنی اگربه ترتیب اعداد 8 تا 17 در سلول ها باشه کوچکترین عدد 8 میشه اگر عدد قرمز 4 بود میشه 11

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

IF(NOT($D$2:$D$5="");ROW($D$2:$D$5)-1 

مثلا شماره ردیف های {2و4و6و8و12و15و17و18و23} خالی نیست پس شماره ردیف مشخص میشه حالا اون row(a1) یعنی 1 و خروجی اولین ردیف میشه....
فرمول در ردیف بعدی عینا تکرار میشه ولی به جای row(a1) میشه row(a2) یعنی 2 و به همین ترتیب برای ردیف های دیگه مشخص میشه..
از اونجایی که اطلاعات ما یک ستون بود دیگه احتیاجی به دادن شماره ستون در تابع index نیست.
در آخر چنانچه مشکل داشتید تا هر جایی که تونستید بنویسید و بقیه رو با داده فرضی قرار بدید تا فرمول های متنانسب با اون نوشته بشه.

[iranweld]

با سلام


فایل پیوست را ملاحظه نمایید.

Sub test()

z1 = Sheet1.Cells(Sheet1.Rows.Count, "A").End(xlUp).Row

Z2 = Sheet2.Cells(Sheet2.Rows.Count, "A").End(xlUp).Row + 1

Sheet2.Range("A2:A" & Z2) = ""

K = 2

T = 0

For i = 1 To z1

If Sheet1.Range("b" & i) = 20 Then

Sheet2.Range("A" & K) = Sheet1.Range("A" & i) & " " & ChrW(1583) & ChrW(1575) & ChrW(1606) & ChrW(1588) & ChrW(32) & ChrW(1570) & ChrW(1605) & ChrW(1608) & ChrW(1586) & ChrW(32) & ChrW(1605) & ChrW(1605) & ChrW(1578) & ChrW(1575) & ChrW(1586) & ChrW(1740) & ChrW(32) & ChrW(1575) & ChrW(1587) & ChrW(1578) & ChrW(46)

T = T + 1

K = K + 1

End If

Next

If T = 0 Then

Sheet2.Range("A" & K) = ChrW(1578) & ChrW(1605) & ChrW(1575) & ChrW(1605) & ChrW(1740) & ChrW(32) & ChrW(1583) & ChrW(1575) & ChrW(1606) & ChrW(1588) & ChrW(32) & ChrW(1570) & ChrW(1605) & ChrW(1608) & ChrW(1586) & ChrW(1575) & ChrW(1606) & ChrW(32) & ChrW(1576) & ChrW(1575) & ChrW(1740) & ChrW(1583) & ChrW(32) & ChrW(1576) & ChrW(1585) & ChrW(1575) & ChrW(1740) & ChrW(32) & ChrW(1605) & ChrW(1605) & ChrW(1578) & ChrW(1575) & ChrW(1586) & ChrW(32) & ChrW(1588) & ChrW(1583) & ChrW(1606) & ChrW(32) & ChrW(1578) & ChrW(1604) & ChrW(1575) & ChrW(1588) & ChrW(32) & ChrW(1576) & ChrW(1740) & ChrW(1588) & ChrW(1578) & ChrW(1585) & ChrW(1740) & ChrW(32) & ChrW(1576) & ChrW(1705) & ChrW(1606) & ChrW(1606) & ChrW(1583)

End If

End Sub 

[2009tv]

سلام دوستان و اساتید عزیز
ممنونم که فرمولهایی را در دو حالت آرایه ای و VB ارائه نمودید.
دوست عزیز (amir_ts) آموزش شما خیلی کامل بود، من نمونه ای در یک پروژه انجام دادم که ممنون میشوم لطف نمایید بررسی نمایید که کجای فرمول را من اشتباه وارد نمودم!
متاسفم که اینقدر وقت با ارزش شما را می گیرم.
امیدوارم شما اساتید عزیز که همه دوستان را راهنمایی می کنید در پناه خدا سالم و موفق باشید.

[amir_ts]

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

SMALL(IF(NOT($P$6:$P$9="");ROW($P$6:$P$9)-5)
عدد قرمز کسر ردیف های خالی بالای ستون p هست که برای فایل شما 5 میباشد.

=IFERROR(IF(AND(COUNTIF(Sheet2!B2:B4;"بله")=3;ROW()=11);P9;INDEX($P$6:$P$9;SMALL(IF(NOT($P$6:$P$9="");ROW($P$6:$P$9)-5);ROW(A1))));"") 

[2009tv]

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

[amir_ts]

با سلام
فرمول زیر رو جایگزین کنید.

=IFERROR(IF(AND(COUNTIF(Sheet2!B2:B4;"خیر")=3;ROW()=11);"فرم  شرکت در دوره آمادگی  جسمانی";INDEX($P$6:$P$9;SMALL(IF(NOT($P$6:$P$9="");ROW($P$6:$P$9)-5);ROW(A1))));"") 

SAMPLE1-1