Зберегти документ у папці Excel.

Практична робота 12. Заповнення таблиці за допомогою формул

1. Викликати програму Excel.

2. Заповнити таблицю за зразком.

3. Заповнити кожну комірку таблиці формулами сумування трьох чисел.

4. Перше число брать із строки №1

5. Друге число брать із столбца А

6. Третє число поставити 122

7. Наприклад: для комірки В2 повинні сумуватись числа 24, 2 і число 122

8. Зберегти документ у папці Excel. Практична робота 13. Обчислення даних різних форматів

1. Завантажити табличний процесор.

2. На лист 1 скласти відомість закладки продуктів на приготування борщу у військовій частині відповідно до зразка.

• Рядок Всього порахувати за допомогою автосуми відповідних стовпців.

• Щоб порахувати продукти у стовпцях М'ясо, Картопля, Капуста, Буряк використовують формулу наступного типу = Норма продукту (абсолютне посилання) *кількість порцій у відповідній роті / 1000.

• Для результату встановити 1 знак після коми.

3. Створити копію листа з обрахунками та назвати його «Змінена норма».

4. Додати у таблицю норми продуктів стовпчики Морква, Цибуля.

5. Змінити значення норми продуктів та подивитися, що сталося в комірках з обрахунками.

   	Норма продуктів на порцію, г.
   М'ясо	Картопля	Капуста	Буряк	Морква	Цибуля
   93	217	148	62	23	16

6. Додати відповідно стопці Морква та Цибуля у відомість закладки продуктів та виконати обрахунки за цими продуктами, відповідно до попередньої відомості.

7. Аркуш 3 назвати Формула Герона, змінити його ярлик на зелений та виконати розрахунок площі 5 трикутників за формулами (замість літер використовувати відповідні імена комірок): p=(x+y+z)/2 S=КОРЕНЬ(p*(p-x)*(p-y)*(p-z))

8. Створити наступну таблицю згідно зразка.

9. У комірку зі словом дата ввести поточну дату за допомогою комбінації клавіш.

10. У комірці місяця з використанням наступної формули =МЕСЯЦ(адреса комірки з датою) ввести поточний місяць:

11. У відповідні комірки ввести наступні курси валют: долар – 5,8; євро – 6,5; рубль – 0,23.

12. Присвоїти коміркам, де вказані курси валют відповідні імена: Долар, Євро, Рубль.

• Щоб присвоїти ім'я - виділити потрібну комірку (діапазон).

• У рядку формул в полі імені замість адреси комірки ввести потрібне слово і натиснути Enter або обрати меню Вставка – Имя – Присвоить – у поле імені ввести слово та натиснути ОК.

13. Комірці з кількістю робочих днів дати ім'я Робочі дні.

14. Порахувати заробітку плату згідно відпрацьованих днів у гривнях, доларах, євро та рублях з використанням іменованих комірок:

• Гривні = Оклад / кількість робочих днів *Днів

• Долар, Євро, Рубль = Гривні / курс відповідної валюти.

15. Аркуш назвати Імена.

16. Скопіювати таблицю з аркуша Імена на новий аркуш.

17. Змінити кількість робочих днів на 19.

18. Змінити курси валют долар – 4,8; євро – 7,5; рубль – 31.

19. Для стовпців таблиці змінити імена діапазонів відповідно до назв стовпців, наприклад діапазон комірок з прізвищами назвати Прізвища, з Днями – Днів, з окладом - Оклад і т.д.

20. Зберегти документ у папці Excel. Практична робота 14. Використання логічних формул

1. Завантажити табличний процесор.

2. Наберіть таблицю та від форматуйте її відповідно до зразка.

3. Виконайте наступні розрахунки:

1. Підрахувати Вартість товару, що поступив по кожному найменуванню товару.

2. Підрахувати Вартість товару, що продали по кожному найменуванню товару.

3. Визначити Залишок по кожному найменуванню товару.

4. Підрахувати суму всього Залишку.

5. Вивести по Сумі товару, що поступив максимальне значення.

6. Вивести мінімальне значення по Сумі товару, що продали.

4. Скопіювати таблицю на другий лист.

5. Додати у таблицю рядок Середнє значення та визначити середнє значення по стовпчику Кількість товару, що продано за допомогою функцій СРЗНАЧ.

6. На третьому аркуші створити наступну таблицю та виконати відповідні обрахунки:

7. Підрахувати для всіх абітурієнтів середній бал та суму балів.

8. За допомогою логічної функції ЕСЛИ заповнити стовпчик Результат за умовою: якщо середній бал не менше 4,5 – Ура!Поступив!, менше 4,5 – Тю-тю! Пролетів!

   1. обрати функцію ЕСЛИ – у вікні в пункті лог_выражение вказати Середній бал >4,5 – у пункті Значение_если_истина вказати Ура!Поступив! – у пункті Значение_если_ложь вказати Тю-тю! Пролетів! – натиснути ОК.

Практична робота 15. Робота зі складними табличними даними

1. Завантажити табличний процесор.

2. Наберіть таблицю та від форматуйте її відповідно до зразка, збільшить у новій книзі ширину стовпців для повного відображення вихідних даних.

   Направ.	Спеціал.	Ціль	Дата народж.	Вік на даний момент	Обрахунки
   1	Діло	Перекваліфікація	10.05.1976		Мін. вік	?
   2	Бухг	Підвищення кваліфікації	24.06.1989		Макс.вік	?
   3	Англ.	Інша	23.08.1990		До 25 років	?
   3	Скан	Робота за границею	14.12.1987		От 40 років	?
   2	Мен	Підвищення кваліфікації	23.09.1967		Від 25 до 40	?
   2	Бухг	Перекваліфікація	14.03.1983		Серед. вік	?
   1	Рук	Підвищення кваліфікації	25.07.1986
   1	Діло	Нач. обучение	21.10.1990
   1	Рук	Підвищення кваліфікації	25.08.1976
   2	Мен	Нач. обучение	25.09.1990
   1	Діло	Підвищення кваліфікації	12.02.1987
   3	Англ.	Інша	27.09.1987
   3	Нім	Робота за границею	29.10.1987
   1	Діло	Перекваліфікація	25.11.1979
   1	Рук	Підвищення кваліфікації	12.12.1985
   3	Англ.	Підвищення кваліфікації	14.12.1987
   1	Діло	Перекваліфікація	12.09.1984
   2	Мен	Нач. обучение	24.11.1990
   2	Бухг	Нач. обучение	28.04.1990
   1	Рук	Підвищення кваліфікації	15.08.1956
   3	Англ.	Робота за границею	12.12.1987
   3	Скан	Нач. обучение	12.12.1987
   3	Нім	інша	12.10.1972
   1	Рук	Підвищення кваліфікації	12.11.1970

3. Розрахуйте вік слухачів на момент статистичної обробки (формула 1) і занесіть його в стовпець Е.

Для обчислення віку виконується наступні дії: З поточної дати (функція СЕГОДНЯ();категорія функцій: Дата і рік); віднімається дата народження (береться зі стовпця дата народження, в даному випадку з D2); Результат ділиться на середню тривалість року (365 днів); Результат повинен бути цілим числом (ЦЕЛОЕ(), категорія функції: Математичні). Формулу внесіть в комірку Е2, а потім скопіюйте вниз.

(1)=ЦЕЛОЕ((СЕГОДНЯ()-D2)/365)

4. Знайдіть мінімальний (формула 2) і максимальний (формула 3) вік тих, що навчаються (категорія функцій: статистичні):

(2) = МИН(Е2:Е25)

(3)= МАКС(Е2:Е25)

5. Порахуйте кількість слухачів, що навчаються на трьох різних напрямах (формули 4,5,6):

(4) = СЧЕТЕСЛИ(С2:С25; «=1»)

(5) = СЧЕТЕСЛИ(С2:С25; «=2»)

(6) = СЧЕТЕСЛИ(С2:С25; «=3»)

Статистичне спостереження СЧЕТЕСЛИ(Діапазон;умова) підраховує кількість не порожніх комірок в зазначеному діапазоні (аргумент Діапазон), задовольняючих цій умові (аргумент Умова).

6. Підрахуйте кількість слухачів по трьом віковим категоріям: до 25 років; від 25 до 40 років; після 40 років (формули 7,8,9):

(7) = СЧЕТЕСЛИ(Е2:Е25; «<25»)

(8) = СЧЕТЕСЛИ(Е2:Е25; «>39»)

(9) = СЧЕТ(Е2:Е25) – G4 – G5

Кількість слухачів вікової групи від 25 до 40 (формула 9) визначається відніманням з загальної кількості слухачів тих, кому менше 25 і більше 39. Для розрахунку загальної кількості слухачів використовується статистична функція СЧЕТ (список значень), що видає кількість чисел у списку аргументів (список може бути заданий перечисленням або діапазоном, як в даному випадку).

7. Порахуйте середній вік слухачів різних курсів (формула 10):

(10) = СРЗНАЧ (F2:F25)

