 |
Работа с формулами. Использование функций
|
|
|
|
Формулы составляют основу вычислений в Excel. Они могут выполнять как простые действия (сложение, вычитание, умножение и деление), так и сложные вычисления. С помощью формул можно также работать с текстом. После ввода формулы в ячейку на рабочем листе сразу появляется результат вычисления. Для того чтобы посмотреть саму формулу, необходимо выделить ячейку и просмотреть формулу в строке формул.
Формулы в Excel можно ввести как в строке формул, так и непосредственно в ячейке. Формула должна начинаться со знака равенства (=), за которым следует набор вычисляемых величин. Формулы могут ссылаться на ячейки или на диапазоны ячеек не только данного листа, но и другой книги.
Для задания диапазона ячеек служит символ ": " (двоеточие). Например, для того чтобы указать диапазон ячеек от A3 до В5, достаточно написать АЗ:В5. А чтобы указать все строки данной колонки, можно задать диапазон и без указания номеров строк. Например, все строки колонки F - (F:F). Аналогично, если надо указать все колонки данной строки, достаточно задать диапазон без указания номеров колонок. Например, все колонки строки 5 -(5:5).
Для обращения к ячейкам другого листа необходимо указать имя листа и восклицательный знак (!), а затем диапазон ячеек.
Для обращения к ячейкам другой книги сначала указывается имя книги в квадратных скобках.
Адреса ячеек. В Excel указать положение ячейки можно с помощью относительной или абсолютной ссылки. По умолчанию для указания адресов ячеек используются относительные ссылки. Это означает, что при копировании формулы на новое место ссылки на ячейки изменяются относительно перемещения.
В следующем примере ячейка В5 (рис. 2.3) содержит формулу =А5+А6.
|
|
|
Рис. 2.3. Ячейка с формулой
При копировании формулы из ячейки В5 в ячейку В6 в ячейке В6 будет содержаться формула =А6+А7.
Чтобы ссылки не изменялись при копировании формулы в другую ячейку, необходимо использовать абсолютные ссылки (рис. 2.4).
Для создания абсолютной ссылки на ячейку используется знак доллара ($) перед той частью, которая не должна изменяться. Например, при копировании ячейки А5:
$А5 Не изменяется ссылка на столбец;
$А$5 Не изменяется ссылка на столбец и строку;
А$5 Не изменяется ссылка на строку.
Другой способ задания абсолютной ссылки заключается в назначении имен ячейкам и использовании их в формулах. Имя ячейки должно начинаться с буквы и не должно содержать пробелов.
Присвоить имя ячейке можно, используя поле Имя в строке формул. Для этого необходимо выделить ячейку, блок ячеек, строку (столбец) и щелкнуть по полю Имя в строке формул. Ввести в поле Имя нужное имя и нажать клавишу < Enter >.
Для того чтобы удалить имена ячеек, необходимо из меню Вставка выбрать команду Имя, а затем Присвоить. В диалоговом окне в списке Имя выбрать требуемое имя и нажать кнопку Удалить.
Рис. 2.4. Использование абсолютных ссылок
Функции Excel. В Excel используются встроенные функции для выполнения математических, текстовых и логических операций, а также для поиска информации на рабочем листе. Функции получают информацию, передаваемую в качестве аргументов, и возвращают результат. Аргументы заключаются в скобки и располагаются после имени функции.
Функции можно ввести либо в строку формул, либо через диалог, называемый Мастером функций. Для этого надо выбрать из меню Вставка команду Функция или воспользоваться кнопкой Вставка функ ции на панели инструментов.
В простейшем случае все сводится к двум шагам. На первом шаге надо выбрать функцию из предложенного перечня, а на втором ввести необходимые аргументы. После выбора функции под строкой формул появляется панель формул, в которой нужно заполнить все поля аргументов.
|
|
|
В панели формул под полями аргументов приведено краткое описание выбранной функции. Для более подробного знакомства с данной функцией необходимо нажать кнопку справки, которая находится в левом нижнем углу данной панели.
В более сложных случаях в качестве аргументов могут применяться другие функции, которые можно ввести вручную с клавиатуры или выбрать требуемую функцию в поле Функции. Excel контролирует правильность ввода аргументов.
Наиболее часто приходится применять функцию суммирования значений, поэтому эта функция выведена на стандартную панель инструментов как кнопка Автосумма.
Для того чтобы подсчитать сумму значений ячеек рабочего листа, необходимо:
1) активизировать ячейку, куда нужно поместить результат;
2) нажать кнопку Автосумма на панели инструментов;
3) выделить диапазон ячеек, значения которых необходимо просуммировать, и нажать кнопку Ввод на строке формул или клавишу < Enter >.
ДИАГРАММЫ
Excel позволяет улучшить наглядность представления данных с помощью различных диаграмм. Создать диаграмму можно с помощью Мастера диаграмм. Для этого необходимо выделить ячейки, на основе которых будет строиться диаграмма, а затем нажать кнопку Мастер диаграмм на панели инструментов или выбрать из меню Вставка команду Диаграмма. После этого на экране появится диалог Мастера диаграмм, состоящий из четырех шагов.
На первом шаге можно выбрать тип будущей диаграммы.
На втором шаге можно указать или изменить область выделенных ячеек.
На третьем – задать параметры диаграммы.
На четвертом – место расположения диаграммы.
Переход на следующий шаг осуществляется нажатием на диалоговую кнопку Далее. В любой момент можно вернуться на предыдущий шаг, нажав на кнопку Назад. Кроме этого, на любом из шагов можно закончить работу с мастером и построить диаграмму, нажав на кнопку Готово.
Для того чтобы отредактировать любой элемент диаграммы, достаточно дважды щелкнуть мышью по этому элементу (контекстное меню на требуемом элементе). На экране появится диалоговое окно, в котором можно задать рамку диаграммы, ее цвет и толщину контура, тип и размер шрифта и прочие атрибуты.
|
|
|
Для изменения размеров диаграммы необходимо выделить ее и, перемещая маркеры изменения размеров с помощью мыши, установить требуемый размер. Если перетаскивать маркер изменения размера на одной из сторон диаграммы при нажатой клавише < Ctrl >, то размеры диаграммы изменяются с этой и противоположной стороны диаграммы одновременно. При перетаскивании углового маркера размер диаграммы изменяется сразу во всех направлениях.
Если нужно удалить диаграмму, выделяем ее и нажимаем клавишу < Delete > или из меню Правка выбираем команду Очи стить, а затем Все.
Использование мастера диаграмм является самым простым и надежным способом создания диаграмм. Но можно построить диаграмму и автоматически, если выделить область данных и нажать клавишу < F 11> (< Alt > + < F 1>).
На существующую диаграмму можно добавлять данные. Если данные, которые необходимо добавить, находятся на том же листе, где и диаграмма, выделяем данные и с помощью мыши перетаскиваем их на диаграмму. Когда данные и диаграмма находятся на разных листах, выделяем диаграмму и из меню Диаграмма выбираем команду Добавить данные. После этого активизируем рабочий лист с новыми данными и выделяем диапазон ячеек с данными.
Если Excel не обновляет диаграммы после изменения данных на рабочем листе, то, возможно, установлен режим ручного пересчета. Для установки автоматического пересчета необходимо из меню Сервис выбрать команду Параметры и на вкладке Вычисления установить переключатель Автоматически.
СОРТИРОВКА ДАННЫХ
Сортировка позволяет выстраивать данные в алфавитном или цифровом порядке по возрастанию или убыванию.
Для того чтобы отсортировать данные, необходимо:
1. Выделить диапазон ячеек, который необходимо отсортировать. Если диапазон ячеек, который нужно отсортировать, со всех сторон ограничен пустыми ячейками, то достаточно только активизировать любую ячейку.
2. Из меню Данные выбрать команду Сортировка. В списке Сортировать по выбрать первое поле, по которому следует упорядочить данные.
|
|
|
3. Выбрать переключатель По возрастанию или По убыва нию.
4. В списке Затем по выбрать второе поле сортировки, если возникает необходимость.
5. Установить переключатель Подписям, если выделены и заголовки столбцов, или переключатель Обозначениям столбцов листа, если заголовков не выделены.
Кнопки Сортировка по возрастанию (убыванию) на стандартной панели инструментов позволяют упорядочить данные только по одному полю. Чтобы воспользоваться ими, необходимо активизировать ячейку в столбе, который следует отсортировать и нажать кнопку Сортировка по возрастанию (убыванию) на панели инструментов.
ПЕЧАТЬ ДОКУМЕНТА
Excel производит автоматическое разбиение рабочего листа на страницы при печати. Иногда бывает нужно переместить разрывы страниц, чтобы связанные данные находились рядом. Для того чтобы вставить разрыв страницы вручную, необходимо переместить активную ячейку правее и ниже места, где нужно сделать разрыв, а затем из меню Вставка выбрать команду Разрыв страницы. Если необходимо установить вертикальные разрывы страниц, то нужно сделать активной ячейку в первой строке. Если требуется установить горизонтальные разрывы страниц, то нужно сделать активной ячейку в столбце А.
Для удаления выборочного ручного разрыва страницы необходимо сделать активной ячейку под ручным разрывом страницы или справа от него и из меню Вставка выбрать команду Убрать разрыв страницы. Если необходимо удалить все разрывы страниц, то выбеливается весь рабочий лист и из меню Вставка выбирается команда Сброс разрывов страниц.
С целью проверки внешнего вида документа перед печатью его можно вывести на экран, используя кнопку Предварительный просмотр на панели инструментов или выбрав эту же команду из меню Файл.
Предварительный просмотр позволяет:
· увеличивать и уменьшать масштаб отображения информации на экране, нажав кнопку Масштаб или щелкнув указателем мыши в форме лупы по той части, которую нужно увеличить (уменьшить);
· перемещаться по страницам документа с помощью кнопок Далее, Назад или клавиш управления курсором;
· изменять поля документа с помощью кнопки Поля или перемещая маркеры полей документа;
· изменить параметры страницы, нажав кнопку Страницы;
· напечатать документ, нажав кнопку Печать.
Функции – заранее определенные формулы, которые выполняют вычисления по заданным величинам, называемым аргументами, и в указанном порядке. Например, функция СУММ суммирует значения в диапазоне ячеек, а функция СРЗНАЧ находит среднее значение своих аргументов.
|
|
|
О вложении функций в другие функции. В некоторых случаях может потребоваться использование функции как одного из аргументов другой функции.
Аргумент – это значения, используемые функцией для выполнения операций или вычислений. Тип аргумента, используемого функцией, зависит от конкретной функции. Обычно аргументы, используемые функциями, являются числами, текстом, ссылками на ячейки и имена.
Например, в следующей формуле функция СРЗНАЧ вложена в функцию ЕСЛИ для сравнения среднего значения нескольких значений с числом 50.
=ЕСЛИ(СРЗНАЧ(F2:F5)>50;СУММ(G2:G5);0).
Допустимые типы вычисляемых значений. Вложенная функция, используемая в качестве аргумента, должна вычислять соответствующий этому аргументу тип данных. Например, если аргумент должен быть логическим, то есть иметь значение либо ИСТИНА, либо ЛОЖЬ, то вложенная функция в результате вычислений тоже должна давать логическое значение либо ИСТИНА, либо ЛОЖЬ. Иначе появится сообщение об ошибке «#ЗНАЧ!».
Ограничение количества уровней вложения функций. В формулах можно использовать до семи уровней вложения функций. Когда функция Б является аргументом функции А, функция Б находится на втором уровне вложенности. Например, функции СРЗНАЧ и СУММ считаются функциями второго уровня, потому что обе они являются аргументами функции ЕСЛИ. Функция, вложенная в качестве аргумента в функцию СРЗНАЧ, будет функцией третьего уровня и так далее.
В некоторых случаях может потребоваться использование функции как одного из аргументов другой функции. Например, в следующей формуле функция СРЗНАЧ вложена в функцию ЕСЛИ для сравнения среднего значения нескольких значений с числом 50.
Функций даты и времени. Широкий набор встроенных функций пакета MS Excel позволяет обрабатывать числа и даты, находящиеся в ячейках таблицы, в соответствии с требованиями. Возможность работы с датами обеспечивают функции категории даты и времени. Так, с помощью данных функций можно: преобразовывать число в текст и наоборот, определять число рабочих дней на различных временных интервалах, вычислять возраст и стаж работы сотрудников и т. д.
С помощью функций даты и времени можно решить практически любые задачи, связанные с учетом даты или времени (например, определить возраст, определить число рабочих дней на любом промежутке времени).
Примечание.
Чтобы применять функцию РАБДЕНЬ и ЧИСТРАБДНИ, на вашем компьютере следует установить специальные надстройки. Для этого щелкните команду Надстройки в меню Сервис и установите флажки Пакет анализа.
ДАТА(год; месяц; число) – возвращает код-даты, соответствующий числовым значениям года, месяца, числа:
год – аргумент, который может иметь от одной до четырех цифр;
месяц – число, представляющее месяц года. Если значение аргумента больше 12, введенное число месяцев отсчитывается от первого месяца указанного года. Например, ДАТА(2008;14;2) возвращает число, соответствующее 2 февраля 2009 года;
день – число, представляющее день месяца. Если значение аргумента больше числа дней в указанном месяце, введенное число дней отсчитывается от первого дня месяца. Например, ДАТА(2008;1;35) возвращает число, соответствующее 4 февраля 2008 года.
ВРЕМЯ(часы; минуты; секунды) – возвращает целое число, представляющее определенное время. Время в числовом формате, возвращаемое функцией:
часы – целое число от 0 (нуля) до 32767, задающее часы. Если значение параметра часы больше 23, его можно разделить на 24; остаток от деления будет соответствовать значению часов. Например, ВРЕМЯ(27;0;0) = ВРЕМЯ(3;0;0) = 0,125 = 3:00 AM;
минуты – число от 0 (нуля) до 32767, задающее минуты. Если значение больше 59, оно будет пересчитано в часы и минуты. Например, ВРЕМЯ(0;750;0) = ВРЕМЯ(12;30;0) = 0,520833 = 12:30 PM;
секунды – число от 0 (нуля) до 32767, задающее секунды. Если значение больше 59, оно будет пересчитано в часы, минуты и секунды. Например, ВРЕМЯ(0;0;2000) = ВРЕМЯ(0;33;22) = 0,023148 = 12:33:20 AM.
СЕГОДНЯ() – возвращает код текущей даты.
ГОД(дата) – возвращает год, соответствующий аргументу дата. Год определяется как целое в интервале 1900-9999.
МЕСЯЦ (дата) – возвращает месяц в дате, заданной в числовом формате. Месяц возвращается как целое число в диапазоне от 1 (январь) до 12 (декабрь).
ДЕНЬ (дата) – возвращает день в дате, заданной в числовом формате. День возвращается как целое число в диапазоне от 1 до 31.
У функций ГОД, МЕСЯЦ и ДЕНЬ аргумент дата представляет собой дату (“10.04.2010”) или код-даты (40278).
ЧАС(время) – возвращает час, соответствующий заданному времени в числовом формате. Час определяется как целое в интервале от 0 до 23.
МИНУТЫ(время) – возвращает минуты, соответствующие аргументу время. Минуты определяются как целое число в интервале от 0 до 59.
СЕКУНДЫ(время) – возвращает секунды, соответствующие аргументу время. Секунды определяются как целое в интервале от 0 до 59.
У функций ЧАС, МИНУТЫ и СЕКУНДЫ аргумент время представляет собой время (“12:05”) или код-даты (0,50347).
Текстовые функции служат для решения задач, связанных с обработкой строковых значений.
ЛЕВСИМВ (текст; количество_знаков) – возвращает указанное число знаков с начала текстовой строки
текст – текстовая строка, содержащая извлекаемые знаки;
количество_знаков – количество знаков, извлекаемых функцией ЛЕВСИМВ.
ПРАВСИМВ (текст;число_знаков) – возвращает указанное число знаков с конца текстовой строки. В остальном функция аналогична функции ЛЕВСИМВ.
СЦЕПИТЬ (текст1;текст2;...) – объединяет несколько текстовых строк в одну.
текст1, текст2,... – это от 1 до 30 элементов текста, объединяемых в один элемент текста. Элементами текста могут быть текстовые строки, числа или ссылки, которые ссылаются на одну ячейку.
ЗАМЕНИТЬ (старый_текст; нач_поз; число_знаков; новый_текст) – замещает указанную часть знаков текстовой строки другой строкой текста.
ПОДСТАВИТЬ (текст; стар_текст; нов_ текст;номер_вхождения) – подставляет текст нов_текст вместо текста стар_текст в текстовой строке.
СИМВОЛ (число) – возвращает символ с заданным кодом.
число – это число от 1 до 255, указывающее нужный знак. Знаки выбираются из кодовой таблицы.
НАЙТИ (искомый_текст; просматриваемый_ текст;нач_позиция). Функция НАЙТИ находит вхождение одной текстовой строки искомый_текст в другую текстовую строку просматриваемый_текст и возвращает положение начала искомого текста относительно крайнего левого знака просматриваемого текста. Функция НАЙТИ учитывает регистр и не допускает использования подстановочных знаков.
ПОИСК (искомый_текст; текст_для_ поиска;нач_позиция) – функция выполняет те же операции, что и функция НАЙТИ. Имеются два основных различия. Функция ПОИСК не учитывает регистр и допускает использование подстановочных знаков (знак вопроса (?) соответствует любому знаку; звездочка (*) соответствует любой последовательности знаков) в аргументе искомый_текст.
|
|
|
