Робота зі складними табличними даними

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

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

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:

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

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

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

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

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

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

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

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

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

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

Якщо бал дорівнює	(формула 1)	Категорія
Більше 89		A
От 80 до 89	B
От 70 до 79	C
От 60 до 69	D
Менше 60	F

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

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

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

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

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

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

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

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

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

Поиск по сайту: