Центр Компьютерного Обучения " Специалист ", 2012 Microsoft Excel 2010. Расширенные возможности решение
 Скачать 4.3 Mb.
|
|
Московский Университет ЛЕВСИМВ (Текст;Количество_знаков) – выдает указанное количество символов с начала строки текста. LEFT( Text; Num_chars ) =ЛЕВСИМВ("Специалист";4) → Спец ПРИМЕР: По исходным данным – Фамилия, Имя и Отчество, получить Фамилия И.О. ПРАВСИМВ (Текст;Количество_знаков) – выдает указанное количество символов с конца строки текста. RIGHT( Text;Num_chars ) =ПРАВСИМВ("Специалист";4) → лист ПСТР (Текст;Начальная_позиция;Количество_знаков) – возвращает заданное число символов из строки текста, начиная с указанной позиции. MID( Text;Start_num;Num_chars ) =ПСТР("Специалист";7;3) → лис НАЙТИ (Искомый-текст;Просматриваемый_текст;Нач_позиция) – определяет позицию начала символа (или символов) в указанной текстовой строке с учетом регистра. FIND( Find_text;Within_text;Start_num ) =НАЙТИ("ц";"Специалист";1) → 4 =НАЙТИ("Ц";"Специалист";1) → #ЗНАЧ! Microsoft Excel 2010. Уровень 2. Расширенные возможности Центр Компьютерного обучения «Специалист» www.specialist.ru 31 ПОИСК (Искомый_текст;Текст_для_поиска;Нач_позиция) – определяет позицию первого вхождения символа или строки текста в указанной текстовой строке без учета регистра. SEARCH( Find_text;Within_text;Start_num ) =ПОИСК("Ц";"Специалист";1) → 4 =ПОИСК("ц";"Специалист";1) → 4 ДЛСТР (Текст) – определяет количество символов в текстовой строке. LEN( Text ) =ДЛСТР("Специалист") → 10 ПРИМЕР: Из исходных данных получить символы до # и символы после #. Результатом обработки текстовых функций являются данные с текстовым типом данных. Существует 2 способа преобразовать текстовый аргумент в числовой: воспользоваться функцией ЗНАЧЕН или умножить полученный результат на 1. ЗНАЧЕН (Текст) – преобразует текстовый аргумент в число. VALUE( Text ) ПРИМЕР: Преобразовать результат вычисления текстовой функции в числовой тип данных. ПРАКТИКУМ: Открыть файл 01_6 Текстовые функции. На листах Задание1, Задание2, Задание3, Задание4, Задание5 решить задачи в соответствии с заданиями. Сохранить изменения в файле. Microsoft Excel 2010. Уровень 2. Расширенные возможности www.specialist.ru Центр Компьютерного обучения «Специалист» 32 Разбиение текста по столбцам. Для разбиения текстовых данных по столбцам необходимо, чтобы исходные данные располагались в столбце, и был критерий для разделения данных: символ-разделитель или расположение данных на одинаковом расстоянии. 1. Выделить столбец с исходными данными. 2. На вкладке Данные [Data], в группе в группе Работа с данными [Data Tools] выбрать Текст по столбцам [Text to Columns]. 3. В диалоговом окне Мастер текстов (разбор) – шаг 1 из 3 [Convert Text to Columns Wizard – Step 1 of 3] указать формат исходных данных: с разделителями [Delimited] - содержимое одного столбца от другого отделено знаком (пробел, табуляция, точка с запятой, запятая и др.). фиксированной ширины [Fixed width]: выбираем, если в тексте каждый столбец состоит из одинакового количества символов. 4. На следующем шаге окна Мастер текстов (разбор) – шаг 2 из 3 [Convert Text to Columns Wizard – Step 2 of 3] в зависимости от формата исходных данных, выбранных на шаге 1 работы мастера, сделать следующие настройки: С разделителями – надо выбрать символ-разделитель из предлагаемых или, если нужного нет, то вписать его в поле другой [other]. Фиксированной ширины – щелкать левой кнопкой мыши в области просмотра в месте разделения столбцов. Для удаления разделяющей линии, щелкнуть дважды левой кнопкой мыши по линии. Microsoft Excel 2010. Уровень 2. Расширенные возможности Центр Компьютерного обучения «Специалист» www.specialist.ru 33 Нажать Далее [Next]. 5. На следующем шаге окна Мастер текстов (разбор) – шаг 3 из 3 [Convert Text to Columns Wizard – Step 3 of 3] настроить формат данных для каждого столбца. Для этого выделить столбец в Образце разбора данных [Data preview] и выбрать Формат данных столбца [Column data format]: общий [General] – автоопределение типа данных. текстовый [Text] – значения будет текстовыми данными. дата [Date] – выбрать для дат нужный вариант: ДМГ, ГМД МДГ и т.д., если порядок расположения составляющих даты отличается от используемого. Подробнее [Advanced] – установить Разделитель целой и дробной части [Decimal Separator], а так же Разделитель разрядов [Thousands separator], если они отличаются от ваших региональных стандартов. После завершения импорта, разделители чисел и форматы дат будут заменены на разделители и форматы, используемые в текущих региональных настройках. В поле Поместить в [Destination] указать ячейку для размещения результата разбиения текстовых данных, нажать Готово [Finish]. Microsoft Excel 2010. Уровень 2. Расширенные возможности www.specialist.ru Центр Компьютерного обучения «Специалист» 34 ПРАКТИКУМ: Открыть файл 01_6 Текстовые функции. На листе Задание6 решить задачу в соответствии с заданием. Сохранить изменения в файле и закрыть его. САМОСТОЯТЕЛЬНЫЕ УПРАЖНЕНИЯ Открыть файл УПРАЖНЕНИЯ ЭКСЕЛЬ2. Выполнить задание на листе 6. Функции даты и времени Excel хранит дату в виде последовательных чисел, а время – в виде десятичной части этого значения. Программа может работать с датами, начиная с 1 января 1900 г. Эта дата соответствует положительному числу 1, каждая последующая дата так же соответствует целому положительному числу. Так как значения даты и времени представляются числами, поэтому их можно использовать в вычислениях. Например, чтобы определить длительность мероприятия, можно вычесть из даты окончания мероприятия дату начала мероприятия. СЕГОДНЯ () – вставка текущей даты в формате даты. TODAY() =СЕГОДНЯ() → 25.01.2012 ТДАТА () – вставка текущей даты в формате даты и времени. NOW() =ТДАТА() → 25.01.2012 12:15 У функций СЕГОДНЯ и ТДАТА нет аргументов. Значения даты и времени подставляются из текущих настроек даты и времени операционной системы. Обновление происходит при открытии файла, печати данных и расчете на листе. Для принудительного обновления значений можно нажать клавишу F9 РАБДЕНЬ (Нач_дата;Число_дней;Праздники) – определение даты, отстоящей на заданное число рабочих дней вперед или назад от начальной даты. WORKDAY( Start_date;Days;Holidays ) РАБДЕНЬ(A2;10) → 05.08.2010, где в ячейке A2 дата 22.07.2010 ПРИМЕР: Определить дату изготовления и выдачи заказа, при условии, что для изготовления требуется 7 календарных дней после даты оформления, а дата выдачи возможна через 5 рабочих дней после даты изготовления. Учесть, что 23 февраля (ячейка F1) – красный день календаря. Microsoft Excel 2010. Уровень 2. Расширенные возможности Центр Компьютерного обучения «Специалист» www.specialist.ru 35 ЧИСТРАБДНИ (Нач_дата;Кон_дата;Праздники) – определение полных рабочих дней между двумя указанными датами. NETWORKDAYS( Start_date;End_date;Holidays ) ПРИМЕР: Вычислить длительность проекта в календарных и рабочих днях, учитывая, что 4 ноября (ячейка H1) – красный день календаря. Часто встречаются задачи, в которых необходимо делать вычисления не только с днями, а так же с месяцами и годами. Сперва необходимо дату разобрать на составляющие с помощью функций: ДЕНЬ, МЕСЯЦ и ГОД. Затем к любой составляющей можно прибавить/отнять число, а чтобы получить интересующую дату, воспользоваться функцией ДАТА. ДЕНЬ (Дата_в_числовом_формате) – определяет число месяца – число от 1 до 31. DAY( Serial_number ) =ДЕНЬ(A2) → 22, где в ячейке A2 введена дата 22.07.2010 МЕСЯЦ (Дата_в_числовом_формате) – определяет месяц – число от 1 (январь) до 12 (декабрь). MONTH( Serial_number ) =МЕСЯЦ(A2) → 7, где в ячейке A2 введена дата 22.07.2010 ГОД (Дата_в_числовом_формате) – определяет год – целое число от 1900 до 9999. YEAR( Serial_number ) =ГОД(A2) → 2010, где в ячейке A2 введена дата 22.07.2010 Microsoft Excel 2010. Уровень 2. Расширенные возможности www.specialist.ru Центр Компьютерного обучения «Специалист» 36 ДАТА (Год;Месяц;День) – получение даты по исходным данным, таким как день, месяц и год. DATE( Year;Month;Day ) =ДАТА(2010;7;22) → 22.07.2010 Для решения задач по определению количества лет, можно воспользоваться функцией ДОЛЯГОДА. Для определения полных лет, результат следует обработать функцией ЦЕЛОЕ. ДОЛЯГОДА (Нач_дата;Кон_дата;Базис) – определяет долю году, которую составляет количество дней между начальной и конечной датой. YEARFRAC( Start_date;End_date;Basis ) ПРИМЕР: Определить дату, когда сотруднику исполнится/исполнилось 50 лет. Рассчитать возраст сотрудников (количество полных лет) на текущую дату. ПРАКТИКУМ: Открыть файл 01_7 Функции Даты и Времени. На листах Задание1, Задание2, Задание3, Задание 4 решитьзадачи в соответствии с заданиями Сохранить изменения в файле и закрыть его. САМОСТОЯТЕЛЬНЫЕ УПРАЖНЕНИЯ Открыть файл УПРАЖНЕНИЯ ЭКСЕЛЬ2. Выполнить задание на листе 7. Финансовые функции При работе с финансовыми функциями следует учитывать, что расходы задаются отрицательными значениями (например, вклад в банк, выплата), а доходы – положительными значениями (например, кредит в банке). В Microsoft Excel каждый из финансовых аргументов выражается через другие аргументы. Если процентная ставка не равна 0, то Пс ∙ (𝟏 + Ставка) Кпер + Плт ∙ (𝟏 + Ставка ∙ Тип) ∙ � (𝟏 + Ставка) Кпер − 𝟏 Ставка � + Бс = 𝟎 Если процентная ставка равно 0, то Плт ∙ Кпер + Пс + Бс = 0 Ставка [Range] – процентная ставка за период. Кпер [Nper] – общее число периодов платежей. Плт [Pmt] – выплата, производимая в каждый период. Значение не может меняться в течение всего периода выплат, указывается со знаком «минус». Microsoft Excel 2010. Уровень 2. Расширенные возможности Центр Компьютерного обучения «Специалист» www.specialist.ru 37 Пс [Pv] - начальное значение вклада или сумма кредита. Бс [Fv] – будущая стоимость накоплений или расплата по кредиту. Тип [Type] – значение 0 или 1, обозначающее, когда будет производиться платеж: 0 - в конце периода (значение по умолчанию), 1 - в начале периода. БС (Ставка;Кпер;Плт;Пс;Тип) – вычисляет будущее значение вклада при условии периодических постоянных равных платежей и постоянной процентной ставки. FV( Rate;Nper;Pmt;Pv;Type ) ПРИМЕР: В банке открыли пополняемый вклад с начальной суммой вклада 300 тыс. р. под 9% годовых на 1 год с ежемесячным пополнением в конце месяца на сумму 5 тыс. р. Определить сумму, которая будет на счету по истечению срока вклада. ПЛТ (Ставка;Кпер;Пс;Бс;Тип) – определяет величину периодических равных платежей, необходимых для выплаты ссуды в указанный срок. PMT( Rate;Nper;Pv;Fv;Type ) ПРИМЕР: В банке взят кредит размером 300 тыс. р. на 3 года под 14% годовых. Определить сумму ежемесячного платежа. Microsoft Excel 2010. Уровень 2. Расширенные возможности www.specialist.ru Центр Компьютерного обучения «Специалист» 38 КПЕР (Ставка;Плт;Пс;Бс;Тип) – вычисляет количество периодов, необходимых для выплаты ссуды при условии периодических постоянных платежей по ссуде и постоянной процентной ставки. NPER( Rate;Pmt;Pv;Fv;Type ) ПРИМЕР: В банке планируется взять кредит размером 200 тыс. р под 13% годовых с ежемесячной выплатой 15 тыс. р. Определить количество периодов, за которое кредит будет погашен. ПРАКТИКУМ: Открыть файл 01_8 Финансовые функции. На листах Инвестиции, Платежи, Сроки решить задачи. Сохранить изменения в файле и закрыть его. САМОСТОЯТЕЛЬНЫЕ УПРАЖНЕНИЯ Открыть файл УПРАЖНЕНИЯ ЭКСЕЛЬ2. Выполнить задание на листе 8. Условное форматирование Условное форматирование позволяет легко выделять необходимые ячейки или диапазоны, подчеркивать необычные значения и визуализировать данные с помощью гистограмм, цветовых шкал и наборов значков. К одному диапазону данных может быть применено несколько разных схем оформления. При создании правил условного форматирования можно ссылаться на ячейки других листов. Microsoft Excel 2010. Уровень 2. Расширенные возможности Центр Компьютерного обучения «Специалист» www.specialist.ru 39 Установка условного форматирования 1. Выделить ячейки для форматирования (без заголовков). 2. На вкладке Главная [Home], в группе Стили [Styles], выбрать Условное форматирование [Conditional Formatting]. 3. Задать нужное правило: Правила выделения ячеек (Highlight Cells Rules) – выделение ячеек (цветом заливки, границы, шрифта, начертанием, числовым форматом) по выбранному критерию: Больше, Меньше, Между, Равно, Содержащие текст, Даты (вчера, сегодня, завтра, прошлая неделя, прошлый месяц…), ячейки с одинаковым или уникальным значением, дополнительный выбор: не равно, не содержит, заканчивается… Правила отбора первых и последних значений (Top/Bottom Rules) – форматирование указанного числа крайних значений: N (N%) наибольших, наименьших элементов, Больших, Меньших среднего арифметического Гистограммы (Data Bars) – градиентная или сплошная заливка ячейки, длина которой напрямую зависит от числа, находящегося в ней Цветовые шкалы (Color Scales) – заливка в 2 или 3 цвета, которая напрямую зависит от числа, находящегося в ячейке Наборы значков (Icon Sets) – установка значка, отражающего тенденцию изменения чисел в ячейках. В Excel 2010 доступны дополнительные наборы значков, включая треугольники, звездочки и рамки. Кроме того, можно смешивать и сопоставлять значки из разных наборов и легко скрывать их из вида (например, отображать значки только для высоких показателей и не отображать их для средних и низких значений). Редактирование условного форматирования Можно просматривать, удалять, редактировать все правила условного форматирования выделенного диапазона, листа, книги. 1. На вкладке Главная (Home), в группе Стили (Styles), раскрыть кнопку Условное форматирование (Conditional Formatting) и выбрать команду Управление правилами (Rules Manager). 2. В поле Показать правила форматирования для выбрать область применения правила: текущий фрагмент, этот лист или любой лист этой книги. 3. В списке правил выделить правило: Изменить правило –изменение условия и формата Удалить правило – удаление конкретного правила Microsoft Excel 2010. Уровень 2. Расширенные возможности www.specialist.ru Центр Компьютерного обучения «Специалист» 40 – изменение приоритета выполнения правил (приоритет убывает сверху вниз) Остановить, если истина – включить, если после исполнения этого правила другие правила выполнять не надо В Excel 2010 доступны новые параметры форматирования гистограмм. Можно применять сплошную заливку и границы, а также задавать направление столбцов "справа налево" вместо "слева направо". Кроме того, столбцы для отрицательных значений теперь отображаются с противоположной стороны от оси относительно положительных. При использовании правила Гистограммы в Excel 2010 можно задавать цвет заливки для отрицательных значений, а так же положение оси. Для этого после нажатия кнопки Изменить правило, выбрать Отрицательные значения и ось [Negative values and axis]. Microsoft Excel 2010. Уровень 2. Расширенные возможности Центр Компьютерного обучения «Специалист» www.specialist.ru 41 Создание условия с использованием формулы. Формула используется, если оформляются ячейки одного столбца (например, текст), а условие задается по ячейкам другого столбца (например, числа). 1. Выделить ячейки для оформления. 2. На вкладке Главная (Home), в группе Стили (Styles), раскрыть кнопку Условное форматирование (Conditional Formatting) и выбрать команду Создать правило (New Rule). 3. В появившемся окне выбрать: Использовать формулу для определения форматируемых |