Визначення середнього арифметичного числових значень

4. Із списку функцій даної категорії вибираємо функцію МИН.

5. Натискаємо кнопку ОК.

6. У якості параметрів функції вводимо або виділяємо діапазони комірок В2: В6 і В8 (увесь діапазон розбили на два діапазони, тому що за умовою завдання вартість цукерок " Ромова", записану в комірці В7, ураховувати не треба).

7. Після натискання кнопки ОК у вікні функції в комірці С2 відображається результат.

1.8. Знаходження значення, що задовольняє деякій умові

55. На аркуші будуть записані відомості про зарплату кожного з п'яти співробітників фірми за кожен місяць першого півріччя із зазначенням прізвища працівника й назви місяця. Підготувати аркуш для знаходження:

a) зарплати в червні співробітника, прізвище якого буде вказуватися в одній з комірок (прийняти, що в списку однакових прізвищ немає);

b) зарплати останнього співробітника в місяці, назва якого буде вводитися в одній з комірок.

Розв’язання

1. У зв'язку з відсутністю вихідних даних в умові завдання, заповнюємо таблицю вхідних даних самостійно, наприклад:

2. Установлюємо курсор на комірку D8.

3. Для вибору зарплати зазначеного працівника в червні вводимо в цю комірку формулу: =ВПР(B8; A3: G7; 7) або формулу =ИНДЕКС(A3: G7; ПОИСКПОЗ(B8; A3: A7); 7).

4. Установлюємо курсор на комірку D9.

5. Для вибору зарплати останнього працівника в списку за зазначений місяць у цю комірку вводимо формулу: =ГПР(B9; B2: G7; 6) або формулу =ИНДЕКС(B2: G7; 6; ПОИСКПОЗ(B9; B2: G2)).

2. Використання абсолютної і змішаної адресації комірок

55. Обчислити значення х ², х ⁴, …, х ¹² для значення, що вказується в окремій комірці. Функцію СТЕПЕНЬ й операцію підведення до ступеня не використовувати.

Розв’язання

Кожне наступне парне значення ступеня числа х визначаємо як добуток попереднього значення і квадрата значення х.

1. Для зручності подання даних будуємо таблицю:

2. Установлюємо курсор на комірку С2.

3. Для підведення числа х до квадрата в комірку вводимо формулу: =А2*А2.

4. Для підведення числа х до четвертого ступеня в комірку С3 вводимо формулу: =C2*$C$2.

5. Установлюємо курсор на комірку С3.

6. Використовуємо маркер заповнення або робимо аналогічні дії (пункт 4) для одержання результатів. У стовпці С з’являються шукані значення.

3. Використання декількох функцій

3.1. Перевірка властивостей результатів розрахунків

55. На координатній осі х подано 12 крапок х ₁, х ₂, …, х ₁₂. За допомогою електронної таблиці з'ясувати, чи вірно, що довжина відрізка, якому належать усі ці крапки, більше 25?

Розв’язання

1. У зв'язку з відсутністю вихідних даних в умові завдання, заповнюємо таблицю вхідних даних самостійно, наприклад:

2. Установлюємо курсор на комірку: В2.

3. Для визначення мінімального відрізка, якому належать усі крапки, у комірку записуємо формулу: =МАКС(B1: L1)-МИН(B1: L1).

4. Установлюємо курсор на комірку В3.

5. Входимо в меню Вставка й вибираємо пункт меню Функция.

6. У діалоговому вікні, що відкрилося, вибираємо категорію Логические.

7. Із списку функцій даної категорії вибираємо функцію ЕСЛИ.

8. Натискаємо кнопку ОК.

9. У якості параметрів функції вводимо Лог_выражение: B2> 25, Значение_если_истина: Yes, Значение_если_ложь: No.

10. Після натискання кнопки ОК у вікні функції в комірці B3 відображається відповідь про справедливість наведеного твердження.

3.2. Визначення максимального (мінімального) значення серед чисел, що задовольняють деякій умові

