منو
خانهمتفرقه

اکسل حداقل مقدار را با شرط پیدا می کند. حداکثر و حداقل بر اساس شرایط در MS EXCEL. نحوه عملکرد انتخاب بر اساس شرایط

موارد خاص

در موارد خاص، راه حل را می توان با شمارش تمام ترکیبات ممکن به دست آورد.

برنامه (C#):

عمومی static int FactGen(int n) ( int fact = new int; fact = 1; for (int i = 1; i< n + 1; i++) fact[i] = i * fact; return fact; } public static int PermsGenerator(int arr, int num, int fact) { int i, j, f, newind, newnum, newval, size = arr.Length; int result = new int; Array.Copy(arr, result, size); for (i = 0, newnum = num; i < size - 1; i++) { newind = newnum / (f = fact); newnum = newnum - newind * f; newval = result; for (j = i + newind; j >من؛ j--) نتیجه[j] = نتیجه; نتیجه[i] = newval; ) نتیجه بازگشت؛ ) عمومی static int SumAbs(int arr) ( int sum = 0, prev = arr; foreach (int value in arr) ( sum += Math.Abs ​​(value - prev); prev = value; ) return sum; ) public static int MaxArr(int arr, bool detprn) ( int i, j, sum, smax = -1, size = arr.Length؛ int perm, result = new int, fact = FactGen(size), sorted = new int, empire = new int;Array.Copy(arr, sorted, size);Array.Sort(sorted);for (i = 0; i)< fact; i++) { if ((sum = SumAbs(perm = PermsGenerator(sorted, i, fact))) >smax) ( smax = مجموع؛ نتیجه = perm؛ ) if (detprn) (Console.Write("\nجایگشت: ")؛ foreach (مقدار int در perm) Console.Write(value + " ")؛ Console.Write(" Sum: (0) Max Sum: (1)", sum, smax؛ ) ) Console.Write("\nانتخاب اولیه:"); foreach (مقدار int در arr) Console.Write(value + " "); Console.Write("\nانتخاب مرتب شده: "); foreach (مقدار int در مرتب شده) Console.Write(value + " "); Console.Write("\nبهترین جایگشت:"); foreach (مقدار int در نتیجه) Console.Write(value + " "); Console.WriteLine("\nحداکثر مقدار: (0)", smax); امپراتوری = مرتب شده; امپراتوری = مرتب شده; امپراتوری = مرتب شده; برای (i = 2، j = 0؛ i< size - 1; i++, j = (size) - 1 + ((i + 1) & 1) - j) empire[i] = sorted[j]; Console.Write("Эвристический алгоритм: "); foreach (int value in empire) Console.Write(value + " "); Console.WriteLine("\nСумма: {0}", SumAbs(empire)); return result; } static void Main(string args) { bool binc; int i, j, n, nmax = 10; int arr; Random rand = new Random(); MaxArr(new int { 1, 2, 3, 4 }, false); MaxArr(new int { 1, 2, 3, 4, 5 }, false); for (n = 6; n < nmax + 1; n++) { arr = new int[n]; for (i = 0; i < n;) { arr[i] = rand.Next(1, 5 * n); binc = true; for (j = 0; j < i; j++) binc &= (arr[i] != arr[j]); if (binc) i++; } MaxArr(arr, false); } }

نتایج:

نمونه اولیه: 1 2 3 4 نمونه مرتب شده: 1 2 3 4 بهترین جایگشت: 2 4 1 3 حداکثر مجموع: 7 اکتشافی: 2 4 1 3 مجموع: 7 نمونه اولیه: 1 2 3 4 5 نمونه مرتب شده: 1 2 3 4 5 بهترین جایگشت: 2 4 1 5 3 حداکثر مجموع: 11 الگوریتم اکتشافی: 2 4 1 5 3 مجموع: 11 نمونه اصلی: 26 10 21 4 27 5 نمونه مرتب شده: 4 5 10 21 26 27 بهترین جایگشت: 10 27 26 حداکثر مجموع: 99 Heuristic: 10 26 4 27 5 21 Sum: 99 نمونه اصلی: 34 3 32 16 28 27 26 نمونه مرتب شده: 3 16 26 27 28 32 34 بهترین جایگشت: 26 28 3 7 342 : 26 28 3 34 16 32 27 مجموع: 97 نمونه اصلی: 27 3 34 38 18 29 31 39 نمونه مرتب شده: 3 18 27 29 31 34 38 39 بهترین جایگشت: 29 3 34 38 8 3 29 34 3 39 18 38 27 31 Sum: 128 نمونه اصلی: 40 27 4 9 32 35 41 39 2 نمونه مرتب شده: 2 4 9 27 32 35 39 40 41 بهترین جایگشت: 32 49 243 223 الگوریتم اکتشافی: 27 35 2 41 4 40 9 39 32 مجموع: 221 نمونه اصلی: 41 35 45 27 34 33 18 24 16 25 نمونه مرتب شده: 16 18 24 25 31 24 5 16 18 24 25 31 27 41 24 45 25 33 حداکثر مجموع: 150 اکتشافی: 27 34 16 45 18 41 24 35 25 33 مجموع: 150

بدین ترتیب:

  1. ترکیب بهتری برای آرایه اصلی پیدا شد.
  2. یک مثال متقابل برای الگوریتم اکتشافی بلافاصله یافت نشد.

تصمیم کلی (10.02.2018)

اجازه دهید
a = (a 0 = M 1، a 1 = M 2، ...، a n-2 = M n-1، a n-1 = M n) - دنباله اولیه،
b = (b 0 , b 1 , …, b n-2 , b n-1 ) - همان دنباله به ترتیب صعودی،
с = (c 0 , c 1 , …, c n-2 , c n-1 ) - دنباله مورد نیاز.

حالات زوج و فرد n را جداگانه در نظر بگیرید.

مورد n = 2k

S(a) = S c (a) - |a n-1 - a 0 |، که در آن
S c a) = |a 0 - a 1 | + |a 1 - a 2 | + … + |a n-3 - a n-2 | + |a n-2 - a n-1 | + |a n-1 - a 0 |.