Для підрахунку використовуйте статистичну функцію СРЗНАЧ (список чисел), обчислюючу середнє арифметичне для вказаних аргументів. Аргументи можуть бути задані перечисленням або діапазоном. Встановіть формати результату Числовий з одним десятковим знаком.

8. Відмітьте в окремому стовпці (Ризик) електронної таблиці «1» тих слухачів, які можуть опинитися в «групі ризику» по працевлаштуванню, і прочерком (знаком «-») – інших слухачів. Для цього використовуйте логічні функції ЕСЛИ, И, ИЛИ, який реалізують поставлену умову вибору.

В «групу ризику» умовно можна віднести слухачів курсів, у яких одночасно виконується дві умови:

• Вік більше 39 років (критичний для працевлаштування);

• Ціль навчання – перекваліфікація або первинне навчання.

Ці умови об’єднуються логічною функцією И (логічна умова 1; логічна умова 2;…).

Друга умова складніша: (ціль – або перекваліфікація, або первинне навчання). Вона реалізується в формулі логічної функції ИЛИ (логічна умова1; логічна умова 2).

Для того, щоб відмітити одним з двох знаків кожного, хто навчається, використовується функція ЕСЛИ (логическое выражение; значение если истина; значение если лож) (формула 11).

(11) = ЕСЛИ(И(Е2>39; ИЛИ (С2 = « перекваліфікація »; С2=”поч. навчання”));1;”-“)

Задаючи таку формулу, ви створюєте логічний фільтр, відбираючи з усіх слухачів тих, хто може мати проблеми з працевлаштуванням.

9. Підрахуйте число слухачів, що входять в «групу ризику», використовуючи математичну функцію СУММ (список чисел) (формула 12):

(12) = СУММ (Н2:Н25)

10. Зберегти документ у папці Excel.

Додаток 2.16

1. Завантажити табличний процесор.

2. Створити матрицю та виконати її множення на число 3:

1. Набрати початкову матрицю (прямокутну таблицю чисел)

2. Виділити діапазон вільних комірок за розміром матриці, та в рядку формул створити формулу = A1:C3*3 і натиснути Ctrl+Shift+Enter.

3. Помножити утворену в результаті обрахунку матрицю на вектор (початкову матрицю).

1. Виділити діапазон вільних комірок за розміром матриці

2. Встановити курсор в рядок формул, обрати функцію МУМНОЖ та у полях Масыв 1 виділити одну матрицю (діапазон) та Масыв 2 – другу матрицю і натиснути Ctrl+Shift+Enter. Формула=МУМНОЖ(A1:C3;E1:G3). (зверніть увагу, що у формулі формула матриці беруться у фігурні дужки)

4. Виконати транспонування матриці, утвореної множенням двох масивів:

1. Виділити діапазон вільних комірок за розміром матриці

2. Встановити курсор в рядок формул, обрати функцію ТРАНСП та у полі Масыв 1 виділити одну матрицю (діапазон) і натиснути Ctrl+Shift+Enter. Формула=ТРАНСП(A1:C3). (Зверніть увагу, що у результаті числа у стовпцях будуть записані в рядок і навпаки).

5. На другому аркуші створити наступні таблиці та виконати розрахунки:

Таблиця категорій, в залежності від балу

7. (формула 1) – відображення поточної дати і часу;

8. (формула 2) – порахувати середній бал студентів;

9. (формула 3) – використовуючи логічну функцію ЕСЛИ визначити категорію кожного учня в залежності від таблиці;

=ЕСЛИ(G2>89;$C$15;ЕСЛИ(80<G2<89;$C$16;ЕСЛИ(70<G2<79;$C$17;ЕСЛИ(60<G2<69;$C$18;$C$19))))

Не забувайте! Кожна вкладена функція ЕСЛИ створюється таким чином: утворити логічний вираз та ввести значение_если_истина, встановити курсор в поле значение_если_ложь та знову обрати функцію ЕСЛИ і повторити заповнення полів.

10. (формула 4) – використати функцію СЧЕТЕСЛИ, де вказати діапазон для обрахунку (стовпчик з даними про категорії без назви стовпчика) та умову (абсолютну адресу комірки з потрібною категорією або категорію з клавіатури)

=СЧЁТЕСЛИ(H2:H10;$C$19) або =СЧЁТЕСЛИ(H2:H10;F)

Обрахунок кількості учнів з 1 групи та кількості учнів 1983 року народження проводиться за відповідною формулою зі зміною умови та діапазону обрахунку, відповідно до даних, що обраховуються.

6. Зберегти документ у папці Excel.

14