Основні теоретичні відомості Дати

Дата в Excel - кількість днів, які пройшли з початку року. Для введення дати, наприклад 1 січня 2004 року, необхідно ввести 1/1/04.

Формула =СЕГОДНЯ() відображає сьогоднішнє число. Якщо накласти на комірку з цією формулою формат Общий, то виведеться кількість днів, які пройшли з початку ХХ століття до сьогоднішнього дня.

Нехай в комірці А1 записана певна дата. Формули =ГОД(А1), =МЕСЯЦ(А1), =ДЕНЬ(А1) повертають відповідно рік, місяць і день цієї дати, але формат комірок з формулами має бути Основной.

Для отримання порядкового номера дня тижня використовується формула =ДЕНЬНЕД(комірка_дати, тип).

Якщо необхідно отримати в комірці текстовий рядок з назвою дня чи місяця, використовується функція ТЕКСТ(значення, форматний рядок). Наприклад, =ТЕКСТ(А1, “ДДД”).

Дві останні цифри року можна отримати по формулі =ТЕКСТ(А1, “ГГ”). Але при цьому отримається текстовий рядок. Щоб отримати число, треба набрати формулу =ЗНАЧЕН(ТЕКСТ(А1, “ГГ”)).

Завдяки тому, що дати являють собою порядкові числа, з ними можна робити обчислення. Можна обчислити, скільки днів пройшло між двома датами, наприклад, =“24/01/99”-“19/12/97”. Можна додавати до дати чи віднімати від неї певну кількість днів.

Є функція для того, щоб “зібрати” дату із року, місяця і дня: ДАТА(рік, місяць, день).

Деякі корисні функції доступні із Пакету аналізу.

Наприклад, функція КОНМЕСЯЦА(початкова_дата, число_місяців) повертає останню дату місяця, що відстоїть на задану кількість місяців від поточної дати.

Функція ЧИСТРАБДНИ(початкова_дата, кінцева_дата, свята) повертає кількість робочих днів між початковою і кінцевою датою включно. Робочими вважаються всі дні, за виключенням вихідних і святкових днів. Найпростіше цією функцією користуватися, якщо її аргументи – імена комірок, останній аргумент – ім’я блоку.

Час

Час вводиться в комірку в форматі чч:мм:сс. Наприклад, 15:56:48. Секунди можна і не вводити. Можна не вводити і хвилини, але двокрапка має бути обов’язково.

Внутрішнє представлення часу – це доля доби. Введемо в комірку 12:, тобто 12 годин. Відобразиться 12:00. Застосуємо формат Общий. В комірці – число 0.5, тобто половина доби.

В комірку можна ввести дату і час. Наприклад, 12/02 15:. В комірці відобразиться 12/02/2003 15:00 (якщо поточний рік 2003). При застосуванні загального формату отримається число, що дорівнює кількості днів від початку століття і долі доби.

Для розрахунку кількості днів між двома датами для 360-денного року використовується функція: ДНЕЙ360(поч_дата, кінц_дата, метод). Перший і другий аргументи – це дати в числовому форматі, але це можуть бути і текстові рядки вигляду “12/2/03”. Третій аргумент може приймати два значення ИСТИНА і ЛОЖЬ (детальніше можна дізнатися з Довідки).

Ще одна функція ДОЛЯГОДА(поч_дата,кінц_дата,базис) повертає частку від ділення кількості днів між поч_дата і кінц_дата на кількість днів у році.

Блоки. Робота з блоками

Нехай на диску є текстовий файл з відомостями про виконання тестів групою абітурієнтів (всього 68 прізвищ):

Прізвище І.П.	Тест 1	Тест 2	Тест 3
Іванов І.І.	20	14	25
Петров П.П.	12	3	18
Сидоров С.С.	13	14	17
...	...	...	...

Потрібно імпортувати цей файл в електронну таблицю. Обчислити сумарну кількість балів для кожного абітурієнта і поставити йому оцінку у відповідності зі шкалою: менше 18 – 2, від 18 до 32 – 3, від 33 до 48 – 4, більше 48 – 5. Відсортувати таблицю за спаданням оцінки, а в межах однакової оцінки – за прізвищами.

За допомогою Майстра текстів імпортуємо текстовий файл, видаляємо другий рядок з пунктирною лінією, виконуємо автопідгонку ширини стовпчиків. В Е1 вводимо “Результат” і обчислюємо суму балів для кожного учасника. Потім в F1 вводимо “Оцінка”, оцінки розрахуємо без використання вкладених функцій ЕСЛИ, а з допомогою функції вертикального перегляду таблиці ВПР.

Для цього на другому аркуші розмістимо довідкову таблицю, в якій в

	А	В
1	0	2
2	18	3
3	33	4
4	49	5

першому стовпчику – дані з граничними значеннями суми балів, а в другому – оцінки. Для зручності назвемо інтервал А1:В4 ім’ям tabl (виділити блок і в полі імені ввести ім’я).

Синтаксис функції ВПР:

ВПР(іскоме_значення,інфо_таблиця,номер_стовпчика,тип_відповідності)

Іскоме_значення – це сума балів із комірки Е2. Це значення функція шукає в інфо_таблиці (tabl). Нехай іскоме значення дорівнює 20. В першому стовпчику інфо_таблиці шукається найбільше значення, що не перевищує іскоме. Це число 18. Якщо номер_стовпчика дорівнює 2, то із таблиці вибирається оцінка 3. Якщо тип_відповідності дорівнює ИСТИНА чи пропущений, то перший стовпчик інфо_таблиці має бути відсортований за зростанням, і в ньому відшукується найбільше значення не більше іскомого. Якщо ж дорівнює ЛОЖЬ або 0 – то перший стовпчик може бути невпорядкованим, а сама функція шукає точну відповідність для іскомого значення.

Вводимо в F2 формулу =ВПР(Е2,tabl,2) і копіюємо її на весь діапазон. Отримуємо стовпчик оцінок. Для його сортування виділяємо будь-яку комірку всередині таблиці, обираємо команду Данные/Сортировка. В першому випадаючому спискові діалогового вікна вказуємо, що сортування ведеться по полю Оцінка за спаданням. В другому спискові – по полю Прізвище за зростанням.

Аналогічно функції ВПР працює і функція горизонтального перегляду ГПР.

Для роботи з блоками часто використовується функція РАНГ(число; посилання;порядок), що повертає ранг числа в списку чисел: його порядковий номер відносно інших чисел в блоці.

Функція ПОИСКПОЗ повертає позицію іскомого значення в масиві. Вона дуже схожа на функцію ВПР. Синтаксис: ПОИСКПОЗ(іскоме_значення, масив, тип_відповідності).

Масив – це блок, що складається із одного стовпчика чи одного рядка. Тип_відповідності – число –1, 0 чи 1.

Тип відповідності	Дії функції	Вимоги до масиву
1	Находить найбільше значення, що менше або дорівнює іскомому.	Масив впорядкований за зростанням.
0	Знаходить перше значення, що дорівнює іскомому.	Масив може бути невпорядкованим.
-1	Знаходить найменше значення, що більше або дорівнює іскомому.	Масив впорядкований за спаданням.

Функція ИНДЕКС(масив,номер_рядка,номер_стовпчика) вибирає із прямокутного блоку (масиву) елемент, що задається номерами рядка і стовпчика, причому ці номери відраховуються від лівого верхнього елемента блоку (фактично функція повертає адресу елемента). Якщо опустити номер_рядка, то функція поверне весь стовпчик із масиву, якщо опустити номер_стовпчика – то поверне весь рядок.

Формули СТРОКА(блок), СТОЛБЕЦ(блок), ЧСТРОК(блок), ЧИСЛСТОЛБ(блок) повертають відповідно номер першого рядка блоку, першого стовпчика блоку, число рядків у блоці і число стовпчиків.