День недели произвольной даты

Для вычисления дня недели любой произвольной даты можно использовать функцию ДЕНЬНЕД. Подготовьте таблицу с датами, отмечающими важнейшие события космической эры (рис. 3). В поле дни недели, используя функцию ДЕНЬНЕД, определите, в какой день недели произошло событие.

Синтаксис функции ДЕНЬНЕД(А;В), где A – дата, для которой определяется день недели. Дату можно вводить обычным порядком. В – тип отсчета дней недели, например, 1 – отсчет дней недели начинается с воскресенья, 2 – отсчет дней недели начинается с понедельника.

Рис. 3. Вычисления дня недели с использованием функции ДЕНЬНЕД

1.4.1. Самостоятельная работа. Определите день недели своего рождения.

2. Excel изображает даты с помощью натуральных чисел от 1 до 65380. Летоисчисление начинается с 1 января 1900 года, которое соответствует 1, и кончается 31 декабря 2078 года (эта дата соответствует числу 65380).

Время считается сложнее, чем дата. Для этого используются дробные числа. Например, час выражается числом 1/24, двенадцать часов 12/24 или 0,5.

Для задания минут используется формула 1/(12*60), для секунд 1/(12*360).

2.1. Заполните таблицу, задав формат ячеек с результатом в формате Время. Введите формулы. Поскольку формат числа, введённого в ячейку, сложный (работа с массивами), то при вводе формулы необходимо нажать сочетание клавиш CTRL+SHIFT+ЕNTER.

Подготовьте таблицу как на рис. 4. В столбце Формула приведены формулы, которые должны записываться в ячейки слева.

		результат	формула
	А	В	Ù Ù Ù
	1 день	8:00
	2 день	9:00
	3 день	3:55
	4 день	4:28
	Простая сумма временных интервалов	1:23	=СУММ(В2:В5)
	Сумма минут		=СУММ(ЗНАЧЕН(ПРАВСИМВ(ТЕКСТ(B1:B4;"чч:мм");2)))
	Сумма часов		=СУММ(ЗНАЧЕН(ЛЕВСИМВ(ТЕКСТ(B1:B4;"чч:мм");ДЛСТР(ТЕКСТ(B1:B4;"чч:мм"))-3)))
	Общая сумма	25:23	=(B7+ЦЕЛОЕ(B6/60))&":"&ТЕКСТ(B6-60*ЦЕЛОЕ(B6:B6/60);"00")

Рис. 4. Таблица данных для работы с датами

 

Введите формулу суммы временных интервалов и вместо ЕNTER нажмите сочетание клавиш CTRL+SHIFT+ЕNTER.

 

2.2. Объединение текста и данных из разных ячеек.

Запишите формулу объединения текста и данных ячеек по образцу, приведённому в таблице на рисунке 5. Запустите формулу, нажав ЕNTER

 

рабочим дням	9.00	21.00
выходным дням	10.00	20.00
=«Посетите наш магазин по «&A1&» c «&B1&» до «&C1&» и по «&A2&» с «&B2&» до «&C2&»»

Рис. 5. Объединение текста, расположенного в разных ячейках

 

Для того, чтобы получилось объявление как на рисунке 6, необходимо скрыть лишние строки и отформатировать текст. Для этого на вкладке Главная в группе Ячейки нажмите кнопку Формат и выберите Видимость/Скрыть или отобразить, а затем Скрыть строки.

Отформатируйте текст, изменив размер и цвет шрифта.

Рис. 6. Результат объединения данных из нескольких ячеек

 

3. Настройка пользовательского формата данных. Создайте таблицу аренды автомобиля (рис. 7):

 

		Счёт от	12.02.2010
марка		дата	время
Волга	с	12.02.10	9:00
	по	22.02.10	14:00
	Итого:
		Всего:
тариф
до 12 часов	45 р.
До 1 дня	120 р.
больше недели	500 р.

Рис. 7. Таблица данных об аренде автомобиля

 

3.1. Создайте формат для ячейки, в которой будут подсчитываться дни аренды: щёлкните правой кнопкой мыши по ячейке и выберите команду Формат ячеек. В открывшемся окне сделайте активной вкладку Число и выберите (все форматы). В поле Тип запишите следующую формулу:

[>=4] 0 "дней";[>=2] 0"дня"; 0 "день",

это будет означать, что в зависимости от срока аренды автомобиля, будет меняться подпись, например 5дней, 3дня, 1день.

Внимание! При записи формулы, определяющей формат ячейки, не должно быть пробелов.

3.2. Вычислите количество дней в ячейке Итого:.

3.3. В ячейку ВСЕГО: запишите формулу, указывая адреса ячеек вместо поясняющих надписей):

= ЕСЛИ(количество дней>=7;сумма больше недели;(ЕСЛИ (количество дней >4/24;сумма до 1 дня;сумма до 4 часов)))

3.4. Проверьте функциональность созданного формата, изменив срок возврата автомобиля, например на 16.02.10

4. Функции для работы с текстом. Текстовые функции используют для преобразования и анализа текстовых значений.

4.1. Преобразование регистра текста. Для преобразования регистра текста используются три функции: ПРОПИСН, ПРОПНАЧ, СТРОЧ.

Функция ПРОПИСН преобразует все буквы в прописные, функция ПРОПНАЧ преобразует в прописные только первую букву каждого слова, а функция СТРОЧ преобразует все буквы в строчные.

Синтаксис всех функций одинаков:

ПРОПИСН(А),

ПРОПНАЧ(А),

СТРОЧ(А), где A – ячейка с преобразуемым текстом.

Примеры использования функций приведены в таблице на рис. 8. В ячейке С1 формула ПРОПИСН(А1), в ячейке С2 формула ПРОПНАЧ(А1), в ячейке С3 формула СТРОЧ(А1).

Рис. 8. Преобразование текста

 

Объединение текста

Для объединения текста из разных ячеек используют функцию СЦЕПИТЬ. Синтаксис функции: СЦЕПИТЬ(А), где A – список от 1 до 30 элементов, текст которых требуется объединить. Элемент может быть ячейкой, текстом или числом. Ссылки на пустые ячейки игнорируются.

Внимание! Нельзя использовать ссылки на диапазоны смежных ячеек.

Подготовьте таблицу как на рисунке 9.

Рис. 9 Таблица данных о студентах

 

В ячейку D2 введите формулу =СЦЕПИТЬ(«Студент»;В2;«»;А2). Текст "Студент " и пробел введены с клавиатуры, остальные данные взяты из ячеек таблицы. Используя функцию автозаполнения, скопируйте формулу из ячейки D2 в ячейки D3:D5. Результат объединения текста показан на рис. 10.

Рис. 10. Объединение текста с использованием функции СЦЕПИТЬ

4.3. Разбивка текста, записанного в одну ячейку, на несколько. Подготовьте таблицу в соответствии с рисунком 11. Разбить текст на несколько колонок позволяет функция ПСТР. В тексте, введённом в ячейку А1, первый пробел находится на 7 позиции, второй – на 12 позиции и последняя буква – на 20 позиции. Чтобы в ячейке В1 была записана фамилия, надо перенести из ячейки А1 все символы до первого пробела: = ПСТР(ячейка, в которой находится текст;позиция первого знака;позиция первого пробела-1), нажмите ОК.В ячейку С1 должно записываться отчество: = ПСТР(ячейка, в которой находится текст;позиция первого пробела+1;позиция второго пробела -позиция первого пробела-1), нажмите ОК. в ячейку D1 надо перенести отчество: = ПСТР(ячейка, в которой находится текст;позиция второго пробела+1;позиция последней буквы), нажмите ОК.

	А	В	С	D
	Иванов Иван Иванович	=ПСТР(А1;1;6)	=ПСТР(А1;8;4)	=ПСТР(А1;13;20)

Рис. 11. Разбивка текста с использованием функции ПСТР

Расчет количества знаков

В некоторых случаях необходимо вычислить количество символов в ячейке. Для этого можно использовать функцию ДЛСТР. Синтаксис функции: ДЛСТР(А), где A – ячейка, в которой требуется вычислить количество символов. Подготовьте таблицу как на рисунке 11.

	А	В
	Меркурий	=ДЛСТР(А1)
	Луна
	Созвездие Девы
	Кассиопея
	Земля

Рис. 11 Вычисление количества символов.

 

Лабораторная работа №13

 

Тема: Макросы