55. Відомо масу й ціну кожного з п'яти моделей стільців. Підготувати аркуш для визначення маси найлегшого стільця вартістю менше 40 гривень.

Розв’язання

1. У зв'язку з відсутністю вихідних даних в умові завдання, заповнюємо таблицю вхідних даних самостійно, наприклад:

Спочатку визначимо масу тих стільців, які дешевше 40 грн., а потім знайдемо мінімальне значення ваги.

2. Установлюємо курсор на комірку С2.

3. Входимо в меню Вставка й вибираємо пункт меню Функция.

4. У діалоговому вікні, що відкрилося, вибираємо категорію Логические.

5. Із списку функцій даної категорії вибираємо функцію ЕСЛИ.

6. Натискаємо кнопку ОК.

7. У якості параметрів функції вводимо Лог_виражение: B2< 40, Значение_если_истина: A2. Для одержання даних про масу стільців інших моделей, вартість яких задовольняє умові, використовуємо маркер заповнення або робимо аналогічні дії (пункти 4-7).

8. Установлюємо курсор на комірку D2.

9. Входимо в меню Вставка й вибираємо пункт меню Функция.

10. У діалоговому вікні, що відкрилося, вибираємо категорію Статистические.

11. Із списку функцій даної категорії вибираємо функцію МИН.

12. Натискаємо кнопку ОК.

13. У якості параметрів функції вводимо або виділяємо діапазон комірок С2: С6.

14. Після натискання кнопки ОК у вікні функції в комірці D2 відображається значення маси найлегшого стільця, вартість якого менше 40 грн.

3.3. Визначення кількості значень, що задовольняють деякій умові

55. Є 10 чисел. Підготувати аркуш для визначення кількості чисел, значення яких більше середнього арифметичного мінімального та максимального числа з заданих.

Розв’язання

Спочатку необхідно визначити мінімальне й максимальне значення з наведених чисел, потім знайти їх середнє значення і нарешті підрахувати скільки з даних чисел більше за це середнє значення. Розв’язання цієї задачі в Excel можливо 3-ма способами. Розглянемо кожен з них.

1-й спосіб:

1. Установлюємо курсор на комірку В2.

2. Входимо в меню Вставка й вибираємо пункт меню Функция.

3. У діалоговому вікні, що відкрилося, вибираємо категорію Математические.

4. Із списку функцій даної категорії вибираємо функцію МАКС.

5. Натискаємо кнопку ОК.

6. У якості параметрів функції вводимо або виділяємо діапазон В1: K1.

7. Після натискання кнопки ОК у вікні функції в комірці В2 відображається значення максимального числа.

8. Установлюємо курсор на комірку В3.

9. Входимо в меню Вставка й вибираємо пункт меню Функция.

10.У діалоговому вікні, що відкрилося, вибираємо категорію Математические.

11.Із списку функцій даної категорії вибираємо функцію МИН.

12.Натискаємо кнопку ОК.

13. У якості параметрів функції вводимо або виділяємо діапазон В1: K1.

14. Після натискання кнопки ОК у вікні функції в комірці В3 відображається значення мінімального числа.

15.Установлюємо курсор на комірку В4.

16.Входимо в меню Вставка й вибираємо пункт меню Функция.

17.У діалоговому вікні, що відкрилося, вибираємо категорію Математические.

18.Із списку функцій даної категорії вибираємо функцію СРЗНАЧ.

19.Натискаємо кнопку ОК.

20.У якості параметра функції вводимо в якості число 1 посилання на комірку В2, у якості число 2 посилання на комірку В3.

21. Після натискання кнопки ОК у вікні функції в комірці В4 відображається середнє значення максимального та мінімального чисел

22.Установлюємо курсор на комірку В5.

23.Входимо в меню Вставка й вибираємо пункт меню Функция.

24.У діалоговому вікні, що відкрилося, вибираємо категорію Логические.

25.Із списку функцій даної категорії вибираємо функцію ЕСЛИ.

26.Натискаємо кнопку ОК.

27. У якості параметрів функції вводимо Лог_виражение: В1> В4, Значение_если_истина: В1, Значение_если_ложь: «».