S c (a) یک مجموع جبری است که هر عنصر اصلی a i را دو بار دارد و حداکثر مقدار آن برابر است با

S c_max = 2∑ i = 0، …، k-1 (h i - b i)، که در آن h i = b i+k، i = 0…k-1.

این مقدار در دو نوع جایگشت به دست می آید:
1) c 2i h (همه عناصر بزرگ دارای شاخص های زوج هستند).
2) c 2i + 1 h (همه عناصر بزرگ دارای شاخص های فرد هستند).

در همان زمان، حداقل |a n-1 - a 0 | = b k - b k-1 با قرار دادن یک جفت عنصر میانه در لبه های دنباله c به دست می آید.

حداکثر مجموع S برابر است با S max = 2∑ i = 0, …, k-2 (b i+k - b i) + b k-1 - b k یا

S max = 2∑ i = 0، …، k-2 (b n-1-i - b i) + b n-k - b k-1،

و در مواردی به دست می آید که دنباله c حاوی بزرگترین عناصر در یک الگوی شطرنجی باشد و عناصر وسط b k-1 و b k در لبه های دنباله باشند.
تعداد چنین جایگشت هایی برای a i به صورت جفتی 2(k-1) است! 2.

مورد n = 2k+1

یک ملاحظه مشابه نشان می دهد که حداکثر S برابر است با

S max = 2∑ i=0…k-2 (b k+2+i - b k) + b k+1 - b k-1 + max (b k+1 - b k , b k - b k-1) یا

S max = 2∑ i=0…k-2 (b n-1-i - b i) + b n-k - b k-1 + max (b k+1 - b k , b k - b k-1)،

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

برنامه (C#):

خالی ثابت عمومی T(متن رشته، تایمر کرونومتر) ( TimeSpan ts = timer.Elapsed; string elapsedTime = String.Format("(0:00):(1:00):(2:00).(3:00) "، ts.Hours، ts.Minutes، ts.Seconds، ts.Milliseconds / 10)؛ Console.Write(text + elapsedTime); ) public static int FactGen(int n) ( int fact = new int; fact = 1; برای (int i = 1; i< n + 1; i++) fact[i] = i * fact; return fact; } public static int PermsGenerator(int arr, int num, int fact) { int i, j, f, newind, newnum, newval, size = arr.Length; int result = new int; Array.Copy(arr, result, size); for (i = 0, newnum = num; i < size - 1; i++) { f = fact; newind = i; while (newnum >= f) ( newind++; newnum -= f; ) newval = نتیجه; برای (j = newind؛ j > i؛ j--) نتیجه[j] = نتیجه; نتیجه[i] = newval; ) نتیجه بازگشت؛ ) عمومی static int SumAbs(int arr) ( int sum = 0, prev = arr; foreach (int value in arr) ( sum += Math.Abs ​​(value - prev); prev = value; ) return sum; ) public static int CalcOptQuant (int n) ( int k = (n - 1) / 2, k2 = Math.Max(k-2, 0)؛ int fact = FactGen(k + 2)؛ بازگشت 2*fact[k] * ( (n-k-k< 2) ? fact : fact[k]); } public static int CalcMaxSum(int brr) { int i, size = brr.Length, k = size / 2, sum = 0; for (i = 0; i < k - 1; i++) sum += brr - brr[i]; sum = 2 * sum + brr - brr; if (size - 2 * k >0) sum += Math.Max(brr - brr[k], brr[k] - brr); بازگشتی ) عمومی static int MaxArr(int arr, bool detprn) (int i, j, sum, smax = -1, size = arr.Length؛ int perm, result = new int, fact = FactGen(size), sorted = int جدید , empire = new int؛ Stopwatch sw = new Stopwatch(); Console.WriteLine("\nانتخاب اولیه: ")؛ foreach (مقدار int در arr) Console.Write(value + " ")؛ sw.Restart(); for (i = 0; i< fact; i++) { perm = PermsGenerator(arr, i, fact); sum = SumAbs(perm); if (sum >smax) ( smax = sum; result = perm; ) ) if (detprn) Console.Write("\nبهترین جایگشت ها"); برای (i = 0، j = 0؛ i< fact; i++) { perm = PermsGenerator(arr, i, fact); sum = SumAbs(perm); if (sum == smax) { j++; if (detprn) { Console.Write("\n#{0}: ", j); foreach (int value in perm) Console.Write(value + " "); } } } Array.Copy(arr, sorted, size); Array.Sort(sorted); Console.WriteLine("\nОтсортированная выборка: "); foreach (int value in sorted) Console.Write(value + " "); Console.Write("\nФакт Наибольшая сумма: {0} Лучших перестановок: {1}" + "\nРасчёт Наибольшая сумма: {2} Лучших перестановок, не менее: {3}", smax, j, CalcMaxSum(sorted), CalcOptQuant(size)); T("\nRuntime = ", sw); sw.Reset(); return result; } static void Main(string args) { bool binc; int i, j, n, nmax = 12; int arr = new int; Random rand = new Random(); MaxArr(new int { 0, 1, 2, 3, 4, 5 }, true); MaxArr(new int { 5, 1, 3, 5, 7, 9 }, true); MaxArr(new int { 0, 1, 2, 3, 4 }, true); MaxArr(new int { 0, 1, 2, 3 }, true); MaxArr(new int { 0, 1, 2 }, true); for (n = 3; n <= nmax; n++) { Array.Resize(ref arr, n); for (i = 0; i < n;) { arr[i] = rand.Next(10, 99); binc = true; for (j = 0; j < i; j++) binc &= (arr[i] != arr[j]); if (binc) i++; } MaxArr(arr, false); } }

نتایج:

نمونه اولیه: 0 1 2 3 4 5 بهترین جایگشت ها # 1: 2 4 0 5 1 3 #2: 2 4 1 5 0 3 #3: 2 5 0 4 1 3 #4: 2 5 1 4 0 3 #5: 3 0 4 1 5 2 #6: 3 0 5 1 4 2 #7: 3 1 4 0 5 2 #8: 3 1 5 0 4 2 نمونه مرتب شده: 0 1 2 3 4 5 واقعیت بالاترین مجموع: 17 بهترین جایگشت: 8 محاسبه بزرگترین مجموع: 17 بهترین جایگشت، دقیقه: 8 زمان اجرا = 00:00:00.01 نمونه اولیه: 5 1 3 5 7 9 بهترین جایگشت ها # 1: 5 1 7 3 9 5 #2: 5 1 9 3 7 5 # 3 : 5 3 7 1 9 5 #4: 5 3 9 1 7 5 #5: 5 7 1 9 3 5 #6: 5 7 3 9 1 5 #7: 5 9 1 7 3 5 #8: 5 9 3 7 1 5 #9: 5 1 7 3 9 5 #10: 5 1 9 3 7 5 #11: 5 3 7 1 9 5 #12: 5 3 9 1 7 5 #13: 5 7 1 9 3 5 #14 : 5 7 3 9 1 5 #15: 5 9 1 7 3 5 #16: 5 9 3 7 1 5 نمونه مرتب شده: 1 3 5 5 7 9 واقعیت بالاترین مجموع: 24 بهترین جایگشت: 16 محاسبه بیشترین مجموع: 24 بهترین جایگشت، حداقل: 8 زمان اجرا = 00:00:00.01 نمونه اولیه: 0 1 2 3 4 بهترین جایگشت ها # 1: 1 3 0 4 2 #2: 1 4 0 3 2 #3: 2 0 4 1 3 #4: 2 1 4 0 3 #5: 2 3 0 4 1 #6: 2 4 0 3 1 #7: 3 0 4 1 2 #8: 3 1 4 0 2 نمونه مرتب شده: 0 1 2 3 4 واقعیت بالاترین مجموع: بهترین 11 جایگشت : 8 محاسبه حداکثر مجموع: 11 بهترین جایگشت، حداقل: 4 زمان اجرا = 00:00:00.01 نمونه اولیه: 0 1 2 3 بهترین جایگشت ها #1: 1 3 0 2 #2: 2 0 3 1 نمونه مرتب شده: 0 1 2 3 واقعیت بزرگترین جمع: 7 بهترین جایگشت: 2 محاسبه بزرگترین مجموع: 7 بهترین جایگشت، حداقل: 2 زمان اجرا = 00:00:00.00 نمونه اولیه: 0 1 2 بهترین جایگشت ها #1: 0 2 1 #2: 1 0 2 # 3: 1 2 0 #4: 2 0 1 نمونه مرتب شده: 0 1 2 واقعیت بالاترین مجموع: 3 بهترین جایگشت: 4 محاسبه بالاترین مجموع: 3 بهترین جایگشت، حداقل: 2 زمان اجرا = 00:00:00.01 نمونه اولیه: 25 51 87 نمونه مرتب شده: 25 51 87 واقعیت بالاترین مجموع: 98 بهترین جایگشت: 2 محاسبه بیشترین مجموع: 98 بهترین جایگشت، حداقل: 2 زمان اجرا = 00:00:00.00 نمونه اولیه: 78 10 34 64 نمونه مرتب شده: 10 38 F بالاترین مجموع: 166 بهترین جایگشت: 2 محاسبه بیشترین مجموع: 166 بهترین جایگشت، حداقل: 2 زمان اجرا = 00:00:00.00 نمونه اولیه: 23 26 93 16 45 نمونه مرتب شده: 16 23 26 45 93 بهترین مجموع در هر 19 بالاترین : 4 محاسبه بزرگترین مجموع: 195 بهترین جایگشت، حداقل: 4 زمان اجرا = 00:00:00.00 نمونه اولیه: 85 12 11 13 90 88 نمونه مرتب شده: 11 12 13 85 88 90 واقعیت بزرگترین جهش 382: بیشترین جهش 382: مجموع: 382 بهترین جایگشت، حداقل: 8 زمان اجرا = 00:00:00.00 نمونه اولیه: 82 43 28 55 29 39 18 نمونه مرتب شده: 18 28 29 39 43 55 82 حقایق بالاترین مجموع محاسبه: 206 بهترین جهش مجموع: 206 بهترین جایگشت، حداقل: 24 زمان اجرا = 00:00:00. 00 نمونه اصلی: 64 20 43 89 47 94 52 71 نمونه مرتب شده: 20 43 47 52 64 71 89 94 00.02 نمونه اصلی: 45 53 81 17 76 97 26 50 517 17 26 50 517 17 00.23 نمونه اصلی: 55 51 36 12 82 63 89 58 14 22 نمونه مرتب شده: 12 14 22 36 51 55 58 63 82 89 زمان اجرا = 00:00:02.46 498 595 6 5 6 5 6 5 6 : 35 49 53 55 65 67 69 78 94 95 96 جایگشت، حداقل: 5760 زمان اجرا = 00:00:29.21 نمونه اولیه: 50 43 39 64 59 18 81 16 16 154 14 18: 16 16 1541 50 59 64 77 81 واقعیت بزرگترین جمع: 463 بهترین جایگشت: 28800 محاسبه بزرگترین مجموع: 463 بهترین جایگشت، حداقل: 28800 زمان اجرا = 00:06:26.81

تجزیه و تحلیل نتایج

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

اکسل تابعی برای تعیین حداقل مقدار دارد. با این حال، یافتن حداقل مقدار بر اساس شرایط مشکل ساز است. یک تابع از افزونه می تواند با این کار کنار بیاید. =مینچسلی

(شبیه به تابع استاندارد Excel SUMIF).

در نسخه اکسل 2016 و در بالا یک عملکرد داخلی وجود دارد MINESLIمی توانید از آن استفاده کنید. اگر اکسل شما یک نسخه قدیمی است، با نصب افزونه می توان از این تابع استفاده کرد VBA-Excel.

تابع دارای آرگومان های زیر است =مینسلی( RANGE;CRITERIA;[SEARCH_RANGE])

    دامنه- محدوده سلول های بررسی شده

  • معیار- شرطی در قالب یک عدد، عبارت یا متن که بررسی حداقل مقدار را مشخص می کند.
  • [ SEARCH_RANGE ]- محدوده واقعی برای تعیین حداقل مقدار. اگر این پارامتر مشخص نشده باشد، از سلول های مشخص شده توسط پارامتر استفاده می شود. دامنه.

مثال 1

به عنوان یک معیار، می توانید مقادیر و عبارات منطقی را مشخص کنید:

  1. مثال زیر را در نظر بگیرید که حداقل امتیاز را در ادبیات تعیین می کند. برای این، در پارامتر معیارمقدار "Literature" و پارامتر مشخص شده است دامنه- لیست اقلام.
  2. اگر یک عبارت منطقی را به عنوان معیار مشخص کنید «<>روسی"، سپس حداقل نمره در تمامی دروس به جز زبان روسی تعیین خواهد شد.

مثال 2

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

اکسل تابعی برای تعیین حداقل مقدار دارد. با این حال، یافتن حداقل مقدار بر اساس شرایط مشکل ساز است. یک تابع از افزونه می تواند با این کار کنار بیاید. =مینچسلی(شبیه به تابع استاندارد Excel SUMIF).

در نسخه اکسل 2016 و در بالا یک عملکرد داخلی وجود دارد MINESLIمی توانید از آن استفاده کنید. اگر اکسل شما یک نسخه قدیمی است، با نصب افزونه می توان از این تابع استفاده کرد VBA-Excel.

تابع دارای آرگومان های زیر است =مینسلی( RANGE;CRITERIA;[SEARCH_RANGE])

    دامنه- محدوده سلول های بررسی شده

  • معیار- شرطی در قالب یک عدد، عبارت یا متن که بررسی حداقل مقدار را مشخص می کند.
  • [ SEARCH_RANGE ]- محدوده واقعی برای تعیین حداقل مقدار. اگر این پارامتر مشخص نشده باشد، از سلول های مشخص شده توسط پارامتر استفاده می شود. دامنه.

مثال 1

به عنوان یک معیار، می توانید مقادیر و عبارات منطقی را مشخص کنید:

  1. مثال زیر را در نظر بگیرید که حداقل امتیاز را در ادبیات تعیین می کند. برای این، در پارامتر معیارمقدار "Literature" و پارامتر مشخص شده است دامنه- لیست اقلام.
  2. اگر یک عبارت منطقی را به عنوان معیار مشخص کنید "<>روسی"، سپس حداقل نمره در تمامی دروس به جز زبان روسی تعیین خواهد شد.

مثال 2

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

با کلیک بر روی دکمه New در نوار ابزار دسترسی سریع، یک فایل جدید ایجاد کنید. تغییر نام برگه 1 به تحلیل هزینه. ما یک جدول برای تجزیه و تحلیل هزینه های سرمایه گذاری ایجاد خواهیم کرد.

شکل 23. تجزیه و تحلیل هزینه های سرمایه گذاری

ورزش: ستون هزینه را با استفاده از فرمول محاسبه کنید. برای ستون‌های قیمت واحد و هزینه، قالب ارز را اعمال کنید.

در سلول F12، کل هزینه سرمایه گذاری را محاسبه کنید. این کار به 2 روش قابل انجام است:

1 راه:

سلول F12 را فعال کنید.

فرمول را در آن وارد کنید: =F5+F6+F7+F8+F9+F10+F11 و Enter را فشار دهید.

2 روش استفاده از تابع:

یک تابع در MS Excel فرمولی است که در آن عملیات ریاضی خاصی با نام توابع جایگزین می شود. به عنوان مثال، برای اضافه کردن چندین مقدار، از تابع SUM استفاده کنید.

بیایید فرمول معمول در سلول F12 را با فرمولی با استفاده از SUM جایگزین کنیم:

ورزش: حداکثر هزینه سرمایه گذاری را بیابید. برای این:

سلولی را که می خواهید نتیجه را در آن بدست آورید فعال کنید (F13).

در سمت راست دکمه Amount در گروه Edit در برگه خانهروی فلش با لیست کشویی توابع کلیک کنید و تابع را انتخاب کنید بیشترین(بزرگترین مقدار را از محدوده انتخاب شده انتخاب می کند)؛

فرمول = MAX(F5:F11) در سلول F13 ظاهر می شود، اما F12 باید از این محدوده حذف شود. نشانگر ماوس را به شکل یک ضربدر پررنگ سفید به وسط اولین سلول در محدوده (F5) حرکت دهید، انتخاب را به سلول F11 بکشید.

Enter را فشار دهید.

ورزش: حداقل و میانگین ارزش هزینه های سرمایه گذاری را بیابید.

تابع حداقل (=MIN)- کوچکترین مقدار محدوده را نشان می دهد.

تابع میانگین (=AVERAGE)- محاسبه مقدار میانگین محدوده، یعنی. همه عناصر را اضافه می کند و بر تعداد عناصر در محدوده جمع تقسیم می کند.

تابع "اگر"

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

بیایید یک ستون "پرداخت" را در جدول اضافه کنیم، که در آن باید مشخص کنید:

اگر هزینه بیش از 45000 روبل باشد. - عقب انداختن؛

اگر هزینه کمتر از 45000 روبل باشد. - "در پرداخت."

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

در کادر محاوره ای، یک دسته را انتخاب کنید بازی فکری، در لیست توابع - اگر.

شکل 24. تابع IF

مقدار به دست آمده در سلول G5 را تا انتهای جدول (به G11) بکشید.

استفاده از شرایط چندگانه هنگام اعمال تابع "IF".

به برگه دوم کتاب کار خود بروید و نام آن را به Salary تغییر دهید. جزئیات زیر را وارد کنید:

شکل 25. مثال محاسبه پاداش حجم فروش

تابع IF از عناصر زیر تشکیل شده است: نام تابع، شرط، مقادیر اگر شرط درست است، مقادیر اگر شرط نادرست است. یک ورودی تابع در یک سلول به شکل زیر است:

اگر (شرط، مقدار زمانی که شرایط درست است، ارزش زمانی که شرایط نادرست هستند)

مثالی را در نظر بگیرید که در آن دو شرط وجود دارد: اگر حجم فروش یک کارمند بیش از 50000 روبل باشد، پاداش 10٪ از حجم فروش و در غیر این صورت 5٪ خواهد بود.

این ورودی برای مثال ما به این شکل خواهد بود:

شکل 26. مثالی از محاسبه حق بیمه حجمی با یک شرط

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

اگر کارمندی کالایی را به مبلغی بیشتر یا مساوی 90000 روبل بفروشد، پاداش او 10% حجم فروش خواهد بود و اگر مقدار کالای فروخته شده بیشتر یا مساوی 30000 روبل باشد، پاداش 5% از مبلغ فروش خواهد بود. حجم فروش، اگر مبلغ کمتر از 30000 روبل باشد، پاداش 3٪ خواهد بود.

شکل 27. مثال محاسبه حق بیمه فروش با شرایط چندگانه

در این حالت چندین شرط وجود دارد و ورودی تابع به این صورت خواهد بود:

قالب بندی شرطی

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

انتخاب محدوده С2:С5;

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

فرمت را تنظیم کنید، اگر مقدار سلول بیش از 30000 باشد، روی OK کلیک کنید.

شکل 28. قالب بندی شرطی

همین عمل را انجام دهید و قالب را برای سلول هایی با مقدار کمتر از 30000 روبل تنظیم کنید.

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

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

محاسبه پرداخت وام

مقدار پرداخت وام را با استفاده از تابع PMT محاسبه کنید.

به برگه 2 بروید و نام آن را به «اعتبار» تغییر دهید. داده های اولیه را برای محاسبه وارد کنید. لطفاً توجه داشته باشید که در سلول B2 - میزان سود سالانه وام نمایش داده می شود. سلول B5 حاوی فرمول است.

شکل 29. محاسبه پرداخت وام

برای محاسبه پرداخت وام ماهانه خود:

    سلول B6 را فعال کنید.

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

شکل 30. درج تابع PMT

در زمینه پیشنهادنرخ ماهانه را وارد کنید، یعنی B2/B4;

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

در زمینه Ps (ارزش فعلی)- لازم است مبلغ وام درخواستی را ذکر کنید.

Сс - موجودی نقدی، که پس از آخرین پرداخت به دست می آید، در مورد ما 0 است.

تایپ کنید- 0 - پرداخت ها در پایان دوره انجام می شود، 1 - پرداخت ها در ابتدای دوره انجام می شود.

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

آدرس دهی مطلق و نسبی

به برگه 3 کتاب بروید و آن را نام ببرید مصرف آب. این قرائت های متر را با ساخت جدول وارد کنید:

شکل 31. مثال جدول

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

مصرف آببه عنوان تفاوت بین قرائت کنتور در ماه جاری و ماه قبل محاسبه می شود:

D9 را فعال کنید، فرمول =B9-B8 را وارد کنید و Enter را فشار دهید.

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

به همین ترتیب محاسبه کنید مصرف آب گرم.

مبلغ قابل پرداختهر HV برای ژانویه به عنوان حاصلضرب مقدار آب مصرف شده توسط هزینه مترمربع محاسبه می شود. فرمول مثال ما این است:

    سلول F9 را فعال کنید.

    فرمول =D9*E4 را وارد کرده و Enter% را فشار دهید

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

به سلول F9 برگردید، ماوس را به گوشه سمت راست پایین سلول ببرید، نشانگر ماوس را به شکل یک ضربدر سیاه بگیرید، دکمه سمت چپ ماوس را نگه دارید، فرمول را به انتهای جدول بکشید.

شکل 32. نیاز به آدرس دهی مطلق

مطمئن شوید که تکمیل خودکار کار نمی کند، بیایید ببینیم چرا:

سلول F9 را فعال کنید، فرمول باید در نوار فرمول ظاهر شود: \u003d D9 * E4.

سلول F10 را فعال کنید. اکسل با استفاده از تکمیل خودکار فرمول را تغییر داد و اکنون مقدار سلول F10 را به صورت =D10*E5 محاسبه می کند. یعنی تمام سلول‌هایی که در فرمول استفاده می‌شوند، زمانی که فرمول با تکمیل خودکار به پایین کپی می‌شود نیز به پایین منتقل می‌شوند. در مورد ما، حرکت به سمت پایین ستون D ضروری است، زیرا برای هر ماه، مقدار متفاوتی از مصرف آب وجود دارد، اما حرکت در امتداد ستون E ضروری نیست، زیرا. ضرب در E4

راه حل:

    به سلول F9 برگردید، در نوار فرمول نگاه کنید: =D9*E4.

    در فرمول، شما باید سلولی را پیدا کنید که در هنگام کپی تغییر نکند. این E4 است.

    در نوار فرمول، مکان نما را در فرمول بین E و 4 قرار دهید. سپس کلید F4 را روی صفحه کلید فشار دهید، فرمول به شکل =D*$E$4 خواهد بود. علائم $ برای اکسل به معنای رفع این سلول یا آدرس دهی مطلق است، در هنگام کپی کردن این فرمول با نقطه تکمیل خودکار، این سلول تغییر نخواهد کرد.

    Enter را فشار دهید، به سلول F9 برگردید و نقطه تکمیل خودکار را به پایین بکشید. سلول F10 را انتخاب کنید - به فرمول توجه کنید.

ورزش: ستون را پر کنید مبلغ قابل پرداخت برای GVبا استفاده از آدرس دهی مطلق و ستون جمع پرداختی- به عنوان مجموع ستون های F و G.

فایل را با یک نام در پوشه خود ذخیره کنید "کارکرد".

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

این امر به ویژه در جداول با مقادیر زیاد داده صادق است. اگر ستون مثلاً نشان دهنده قیمت محصولات مرکز خرید باشد. و باید دریابید که کدام محصول ارزان ترین است. اگر آن را "دستی" جستجو کنید، زمان زیادی می برد. اما در اکسل، این کار را می توان تنها با چند کلیک انجام داد. ابزار همچنین میانگین حسابی را محاسبه می کند. از این گذشته، این دو عملیات ساده هستند: جمع و تقسیم.

حداکثر و حداقل

در اینجا نحوه یافتن حداکثر مقدار در اکسل آمده است:

  1. مکان نما سلول را در هر جایی قرار دهید.
  2. به منوی "Formulas" بروید.
  3. روی Insert Function کلیک کنید.
  4. "MAX" را از لیست انتخاب کنید. یا این کلمه را در قسمت «جستجو» بنویسید و روی «یافتن» کلیک کنید.
  5. در پنجره Arguments آدرس های محدوده ای را که می خواهید حداکثر مقدار آن را بدانید وارد کنید. در اکسل، نام سلول ها از یک حرف و یک عدد ("B1"، "F15"، "W34") تشکیل شده است. و نام محدوده اولین و آخرین سلولی است که در آن گنجانده شده است.
  6. به جای آدرس، می توانید چندین عدد بنویسید. سپس سیستم بزرگترین آنها را نشان خواهد داد.
  7. روی OK کلیک کنید. نتیجه در سلولی که مکان نما قرار داشت ظاهر می شود.

مرحله بعدی تعیین محدوده ای از مقادیر است

اکنون فهمیدن چگونگی یافتن حداقل مقدار در اکسل آسان تر خواهد بود. الگوریتم اقدامات کاملاً یکسان است. به سادگی به جای "MAX" "MIN" را انتخاب کنید.

میانگین

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

در اینجا نحوه یافتن میانگین در اکسل آورده شده است:

  1. مکان نما سلول را در هر فضای خالی در جدول قرار دهید.
  2. به تب "فرمول ها" بروید.
  3. روی "درج تابع" کلیک کنید.
  4. AVERAGE را انتخاب کنید.
  5. اگر این مورد در لیست نیست، با استفاده از گزینه "یافتن" آن را باز کنید.
  6. در قسمت Number1 آدرس محدوده را وارد کنید. یا در فیلدهای مختلف چندین عدد بنویسید «Number2»، «Number3».
  7. روی OK کلیک کنید. مقدار مورد نظر در سلول ظاهر می شود.

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

روش های دیگر

حداکثر، حداقل و میانگین را می توان به روش های دیگری یافت.

  1. نوار تابع با برچسب "Fx" را پیدا کنید. بالای منطقه کار اصلی میز است.
  2. مکان نما را در هر سلولی قرار دهید.
  3. یک آرگومان در قسمت "Fx" وارد کنید. با علامت مساوی شروع می شود. سپس فرمول و آدرس محدوده/سلول می آید.
  4. شما باید چیزی شبیه "=MAX(B8:B11)" (حداکثر)، "=MIN(F7:V11)" (حداقل)، "=AVERAGE(D14:W15)" (میانگین) دریافت کنید.
  5. روی "تیک" کنار فیلد تابع کلیک کنید. یا فقط Enter را فشار دهید. مقدار مورد نظر در سلول انتخاب شده ظاهر می شود.
  6. فرمول را می توان مستقیماً در خود سلول کپی کرد. اثر همان خواهد بود.

ابزار Excel "Autofunctions" به یافتن و محاسبه کمک می کند.

  1. مکان نما را در سلول قرار دهید.
  2. دکمه ای را پیدا کنید که نام آن با "Auto" شروع می شود. این بستگی به گزینه پیش فرض انتخاب شده در اکسل (AutoSum، AutoNumber، AutoOffset، AutoIndex) دارد.
  3. روی فلش سیاه زیر آن کلیک کنید.
  4. MIN (حداقل)، MAX (حداکثر)، یا AVERAGE (متوسط) را انتخاب کنید.
  5. فرمول در سلول مشخص شده ظاهر می شود. روی هر سلول دیگری کلیک کنید - به عملکرد اضافه می شود. جعبه را در اطراف آن بکشید تا محدوده را پوشش دهد. یا روی شبکه Ctrl کلیک کنید تا هر بار یک عنصر را انتخاب کنید.
  6. وقتی کارتان تمام شد، Enter را فشار دهید. نتیجه در یک سلول نمایش داده می شود.

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