Центр Компьютерного Обучения " Специалист ", 2012 Microsoft Excel 2010. Расширенные возможности решение
 Скачать 4.3 Mb.
|
|
Требования к именам: имя может начинаться с буквы, знака подчеркивания_ или обратного слеша \, затем могут идти буквы, цифры, точки, подчеркивание и обратный слеш \. В имени нельзя использовать пробелы и имя не может совпадать с адресацией ячеек (например, А1 или R1C1). Создание имен 1-й способ: Присвоение имени в поле Имя 1. Выделить ячейку или диапазон ячеек. 2. В поле Имя [Name Box] (слева в строке формул) ввести имя с клавиатуры, нажать Enter Microsoft Excel 2010. Уровень 2. Расширенные возможности Центр Компьютерного обучения «Специалист» www.specialist.ru 9 2-й способ: Присвоение имени в окне Диспетчера имен 1. Выделить ячейку или диапазон ячеек. 2. На вкладке Формулы [Formulas], в группе Определенные имена [Defined Names], выбрать Диспетчер имен [Name Manager] или нажать клавиши Ctrl + F3, , затем Создать [New] 3. В окне Создание имени [New Name]: Ввести Имя [Name]. Определить Область [Scope] действия имени (в пределах листа или книги). Ввести текст в Примечание [Comment] – комментарий при необходимости. Диапазон [Refers to] – область ссылки, тип ссылки, формула. OK. 3-й способ: Из заголовков строк столбцов 1. Выделить диапазон вместе с заголовками. 2. На вкладке Формулы [Formulas], в группе Определенные имена [Defined Names], выбрать Создать из выделенного [Create from Selection] или нажать клавиши Ctrl + Shift + F3 3. Выбрать расположение заголовков относительно данных в выделенном диапазоне, OK. Редактирование имен Для редактирования имени: 1. На вкладке Формулы [Formulas], в группе Определенные имена [Defined Names], выбрать Диспетчер имен [Name Manager] или нажать клавиши Ctrl + F3 2. Выделить имя, выбрать: Изменить [Edit], чтобы изменить имя, диапазон ячеек или область применения. Удалить [Delete] изменить имя или диапазон ячеек. 3. OK. Вставка имен в формулу При написании формулы можно: Щелкнуть по ячейке или выделить диапазон ячеек. Ввести имя с клавиатуры, используя автозавершение формул. Microsoft Excel 2010. Уровень 2. Расширенные возможности www.specialist.ru Центр Компьютерного обучения «Специалист» 10 Нажать клавишу F3 для выбора из списка имен. На вкладке Формулы [Formulas], в группе Определенные имена [Defined Names], выбрать Использовать в формуле [Use in Formula]. ПРАКТИКУМ: Открыть файл 01_1 Вычисления. Выполнить задания на листах Имена и ИменаДиапазонов Сохранить изменения в файле и закрыть его. Microsoft Excel 2010. Уровень 2. Расширенные возможности Центр Компьютерного обучения «Специалист» www.specialist.ru 11 Применение различных типов встроенных функций Кроме ввода формул для выполнения базовых математических операций, таких как сложение, вычитание, умножение и деление, в Microsoft Excel можно использовать большую библиотеку встроенных функций для выполнения других операций. Обращение к функции: ИМЯ_ФУНКЦИИ(аргумент1;аргумент2;…). Имя функции не может содержать пробелы. Аргументы перечисляются через ; (или запятую – зависит от настроек), количество аргументов зависит от функции. Вставка функции. Способ 1. Классический 1. Выделить ячейку для результата. 2. Вызвать мастер функций одним из вариантов: Нажать кнопку в строке формул На вкладке Формулы [Formulas], в группе Библиотека функций [Function Library], выбрать Вставить функцию [Insert Function]. Нажать клавиши Shift + F3 3. Ввести текст для быстрого поиска функции, нажать Найти [Go] или выбрать в списке Категория [Or select a category] нужную категорию функций, затем выбрать нужную функцию в списке Выберите функцию [Select a function], нажать OK. 4. Ввести аргументы функции, нажать OK. Аргументами могут быть числа, адреса других ячеек, вычисляемые выражения и другие функции. Microsoft Excel 2010. Уровень 2. Расширенные возможности www.specialist.ru Центр Компьютерного обучения «Специалист» 12 Способ 2. Современный Для упрощения создания и редактирования формул, удобно использовать автозавершение формул. 1. Выделить ячейку для результата. 2. Ввести с клавиатуры знак = 3. Ввести с клавиатуры первые буквы имени функции. Под ячейкой ввода автоматически отображается динамический список наиболее близких по именам функций. Всплывающие подсказки помогают сделать оптимальный выбор. 4. Выбрать нужную функцию из предлагаемого списка можно: Щелкнуть дважды по подходящей функции. Стрелочками с клавиатуры выделить функцию и нажать клавишу Tab 5. Ввести аргументы функции, используя всплывающие подсказки или вызвать окно Аргументы функции [Function Arguments] – нажать или клавиши Shift + F3 В любой момент, можно вернуться в окно аргументов функции. Для этого выделить ячейку с формулой (если формула состоит из нескольких функций, то щелкнуть в формуле в название нужной функции) и нажать или клавиши Shift + F3 Математические функции Функции округления. Настройка числового формата в окне Формат ячеек [Format Cells] позволяет на экране получить внешний вид значения, округленного по правилам математики до указанного количества десятичных знаков. Однако, в расчетах будет использоваться именно исходное число. Поэтому в результатах последующих вычислений могут возникать погрешности вычислений. К тому же, порой необходимо результат вычисления округлить в большую или меньшую сторону. Для решения таких задач, следует обратить внимание на функции округления. ОКРУГЛ (Число;Число_разрядов) – округляет число до указанного количества десятичных разрядов (по правилам математики). ROUND( Number;Num_digits ) =ОКРУГЛ(755,37;1) → 755,4 Microsoft Excel 2010. Уровень 2. Расширенные возможности Центр Компьютерного обучения «Специалист» www.specialist.ru 13 ОКРУГЛВНИЗ (Число;Число_разрядов) – округляет число до ближайшего меньшего по модулю до указанного количества десятичных разрядов. ROUNDDOWN( Number;Num_digits ) =ОКРУГЛВНИЗ(755,37;1) → 755,3 ОКРУГЛВВЕРХ (Число;Число_разрядов) – округляет число до ближайшего большего по модулю до указанного количества десятичных разрядов. ROUNDUP( Number;Num_digits ) =ОКРУГЛВВЕРХ(755,37;1) → 755,4 ОКРВНИЗ (Число;Точность) – округляет число до ближайшего меньшего по модулю целого, кратному указанному значению. FLOOR( Number;Significance ) =ОКРВНИЗ(755,37;10) → 750 ОКРВВЕРХ (Число;Точность) – округляет число до ближайшего большего по модулю целого, кратному указанному значению. CEILING( Number;Significance ) =ОКРВВЕРХ(755,37;10) → 760 ЦЕЛОЕ (Число) – округляет число до ближайшего меньшего целого. INT( Number ) =ЦЕЛОЕ(755,37) → 755 ПРАКТИКУМ: Открыть файл 01_2 Математические функции. На листе Округление выполнить задание. Сохранить сделанные изменения в файле. Функции сложения. Задачи на суммирование исходных данных встречаются достаточно часто. Можно осуществлять сложение всех числовых аргументов или только значений, которые отвечают заданным критериям. СУММ (Число1;Число2) – суммирует только числовые аргументы. SUM( Number1;Number1 ) Microsoft Excel 2010. Уровень 2. Расширенные возможности www.specialist.ru Центр Компьютерного обучения «Специалист» 14 ПРИМЕР: Определить затраты на Доставку, в которой находится значение месяца Июнь. СУММЕСЛИ (Диапазон;Критерий;Диапазон_суммирования) – суммирует ячейки, заданные указанным условием. SUMIF( Range;Criteria;Sum_range ) Диапазон [Range] – диапазон ячеек, где ответ на критерий (условие). Критерий [Criteria] – критерий отбора (условие). Больше > Меньше < Не более <= Не менее >= Не равно <> Для текстовых значений: ? – замена 1-го символа, * – замена символов Условие сравнения со ссылкой на ячейку: " оператор_сравнения " &Ячейка " <= " &D4, где D4 – ячейка, в которой находится число, дата или время. Диапазон_суммирования [Sum_range] – диапазон суммируемых ячеек. Если Диапазон_суммирования не указан, то будут использоваться ячейки, указанные в поле Диапазон. ПРИМЕР: Рассчитать объем партии продаж Бананов. =СУММЕСЛИ(B:B;"Банан";C:C) – суммирует ячейки из столбца С, если соответствующие им ячейки столбца В содержат слово Банан. Microsoft Excel 2010. Уровень 2. Расширенные возможности Центр Компьютерного обучения «Специалист» www.specialist.ru 15 =СУММЕСЛИ(D:D;">"&F1;C:C) – суммирует ячейки из столбца С, если соответствующие им ячейки в столбце D содержат дату, большую даты из ячейки F1. Функция СУММЕСЛИ позволяет суммировать ячейки только при выполнении одного критерия, если критериев несколько, то нужно использовать функцию СУММЕСЛИМН, которая позволяет суммировать ячейки при 127 условиях максимум. СУММЕСЛИМН (Диапазон_суммирования;Диапазон_условия;Условие;) – суммирует ячейки, удовлетворяющие заданному набору условий. SUMIFS( Sum_ r ange;Criteria_range;Criteria; ) Диапазон_суммирования [Sum_range] – диапазон суммируемых ячеек. Диапазон_условия [Criteria_range] – диапазон ячеек, где ответ на условие (критерий). Условие [Criteria] – условие отбора (критерий). ПРИМЕР: Рассчитать объема партии продаж Бананов, начиная с 10 октября 2010 г. СУММЕСЛИМН(C:C;B:B;"Банан";D:D;">=10.10.2010") – суммирует ячейки из столбца С, если соответствующие им ячейки в столбце B содержат слово Банан, а соответствующие им ячейки в столбце D дату не ранее 10 октября 2010 г. ПРАКТИКУМ: Открыть файл 01_2 Математические функции. На листе Сумма выполнить задание. Сохранить сделанные изменения в файле. САМОСТОЯТЕЛЬНЫЕ УПРАЖНЕНИЯ Открыть файл УПРАЖНЕНИЯ ЭКСЕЛЬ2. Выполнить задание на листе 2. Microsoft Excel 2010. Уровень 2. Расширенные возможности www.specialist.ru Центр Компьютерного обучения «Специалист» 16 Статистические функции Применение статистических функций облегчает пользователю осуществить статистический анализ данных. При исследовании исходных данных, можно легко определить минимальное или максимальное значения, вычислить среднее арифметическое или подсчитать количество значений, в том числе и отвечающих заданным условиям отбора. МИН (Число1;Число2;) – вычисление наименьшего значения из списка аргументов, логические и текстовые значения игнорируются. MIN( Number1;Number2; ) =МИН(120;50;100) → 50 МАКС (Число1;Число2;) – вычисление наибольшего значения из списка аргументов, логические и текстовые значения игнорируются. MAX( Number1;Number2; ) =МАКС(120;50;100) → 120 СРЗНАЧ (Число1;Число2;) – определение среднего арифметического своих аргументов, которые могут быть числами, именами или ссылками на ячейки с числами. AVERAGE( Number1;Number2; ) =СРЗНАЧ(120;50;100) → 90 СЧЁТ (Значение1;Значение2;) – подсчитывает количество ячеек в диапазоне, которые содержат числа. COUNT( Value1;Value2; ) =СЧЁТ(70;50;100;«масса») → 3 СЧЁТЗ (Значение1;Значение2;) – подсчитывает количество непустых ячеек в указанном диапазоне. COUNTA( Value1;Value2; ) =СЧЁТЗ(70;50;100;«масса») → 4 ПРАКТИКУМ: Открыть файл 01_3 Статистические функции. На листе Задание1 выполнить задание. Сохранить сделанные изменения в файле. Microsoft Excel 2010. Уровень 2. Расширенные возможности Центр Компьютерного обучения «Специалист» www.specialist.ru 17 СЧЁТЕСЛИ (Диапазон;Критерий) – подсчитывает количество ячеек в диапазоне, удовлетворяющих заданному условию. COUNTIF( Range; Criteria ) Диапазон [Range] – диапазон ячеек, где ответ на критерий (условие). Критерий [Criteria] – критерий отбора (условие). СЧЁТЕСЛИ(B:B;"Банан")– количество ячеек в столбце В, содержащих слово Банан. СЧЁТЕСЛИ(D:D;">13.10.2010") – количество ячеек в столбце D с датой продажи после 13.10.2010. Функция СЧЁТЕСЛИ подсчитывает количество ячеек только при выполнении одного критерия, если критериев несколько, то нужно использовать функцию СЧЁТЕСЛИМН. СЧЁТЕСЛИМН (Диапазон_условия;Условие;) – подсчитывает количество ячеек в диапазоне, удовлетворяющих заданному набору условий. COUNTIF( Criteria_range;Criteria; ) Диапазон_условия [Criteria_range] – диапазон ячеек, где ответ на условие (критерий). Условие [Criteria] – условие отбора (критерий). ПРИМЕР: Рассчитать количество продаж Бананов, начиная с 10 октября 2010 г. Microsoft Excel 2010. Уровень 2. Расширенные возможности www.specialist.ru Центр Компьютерного обучения «Специалист» 18 СЧЁТЕСЛИМН(B:B;"Банан";D:D;">=10.10.2010") – подсчитывает количество записей в таблице, если в соответствующей ячейке столбца B содержится слово Банан, а в соответствующей ячейке столбца D содержится дата не ранее 10 октября 2010 г. СРЗНАЧЕСЛИ (Диапазон;Условие;Диапазон_усреднения) – подсчитывает среднее арифметическое для ячеек, удовлетворяющих заданному условию. AVERAGEIF( Range;Criteria;Average_range ) Диапазон [Range] – диапазон ячеек, где ответ на условие (критерий). Условие [Criteria] – условие отбора (критерий). Диапазон_усреднения [Average_range] – диапазон ячеек для расчета среднего значения. Если Диапазон_усреднения не указан, то будут использоваться ячейки, указанные в поле Диапазон. ПРИМЕР: Рассчитать средний объем партии продаж Бананов. СРЗНАЧЕСЛИ(B:B;"Банан";C:C) – рассчитывает среднее значение по ячейкам столбца С, если соответствующие им ячейки столбца В содержат слово Банан. СРЗНАЧЕСЛИМН (Диапазон_усреднения;Диапазон_условия;Условие;) – подсчитывает среднее арифметическое для ячеек, удовлетворяющих заданному набору условий. AVERAGEIFS( Average_range;Criteria_range;Criteria; ) Диапазон_усреднения [Average_range] – диапазон ячеек для расчета среднего значения. Диапазон_условия [Criteria_range] – диапазон ячеек, где ответ на условие (критерий). Условие [Criteria] – условие отбора (критерий). Microsoft Excel 2010. Уровень 2. Расширенные возможности Центр Компьютерного обучения «Специалист» www.specialist.ru 19 ПРИМЕР: Рассчитать среднее значение объема партии продаж Бананов, начиная с 10 октября 2010 г. СРЗНАЧЕСЛИМН(C:C;B:B;"Банан";D:D;">=10.10.2010") – рассчитывает среднее значение по ячейкам столбца С, если соответствующие им ячейки столбца В содержат слово Банан, а соответствующие им ячейки в столбце D дату не ранее 10 октября 2010 г. ПРАКТИКУМ: Открыть файл 01_3 Статистические функции. На листе Задание2 выполнить задание. Сохранить сделанные изменения в файле и закрыть его. САМОСТОЯТЕЛЬНЫЕ УПРАЖНЕНИЯ Открыть файл УПРАЖНЕНИЯ ЭКСЕЛЬ2. Выполнить задание на листах 3-1 и 3-2. Функции ссылок и подстановки При работе с большими списками (таблицами) для быстрого получения отдельных записей из этих списков можно использовать функции подстановок. Функции поиска используются для поиска связанных записей в таблицах. При использовании таких функций задача, по существу, формулируется следующим образом – есть значение, для которого нужно найти совпадение в другой таблице и получить в ответ значение, которое хранится в ячейке, соответствующей строки или столбца этой другой таблицы. Основное применение этих функций – это подставлять данные, осуществлять сравнение двух таблиц. Рассмотрим функции: ВПР, ГПР, ПОИСКПОЗ и ИНДЕКС. Их использование зависит от расположения исходных данных в таблицах, из которых осуществляется подстановка. |