28.Натискаємо кнопку ОК.

29. Використовуємо поширення формули комірки В5 на комірки В5: K5.

30.Установлюємо курсор на комірку В6.

31.Входимо в меню Вставка й вибираємо пункт меню Функция.

32.У діалоговому вікні, що відкрилося, вибираємо категорію Статистические.

33.Із списку функцій даної категорії вибираємо функцію СЧЕТ.

34.Натискаємо кнопку ОК.

35.У якості параметрів функції вводимо або виділяємо діапазон комірок В5: K5.

36.Після натискання кнопки ОК у вікні функції в комірці В6 відображається значення кількості чисел, які більше за середнє значення мінімального та максимального чисел.

2-й спосіб:

1. Виконуємо дії аналогічні 1-му способу, зазначені в пунктах 1-21.

2. Установлюємо курсор на комірку В7.

3. Входимо в меню Вставка й вибираємо пункт меню Функция.

4. У діалоговому вікні, що відкрилося, вибираємо категорію Статистические.

3-й спосіб:

1. Установлюємо курсор на комірку В8.

2. Входимо в меню Вставка й вибираємо пункт меню Функция.

3. У діалоговому вікні, що відкрилося, вибираємо категорію Статистические.

4. Із списку функцій даної категорії вибираємо функцію СЧЕТЕСЛИ.

5. Натискаємо кнопку ОК.

6. Як параметри функції в якості диапазон: В1: K1, у якості критерий: “> ”& СРЗНАЧ(МАКС(B1: K1); МИН(B1: K1)).

7. Після натискання кнопки ОК у вікні функції в комірці В8 відображається значення кількості чисел, які більше за середнє значення мінімального та максимального чисел.

3.4. Розрахунок суми значень, що задовольняють деякій умові

Вказівка

Завдання даного розділу виконати двома способами:

1) з виконанням відповідних чисел у яких-небудь додаткових комірках;

2) без виведення відповідних чисел у яких-небудь додаткових комірках.

55. Відомо дані про ціну й тираж кожної з п'яти газет міста. Оформити аркуш для визначення вартості тиражу всіх газет, кількість яких становить 1000-3000 примірників.

Розв’язання

1. У зв'язку з відсутністю вихідних даних в умові завдання, заповнюємо таблицю вхідних даних самостійно, наприклад:

2. Установлюємо курсор на комірку D2.

3. Уводимо в неї формулу: =B2*C2.

4. Використовуємо поширення формули комірки D2 накомірки D3: D6.

5. Установлюємо курсор на комірку Е2.

6. Входимо в меню Вставка й вибираємо пункт меню Функция.

7. У діалоговому вікні, що відкрилося, вибираємо категорію Логические.

8. Із списку функцій даної категорії вибираємо функцію ЕСЛИ.

9. Натискаємо кнопку ОК.

10.У якості параметрів функції вводимо Лог_виражение: И(C3> 1000; C3< 3000), Значение_если_истина: D3, Значение_если_ложь: 0

11. Використовуємо поширення формули комірки Е2 на комірки Е3: Е6.

12.Установлюємо курсор на комірку Е7.

13.Входимо в меню Вставка й вибираємо пункт меню Функция.

14.У діалоговому вікні, що відкрилося, вибираємо розділ Математические.

15.Із списку функцій даного розділу вибираємо функцію СУММ.

16.Виділяємо діапазон Е2: Е6.

17.Натискаємо кнопку ОК вікна майстра функцій.

18.У комірці Е7 одержуємо суму вартості всіх газет, тираж яких задовольняє умові.

3.5. Визначення середнього арифметичного значення чисел, що задовольняють деякій умові

Вказівка

Усі завдання даного розділу виконати двома способами:

1) з використанням функції СРЗНАЧ;

2) без використання цієї функції.

55. Підготувати аркуш для визначення середнього арифметичного непарних чисел з десяти заданих чисел.

Розв’язання

1. У зв'язку з відсутністю вихідних даних в умові завдання, заповнюємо таблицю вхідних даних самостійно, наприклад:

Середнє арифметичне чисел знаходиться як їх сума, поділена на їх кількість.

2. Установлюємо курсор на комірку B2.

3. Входимо в меню Вставка й вибираємо пункт меню Функция.

4. У діалоговому вікні, що відкрилося, вибираємо категорію Логические.

5. Із списку функцій даної категорії вибираємо функцію ЕСЛИ.

6. Натискаємо кнопку ОК.

7. У якості функції вводимо Лог_виражение: ОСТАТ(A2; 2) =0, Значение_если_истина: 0. Значение_если_ложь: A2.

8. Використовуємо поширення формули комірки B2 накомірки В3: В11.

9. Установлюємо курсор на комірку В12.

10.Уводимо в неї формулу: =СУММЕСЛИ(B2: B11; " > 0")/СЧЕТЕСЛИ(B2: B11; " > 0")

Результат одержуємо в комірці В12.

Для розв’язання завдання іншим способом з використанням функції СРЗНАЧ необхідно в комірку С2 увести формулу: =ЕСЛИ(НЕЧЕТ(A2)=ИСТИНА; A2), яку поширити на комірки С3: С11, а в комірку С12 увести формулу: СРЗНАЧ(С2: С11). Отриманий результат ідентичний результату першого способу.

3.6. Пошук значення, що відповідає деякому іншому значенню

Вказівка

Шукане значення варто одержати в окремій комірці, поруч із якою має бути виведено відповідне повідомлення.

55. На аркуші записано ряд чисел. Усі числа попарно різні. Підготувати аркуш для визначення номера максимального числа з тих, які не більше 46.

Розв’язання

1. У зв'язку з відсутністю вихідних даних в умові завдання, заповнюємо таблицю вхідних даних самостійно, наприклад:

2. Виділяємо діапазон комірок А2: В6.

3. Входимо в меню Данные й вибираємо в ньому пункт Сортировка...

4. Залишаємо параметри сортування тими ж, що представлені у вікні й натискаємо ОК.

5. Установлюємо курсор на комірку В7.

6. Для визначення номера максимального числа, що не більше 46, уводимо в цю комірку формулу: =ВПР(46; А2: В6; 2)

7. Для виведення відповідного повідомлення, наприклад, «Номер шуканого числа», у комірку А7 установлюємо курсор і вводимо формулу: =ЕСЛИ(B7> 0; " Номер шуканого числа: "). Це повідомлення з'явиться в тому випадку, якщо в ряді вихідних чисел знайдеться хоча б одне число, що не більше 46. Інакше видається помилка: #Н/Д.

3.7. Пошук значень в упорядкованих послідовностях

Вказівка

Усі завдання даного розділу виконати двома способами:

1) без використання функцій МИН і МАКС;

2) з використанням таких функцій.

55. У діапазоні комірок В1: I1 записано числа. Підготувати аркуш для визначення мінімального з тих наведених чисел, які більше 61, 847. Шукане значення одержати в комірці В2.

Розв’язання

1. У зв'язку з відсутністю вихідних даних в умові завдання, заповнюємо таблицю вхідних даних самостійно, наприклад:

2. Установлюємо курсор на комірку В2.

3. Уводимо в цю комірку формулу:

¨ =ИНДЕКС(B1: I1; 1; ПОИСКПОЗ(61, 847; B1: I1; -1)) для спадної послідовності;

¨ =ИНДЕКС(B1: I1; 1; ПОИСКПОЗ(61, 847; B1: I1)+1) для зростаючої послідовності.

2. Установлюємо курсор на комірку В3.

3. Для одержання послідовності чисел, більших 61, 847, уводимо в цю комірку формулу: =ЕСЛИ(B1< 61, 847; ИСТИНА; B1) і поширюємо її на діапазон В1: I1.

4. Установлюємо курсор на комірку В2.

5. Уводимо в цю комірку формулу: =МИН(В1: I1). Отриманий результат ідентичний результату, отриманому першим способом.