Если у вас возникли сложности с курсовой, контрольной, дипломной, рефератом, отчетом по практике, научно-исследовательской и любой другой работой - мы готовы помочь.
Предоплата всего
Подписываем
Министерство образования и науки РФ
Федеральное бюджетное государственное
образовательное учреждение
высшего профессионального образования
«Тульский государственный университет»
Политехнический институт
Кафедра "Автоматизированные станочные системы"
Троицкий Д.И.
доцент, к.т.н.
МЕТОДИЧЕСКИЕ УКАЗАНИЯ
ПО ВЫПОЛНЕНИЮ ЛАБОРАТОРНОЙ РАБОТЫ №3
по дисциплине
ИНФОРМАТИКА
Направление подготовки:
230100 Информатика и вычислительная техника
Профиль подготовки:
Системы автоматизированного проектирования
Форма обучения – очная, очно-заочная, заочная
Тула 2011 г.
Рассмотрено на заседании кафедры "Автоматизированные станочные системы"
протокол №1 от "31" августа 2011 г.
Зав. кафедрой________________А.Н. Иноземцев
Содержание
|
[1] Что такое Excel? [2] Начинаем работать [2.1] Перемещение по листу Excel и выделение ячеек [2.2] Ввод данных в ячейки Excel [2.2.1] Числовые значения [2.2.2] Текстовые значения [2.2.3] Изменение значений в ячейке [2.2.4] Защита данных в ячейках [2.2.5] Скрытие ячеек и листов [2.3] Использование мыши при вводе и редактировании [3] Создание формул в Excel [3.1] Относительные, абсолютные и смешанные ссылки [3.2] Использование текста в формулах [3.3] Введение в функции Excel [3.4] Использование заголовков в формулах [4] Имена ячеек и диапазонов в Excel [4.1] Имена в формулах [4.2] Правила присваивания имен ячейкам и диапазонам [5] Массивы Excel [5.1] Двумерные массивы [5.2] Правила для формул массива [6] Форматы ячеек в Excel [6.1] Форматирование чисел и текста в Excel [6.1.1] Общий формат [6.1.2] Числовые форматы [6.1.3] Денежные форматы [6.1.4] Финансовые форматы [6.1.5] Процентные форматы [6.1.6] Дробные форматы [6.1.7] Экспоненциальные форматы [6.1.8] Текстовый формат [6.1.9] Дополнительные форматы [6.2] Условное форматирование и объединение ячеек [6.3] Объединение ячеек [7] Все о функциях Excel [7.1] Синтаксис функций [7.2] Использование аргументов [7.3] Типы аргументов [7.3.1] Числовые значения [7.3.2] Текстовые значения [7.3.3] Логические значения [7.3.4] Именованные ссылки [7.3.5] Использование различных типов аргументов [7.4] Ввод функций в рабочем листе Excel [7.5] Математические функции Excel [7.5.1] Функция СУММ (SUM) [7.5.2] Функции ОКРУГЛ, ОКРУГЛВНИЗ, ОКРУГЛВВЕРХ [7.5.3] Функции ЧЁТН и НЕЧЁТ [7.5.4] Функции ОКРВНИЗ, ОКРВВЕРХ [7.5.5] Функции ЦЕЛОЕ и ОТБР [7.5.6] Функции СЛЧИС и СЛУЧМЕЖДУ [7.5.7] Функция ПРОИЗВЕД [7.5.8] Функция ОСТАТ [7.5.9] Функция КОРЕНЬ [7.5.10] Функция ЧИСЛОКОМБ [7.5.11] Функция ЕЧИСЛО [7.5.12] Функция LOG [7.5.13] Функция LN [7.5.14] Функция EXP [7.5.15] Функция ПИ [7.5.16] Функция РАДИАНЫ и ГРАДУСЫ [7.5.17] Функция SIN [7.5.18] Функция COS [7.5.19] Функция TAN [7.6] Текстовые функции Excel [7.6.1] Функция ТЕКСТ [7.6.2] Функция РУБЛЬ [7.6.3] Функция ДЛСТР [7.6.4] Функция СИМВОЛ и КОДСИМВ [7.6.5] Функции СЖПРОБЕЛЫ и ПЕЧСИМВ [7.6.6] Функция СОВПАД [7.6.7] Функции ПРОПИСН, СТРОЧН и ПРОПНАЧ [7.6.8] Функции ЕТЕКСТ и ЕНЕТЕКСТ [7.7] Функции Excel для работы с элементами строк [7.7.1] Функции НАЙТИ и ПОИСК [7.7.2] Функции ПРАВСИМВ и ЛЕВСИМВ [7.7.3] Функция ПСТР [7.7.4] Функции ЗАМЕНИТЬ и ПОДСТАВИТЬ [7.7.5] Функция ПОВТОР [7.7.6] Функция СЦЕПИТЬ [7.8] Логические функции Excel [7.8.1] Функция ЕСЛИ [7.8.2] Функции И, ИЛИ, НЕ [7.8.3] Вложенные функции ЕСЛИ [7.8.4] Функции ИСТИНА и ЛОЖЬ [7.8.5] Функция ЕПУСТО [8] Построение диаграмм и графиков в Excel [8.1] Тип диаграммы [8.2] Выбор данных [8.3] Оформление диаграммы [8.4] Размещение диаграммы [8.5] Редактирование диаграммы [8.6] Примеры построения диаграмм [8.6.1] Круговая диаграмма [8.6.2] Столбчатая диаграмма (гистограмма) [8.6.3] Линейная диаграмма (график) [8.6.4] Нормированная линейчатая диаграмма [8.6.5] Диаграмма с областями [9] Библиографический список |
Новые системы плодят новые проблемы.
Из законов Мэрфи
Excel относится к табличным процессорам или электронным таблицам (electronic spreadsheet). Исторически это первый класс программного обеспечения, реализованный на персональных компьютерах. Принципы работы табличных процессоров остались неизменными со времен первой подобной программы VisiCalс (1979г.)
Что же такое электронная таблица (ЭТ)? Это средство информационных технологий, позволяющее решать целый комплекс задач:
1. Выполнение вычислений. Издавна многие расчеты выполняются в табличной форме: многочисленные расчетные ведомости, сметы расходов и т.п. Кроме того, решение численными методами целого ряда математических задач удобно выполнять в табличной форме. Электронные таблицы представляют собой удобный инструмент для автоматизации таких вычислений.
2. Математическое моделирование. Использование математических формул в ЭТ позволяет представить взаимосвязь между различными параметрами некоторой реальной системы. Основное свойство ЭТ — мгновенный пересчет формул при изменении значений входящих в них операндов. Благодаря этому свойству, таблица представляет собой удобный инструмент для организации численного эксперимента:
— подбор параметров,
— прогноз поведения моделируемой системы,
— анализ зависимостей,
— планирование.
Дополнительные удобства для моделирования дает возможность графического представления данных (диаграммы, графики).
Использование электронной таблицы в качестве базы данных. Конечно, по сравнению с настоящими системами управления базами данных (СУБД) электронные таблицы имеют меньшие возможности в этой области. Однако некоторые операции манипулирования данными, свойственные реляционным СУБД, в них реализованы. Это поиск информации по заданным условиям и сортировка информации.
ЭТ Excel создана корпорацией Microsoft и входит в пакет MS Office. Название "excel" означает "превосходный, наилучший".
Как только вы принимаетесь делать какую-то работу, находится другая,
которую надо сделать еще раньше.
Из законов Мэрфи
Интерфейс Excel очень схож с Microsoft Word. После запуска программы экран Excel содержит пять областей (по порядку сверху вниз, ):
Рис. . – Экран Excel.
Основным отличием от Word является присутствие вместо окна документа так называемого окна книги, другими словами электронной таблицы.
Книга Excel разбита на несколько листов (таблиц). Листы можно удалять или добавлять новые. Как и всякая таблица, лист Excel состоит из строк и столбцов, пересечения которых образуют ячейки.
В нижней части окна книги находится несколько кнопок, с помощью которых можно переходить от одного листа к другому. Если видны не все ярлычки листов, то для просмотра содержания книги можно использовать четыре кнопки, расположенные в нижнем левом углу окна.
Ячейки Excel являются основными строительными единицами рабочего листа. Каждая ячейка имеет свои строго определенные координаты, или адрес ячейки, где можно хранить и отображать информацию. Ячейка, находящаяся на пересечении столбца А и строки 1 имеет адрес А1. Возможен другой способ адресации ячеек (так называемый "стиль R1C1", где R означает ряд [row], а С – колонку [column]), когда ячейки нумеруются и по горизонтали, и по вертикали, и левая верхняя ячейка будет иметь адрес R1C1. Переключение способа адресации производится в настройках.
Зачастую границы данных таблицы выходят за пределы экрана монитора. В этом случае для просмотра содержимого листа надо использовать полосы прокрутки, расположенные вдоль правой и нижней сторон окна книги.
В верхней части рабочей области Excel расположена строка меню. Ниже находятся панели инструментов с кнопками, выполняющими наиболее часто используемые функции. Работа с этой частью рабочей области Excel аналогична работе в Word.
Ниже панели инструментов расположена строка формул ().
Рис. . – Строка формул.
Содержимое активной (выделенной в данный момент) ячейки Excel всегда появляется в строке формул. В процессе ввода или редактирования данных в ячейке, в строке формул появляются три кнопки:
- кнопка отмены (красный крестик);
- кнопка ввода (зеленая галочка);
- кнопка изменения формулы (знак функции).
Информацию можно вводить как непосредственно в ячейку, так и в строку формул.
В самом низу окна рабочей области находится строка состояния, показывающая режим работы программы.
Чтобы производить какие-либо действия с ячейками Excel их надо выделить. При выделении одной ячейки ее адрес появляется в поле имени в строке формул. Самый простой и распространенный способ выделения одной ячейки - щелчок левой кнопкой мыши. Подведите курсор к нужной ячейке Excel и нажмите левую кнопку мыши. При этом вокруг ячейки появится рамка, которая говорит о том, что данная ячейка является активной, т.е. в нее можно вводить или редактировать данные.
Для ускорения выполнения операций часто бывает необходимо выделение группы ячеек или диапазона. Чтобы выделить диапазон надо установить курсор на левую верхнюю ячейку выделяемого диапазона, нажать левую кнопку мыши и, не отпуская кнопку, "тащить" мышь к правой нижней ячейке. При достижении нужной ячейки кнопку мыши надо отпустить. При этом выделенный диапазон будет подсвечен другим цветом.
Для выделения большого диапазона ячеек Excel можно воспользоваться приемом расширения выделения. Для этого надо выделить левую верхнюю ячейку диапазона, затем, удерживая клавишу Shift, щелкнуть на правой нижней ячейке, если ячейка не видна, то можно воспользоваться полосами прокрутки. Если известны координаты ячеек, то можно сделать следующее: выделить левую верхнюю ячейку, затем в поле имени в строке формул, через двоеточие ввести координаты правой нижней ячейки и нажать клавишу Enter.
Для уменьшения/увеличения масштаба изображения можно воспользоваться командой "Масштаб" меню "Вид" либо крутить колесико мыши, зажав клавишу Ctrl.
Если необходимо выделить группу диапазонов ячеек, то для этой цели надо воспользоваться клавишей Ctrl.
Если необходимо выделить столбец или строку целиком, надо щелкнуть на заголовке строки или столбца.
Блок - это диапазон ячеек Excel, окруженный со всех сторон пустыми ячейками или заголовками столбцов и строк.
Активная область - это прямоугольник, который заключает в себя все столбцы и строки листа, содержащие непустые ячейки.
Рис. . – Блоки ячеек.
На представлены три блока ячеек: A3:E8, A10:E10, G3:G8. Активная область - A3:G10. Если установить указатель мыши на нижней границе активной ячейки и дважды щелкнуть левой кнопкой мыши, то будет выделена нижняя ячейка блока. Если активной является самая нижняя ячейка блока, то двойной щелчок на нижней границе ячейки приведет к выделению ячейки, расположенной ниже блока. Аналогичным образом выделяются ячейки при щелчке на правой, левой или верхней границе активной ячейки. Если дважды щелкнуть на границе ячейки при нажатой клавише Shift, то будут выделены все ячейки, начиная с текущей до края блока.
Перемещаться по соседним ячейкам можно при помощи клавиш управления курсором. Для перемещения к краю блока ячеек используются клавиши со стрелками при нажатой клавише Ctrl.
Для перемещения по листу Excel и выделения ячеек удобно пользоваться клавишами:
Home - перемещение к первой ячейке текущей строки;
Ctrl+Home - перемещение к ячейке A1;
Ctrl+End - перемещение к последней ячейке последнего столбца активной области.
Для быстрого перемещения и выделения ячейки или диапазона можно пользоваться командой меню "Правка"-"Перейти" (F5).
Существует два типа данных, которые можно вводить в ячейки листа Excel - константы и формулы.
Константы в свою очередь подразделяются на числовые значения, текстовые значения, значения даты и времени, логические значения и ошибочные значения.
Числовые значения могут содержать цифры от 0 до 9, а также спецсимволы + - Е е ( ) . , $ % /
Для ввода числового значения в ячейку необходимо выделить нужную ячейку и ввести с клавиатуры необходимую комбинацию цифр. Вводимые цифры отображаются как в ячейке, так и в строке формул. По завершению ввода необходимо нажать клавишу Enter. После этого число будет записано в ячейку. По умолчанию после нажатия Enter активной становится ячейка, расположенная на строку ниже, но командой "Сервис"-"Параметры" можно на вкладке "Правка" установить необходимое направление перехода к следующей ячейке после ввода, либо вообще исключить переход. Если после ввода числа нажать какую-либо из клавиш перемещения по ячейкам (Tab, Shift+Tab…), то число будет зафиксировано в ячейке, а фокус ввода перейдет на соседнюю ячейку.
Рассмотрим особенности ввода числовых значений, использующих спецсимволы.
1. Если надо ввести отрицательное число, то перед числом необходимо поставить знак "-" (минус).
2. Символ Е или е используется для представления числа в экспоненциальном виде. Например, 5е3 означает 5*103, т.е. 5000.
3. Числа, заключенные в скобки, интерпретируются как отрицательные, даже если перед числом нет знака минуса. Например, (40) для Excel означает -40.
4. При вводе больших чисел для удобства представления между группами разрядов можно вводить пробел (23 456,00). В этом случае в строке формул пробел отображаться не будет, а в самой ячейке число будет с пробелом.
5. Для ввода денежного формата используется знак доллара ($).
6. Для ввода процентного формата используется знак процента (%).
7. Для ввода даты и дробных значений используется знак косой черты (/). Если Excel может интерпретировать значение как дату, например 1/01, то в ячейке будет представлена дата - 1 января. Если надо представить подобное число как дробь, то надо перед дробью ввести ноль - 0 1/01. Дробью также будет представлено число, которое не может быть интерпретировано как дата, например 88/32.
Иногда возникает необходимость ввода длинных чисел. При этом для его отображения в строке формул используется экспоненциальное представление не более чем с 15 значащими цифрами. Точность значения выбирается такой, чтобы число можно было отобразить в ячейке.
Рис. . – Типы числовых данных
В этом случае значение в ячейке называется вводимым или отображаемым значением. Значение в строке формул называется хранимым значением. Количество вводимых цифр зависит от ширины столбца. Если ширина недостаточна, то Excel либо округляет значение, либо выводит символы ###. В этом случае можно попробовать увеличить размер ячейки.
Ввод текста полностью аналогичен вводу числовых значений. Вводить можно практически любые символы. Если длина текста превышает ширину ячейки, то текст накладывается на соседнюю ячейку, хотя фактически он находится в одной ячейке. Если в соседней ячейке тоже присутствует текст, то он перекрывает текст в соседней ячейке.
Рис. . – Ввод текста.
Для настройки ширины ячейки по самому длинному тексту, надо щелкнуть на границе столбца в его заголовке. Так если щелкнуть на линии между заголовками столбцов А и В, то ширина ячейки будет автоматически настроена по самому длинному значению в этом столбце.
Рис. . – Настройка ширины столбцов.
Если возникает необходимость ввода числа как текстового значения, то перед числом надо поставить знак апострофа, либо заключить число в кавычки - '123 "123".
Различить какое значение (числовое или текстовое) введено в ячейку можно по признаку выравнивания. По умолчанию текст выравнивается по левому краю, в то время как числа - по правому.
При вводе значений в диапазон ячеек ввод будет происходить слева-направо и сверху-вниз. Вводя значения и завершая ввод нажатием Enter, курсор будет переходить к соседней ячейке, находящейся справа, а по достижении конца блока ячеек в строке, перейдет на строку ниже в крайнюю левую ячейку.
Для изменения значений в ячейке до фиксации ввода надо пользоваться, как и в любом текстовом редакторе, клавишами Del и Backspace. Если надо изменить уже зафиксированную ячейку, то надо дважды щелкнуть на нужной ячейке, при этом в ячейке появится курсор. После этого можно производить редактирование данных в ячейке. Можно просто выделить нужную ячейку, а затем установить курсор в строке формул, где отображается содержимое ячейки и затем отредактировать данные. После окончания редакции надо нажать Enter для фиксации изменений. В случае ошибочного редактирования ситуацию можно "отмотать" назад при помощи кнопки "Отменить" (Ctrl+Z).
Для защиты отдельных ячеек надо воспользоваться командой меню "Сервис"-"Защита"-"Защитить лист". После включения защиты изменить заблокированную ячейку невозможно. Однако не всегда необходимо блокировать все ячейки листа. Прежде чем защищать лист, выделите ячейки, которые надо оставить незаблокированными, а затем в меню "Формат" выберите команду "Ячейки". В открывшемся окне диалога "Формат ячеек" на вкладке "Защита" снимите флажок "Защищаемая ячейка". Следует иметь ввиду, что Excel не обеспечивает индикации режима защиты для отдельных ячеек. Если необходимо отличать заблокированные ячейки, можно выделить их цветом. В защищенном листе можно свободно перемещаться по незаблокированным ячейкам при помощи клавиши Tab.
Чтобы включить режим скрытия формул, надо:
- выделить нужные ячейки;
- выбрать "Формат"-"Ячейки" (Ctrl+1);
- на вкладке "Защита" установить флажок "Скрыть формулы";
- выбрать "Сервис"-"Защита"-"Защитить лист";
- в окне диалога "Защитить лист" установить флажок "Содержимого".
После этого при активизации ячеек, содержащих скрытые формулы, эти формулы не выводятся в строке формул, хотя результаты вычислений видны.
При желании можно скрыть весь лист. При этом все данные листа сохраняются, но они не доступны для просмотра.
Чтобы скрыть лист книги надо щелкнуть на ярлычке листа и выбрать команду "Формат"-"Лист"-"Скрыть". После скрытия листа в подменю "Лист" появится команда "Отобразить", с помощью которой можно сделать лист опять видимым.
Для удаления защиты листа или книги надо выбрать команду "Сервис"-"Защитить"-"Снять защиту листа/книги".
Чтобы быстро переместить ячейку (или диапазон ячеек) на новое место, достаточно ее выделить и перетащить. Следует сказать, что "захватывать" выделенные ячейки следует за жирный контур, окаймляющий диапазон. При перетаскивании появляется серая рамка, которая позволяет правильно разместить диапазон. Когда серая рамка окажется над нужными ячейками, надо отпустить кнопку мыши.
Чтобы скопировать и вставить выделение, не перемещая его, перед тем как отпустить кнопку мыши, надо нажать клавишу Ctrl. Рядом с указателем мыши при этом появится значок "плюс".
С помощью мыши можно также вставлять ячейки в рабочий лист. При этом надо удерживать нажатой клавишу Shift. При этом, рядом с указателем мыши выводится экранная подсказка с новым адресом ячеек. Нажимать и отпускать клавишу Shift надо обязательно до и после нажатия кнопки мыши.
Для того, чтобы скопировать и вставить выделенные ячейки надо использовать комбинацию клавиш Ctrl+Shift.
Эти приемы можно использовать для выделения целых строк или столбцов и последующего их перемещения или копирования в новые места.
Вставка, удаление и очистка ячеек с помощью мыши. При выполнении следующей группы операций используется маркер заполнения (). Если вы выделите ячейку или диапазон, в нижнем правом углу прямоугольника выделения появится маркер заполнения. Если выделить целый столбец (столбцы), маркер заполнения появится рядом с заголовком столбца. При выделении целой строки (или нескольких строк) маркер заполнения окажется рядом с заголовком строки. Используя в качестве примера лист, представленный вверху, предположим, что надо добавить несколько чисел между столбцами А и В. Для этого надо сначала выделить ячейки А1:А2 (или столбец А целиком).
Рис. . – Маркер заполнения.
Затем установите указатель на маркере заполнения и перетащите его на один столбец вправо при нажатой клавише Shift. При перетаскивании указатель мыши изменяется на двойную линию с парой поперечных разнонаправленных стрелок.
Можно легко удалять ячейки, столбцы или строки, используя ту же технику. Например, чтобы удалить столбец, который мы только что вставили, выделите столбец В, а затем, при нажатой клавише Shift, перетащите маркер заполнения на один столбец влево. Выделение станет серым, а указатель мыши изменится на двойную линию с парой поперечных стрелок, направленных навстречу друг другу. Когда кнопка мыши будет отпущена, выделение будет удалено. Если не удерживать клавишу Shift при перетаскивании назад над выделенными ячейками, то очистится содержимое ячеек вместо их удаления.
Перетаскивание с использованием правой кнопки мыши. Если выделить ячейки и затем перетащить любую границу прямоугольника выделения (но не за маркер заполнения) при нажатой правой кнопке мыши, то когда кнопка будет отпущена, на экране появится контекстное меню (). Команды этого меню можно использовать при работе с выделенными ячейками несколькими способами.
Рис. . – Контекстное меню.
Команда "Переместить" перемещает исходные ячейки в новое место.
Команда "Копировать" копирует выделенные ячейки (содержимое и форматы) в конечные ячейки, начиная с того места, где вы отпустите кнопку мыши.
Команда "Копировать только форматы" копирует в конечные ячейки все форматы, которые были применены к исходным ячейкам. Содержимое исходных и конечных ячеек при этом не изменяется.
Команда "Копировать только значения" копирует содержимое исходных ячеек в конечные, но не оказывает никакого воздействия на форматы.
Команда "Связать" создает в конечном диапазоне формулы, ссылающиеся на исходные ячейки.
Команду "Создать гиперссылку" можно использовать для создания "перехода" к информации, хранящейся в другом документе или рабочем листе. Например, вы можете выделить ячейку (или диапазон) и затем перетащить ее при нажатой правой кнопке мыши в другой лист. После того как вы отпустите кнопку мыши и в контекстном меню выберете команду "Создать гиперссылку", Excel создаст гиперссылку в конечной ячейке. Содержимое конечной ячейки сохранится, но ее формат изменится для того, чтобы показать, что вы можете воспользоваться гиперссылкой для быстрого перехода к ячейке в исходном листе.
Во всякой формуле константы (особенно те, которые взяты из технических
справочников) должны рассматриваться как переменные.
Из законов Мэрфи
Переходим к самому интересному - созданию формул. Собственно это то, ради чего и разрабатывались электронные таблицы.
Вводить формулу надо со знака равенства. Это надо для того, чтобы Excel понял, что в ячейку вводится именно формула, а не данные.
Выделим произвольную ячейку, например А1. В строке формул введем =2+3 и нажмем Enter (). В ячейке появится результат (5). А в строке формул останется сама формула.
Рис. . – Ввод формул.
Поэкспериментируйте с различными арифметическими операторами: сложения (+), вычитания (-), умножения (*), деления (/). Чтобы их правильно использовать надо четко представлять их приоритет.
1. В первую очередь выполняются выражения внутри скобок.
2. Умножение и деление имеют более высокий приоритет чем сложение и вычитание.
3. Операторы с одинаковым приоритетом выполняются слева направо.
ИСПОЛЬЗУЙТЕ СКОБКИ. В этом случае вы обезопасите себя от случайной ошибки в вычислениях с одной стороны, а с другой - скобки значительно облегчают чтение и анализ формул. Если в формуле количество закрывающих и открывающих скобок не совпадает, Excel выдаст сообщение об ошибке и предложит вариант ее исправления. Сразу после ввода закрывающей скобки Excel отображает жирным шрифтом (или другим цветом) последнюю пару скобок, что очень удобно при наличии в формуле большого количества скобок.
Теперь давайте попробуем поработать с использованием в формулах ссылок на другие ячейки (). Введите в ячейку А1 число 10, а в ячейку А2 - число 15. В ячейке А3 введите формулу =А1+А2. В ячейке А3 появится сумма ячеек А1 и А2 - 25. Поменяйте значения ячеек А1 и А2 (но не А3!). После смены значений в ячейках А1 и А2 автоматически пересчитывается значение ячейки А3 (согласно формулы).
Рис. . – Использование ссылок.
Дабы не ошибиться при вводе адресов ячеек, можно использовать при вводе ссылок мышь. В нашем случае надо проделать следующее:
Результат будет аналогичным.
Относительная ссылка указывает на ячейку, согласно ее положения относительно ячейки, содержащей формулу. Обозначение относительной ячейки - А1.
Абсолютная ссылка указывает на ячейку, местоположение которой неизменно. Обозначение абсолютной ячейки - $A$1.
Смешанная ссылка содержит комбинацию относительной и абсолютной ссылок - $A1, A$1.
Для быстрого изменения типа ссылки используется клавиша F4. Введите в ячейку А1 любое число. В ячейку А2 введите формулу =А1. Затем нажимайте клавишу F4. После каждого нажатия клавиши тип ссылки будет меняться.
Рис. . – Изменение типа ссылок.
Чтобы лучше разобраться в различиях ссылок, давайте поэкспериментируем. Для начала разберем относительные ссылки. Введите в ячейки следующие значения:
А1 - 20 В1 - 200
А2 - 30 В2 - 300
В ячейку А3 введите формулу =А1+А2 и нажмите ввод.
Рис. . – Относительные ссылки
Теперь установите курсор на правый нижний угол ячейки А3, нажмите правую кнопку мыши и протяните на ячейку В3 и отпустите кнопку мыши. Появится контекстное меню, в котором надо выбрать "Копировать ячейки" ().
Рис. . – Копирование ячеек
После этого значение формулы из ячейки А3 будет скопировано в ячейку В3. Активизируйте ячейку В3 и посмотрите какая формула получилась - В1+В2 (). Почему так получилось? Когда мы записали в ячейке А3 формулу А1+А2, Excel интерпретировал эту запись так: "Взять значения из ячейки, расположенной в текущем столбце на две строки выше и сложить со значением ячейки, расположенной в текущем столбце на одну строку выше". Поэтому скопировав формулу из ячейки А3, например, в ячейку С43, мы получим - С41+С42. Вот в этом прелесть относительных ссылок: формула как бы сама подстраивается под наши задачи.
Рис. . – Результат копирования относительной ссылки.
Рассмотрим теперь абсолютные ссылки.
Введите в ячейки следующие значения:
А1 - 20 В1 - 200
А2 - 30 В2 - 300
Введите в ячейку С1 число 5.
В ячейку А3 введите следующую формулу =А1+А2+$C$1. Аналогичным образом скопируйте формулу из А3 в В3. Посмотрите что получилось. Относительные ссылки "подстроились" под новые значения, а вот абсолютная - осталась неизменной.
Рис. . – Абсолютные ссылки.
Попробуйте теперь сами поэкспериментировать со смешанными ссылками и посмотрите как они работают. Можно ссылаться на другие листы той же книги точно так, как и на ячейки текущего листа. Даже можно ссылаться на листы других книг. В этом случае ссылка будет называться внешней ссылкой.
Например, чтобы записать в ячейку А1 (Лист 1) ссылку на ячейку А5 (Лист2), надо проделать следующее:
Ссылка на ячейку из другой книги будет выглядеть так: =[Книга2]Лист2!А5.
Редактирование формул происходит аналогично редактированию текстовых значений в ячейках. Надо активизировать ячейку с формулой выделением или двойным щелчком мыши, а затем произвести редактирование, используя, при необходимости, клавиши Del, Backspace. Фиксация изменений выполняется клавишей Enter.
С текстовыми значениями можно выполнять математические операции, если текстовые значения содержат только следующие символы:
Цифры от 0 до 9 , + - е Е /
Еще можно использовать пять символов числового форматирования:
$ % ( ) пробел
При этом текст должен быть заключен в двойные кавычки, например "55"+"33". При выполнении вычислений Excel преобразует числовой текст в числовые значения, так результатом вышеуказанной формулы будет значение 88.
Для объединения текстовых значений служит текстовый оператор & (амперсанд). Например, если ячейка А1 содержит текстовое значение "Российская", а ячейка А2 - "Федерация", то введя в ячейку А3 следующую формулу =А1&А2, получим " РоссийскаяФедерация". Для вставки пробела надо написать так: =А1&" "&А2. Амперсанд можно использовать для объединения ячеек с разными типами данных. Так, если в ячейке А1 находится число 10, а в ячейке А2 - текст "мешков", то в результате действия формулы =А1&А2, мы получим "10мешков", Причем результатом такого объединения будет текстовое значение.
Функция Excel - это заранее определенная формула, которая работает с одним или несколькими значениями и возвращает результат. Наиболее распространенные функции Excel являются краткой записью часто используемых математических и финансовых формул.
Например функция =СУММ(А1:А4) аналогична записи =А1+А2+А3+А4.
Каждая функция состоит из имени и аргумента. В предыдущем случае СУММ - это имя функции, а А1:А4 - аргумент. Аргумент заключается в круглые скобки.
Автосумма. Так как функция суммы используется наиболее часто, то на панель инструментов "Стандартная" вынесена кнопка "Автосумма" [].
Введите в ячейки А1, А2, А3 произвольные числа. Активизируйте ячейку А4 и нажмите кнопку автосуммы. Результат показан ниже ().
Рис. . – Использование автосуммы.
Нажмите клавишу ввода. В ячейку А4 будет вставлена формула суммы ячеек А1..А3. Кнопка автосуммы снабжена выпадающим списком, из которого можно выбрать другую формулу для ячейки ().
Рис. . – Варианты автосуммы.
Для выбора имени функции служит кнопка "Вставка функции" в строке формул. При ее нажатии появляется следующее окно ().
Рис. . – Окно вставки функции.
Если точно неизвестна функция, которую надо применить в данный момент, то в окне диалога "Поиск функции" можно произвести поиск. В выпадающем списке "Категория" надо выбрать подходящую категорию для использования функции. Саму функцию выбирают в нижнем окне "Выберите функцию".
Если формула очень громоздкая, то можно включить в текст формулы пробелы или разрывы строк. Это никак не влияет на результаты вычислений. Для разрыва строки надо нажать комбинацию клавиш Alt+Enter.
Можно использовать в формулах вместо ссылок на ячейки таблицы заголовки таблицы. Постройте следующий пример ().
Рис. . – Использование заголовков в формулах
По умолчанию Excel не распознает заголовки в формулах. Чтобы использовать заголовки в формулах, выберите команду "Параметры" в меню "Сервис". На вкладке "Вычисления" в группе "Параметры книги" установите флажок "Допускать названия диапазонов".
При обычной записи формула в ячейке В6 выглядела бы так
=СУММ(В2:В4)
При использовании заголовков формула будет выглядеть так
=СУММ(Кв 1)
Необходимо знать следующее:
- если формула содержит заголовок столбца/строки, в котором она находится, то Excel считает, что вы хотите использовать диапазон ячеек, расположенных ниже заголовка столбца таблицы (или справа от заголовка строки);
- если формула содержит заголовок столбца/строки, отличного от того, в котором она находится, Excel считает, что вы хотите использовать ячейку на пересечении столбца/строки с таким заголовком и строки/столбца, где расположена формула.
При использовании заголовков можно указать любую ячейку таблицы с помощью пересечения диапазонов. Например, для ссылки на ячейку С3 в нашем примере можно использовать формулу =Строка2 Кв 2. Обратите внимание на пробел между заголовками строки и столбца.
Формулы, содержащие заголовки, можно копировать и вставлять, при этом Excel автоматически настраивает их на нужные столбцы и строки. Если будет произведена попытка скопировать формулу в неподходящее место, то Excel сообщит об этом, а в ячейке выведет значение ИМЯ?. При смене названий заголовков, аналогичные изменения происходят и в формулах.
У самого интересного экспоната не бывает таблички с названием.
Из законов Мэрфи
Ячейкам Excel и диапазонам ячеек можно присваивать имена и затем использовать их в формулах. Если формулы, содержащие заголовки, можно применять только в том же листе, где находится таблица, то, используя имена диапазонов, можно ссылаться на ячейки таблицы в любом месте любой книги.
Имя ячейки или диапазона можно использовать в формуле. Пусть у нас в ячейке А3 записана формула А1+А2. Если присвоить ячейке А1 имя "Базис", а ячейке А2 - "Надстройка", то запись Базис+Надстройка возвратит то же самое значение, что и предыдущая формула.
Для присвоения имени ячейке (диапазону ячеек) надо выделить соответствующий элемент, а затем в поле имени (там, где обычно отображается адрес ячейки вида A1) ввести наименование, при этом нельзя использовать пробелы.
Если выделенной ячейке или диапазону было дано имя, то в поле имени выводится именно оно, а не ссылка на ячейку. Если имя определено для диапазона ячеек, оно появится в поле имени только при выделении всего диапазона.
Если нужно перейти к именованной ячейке или диапазону, щелкните на стрелке рядом с полем имени и выберите в раскрывающемся списке имя ячейки или диапазона ().
Рис. . – Переход по имени
Более гибкие возможности присвоения имен ячеек и их диапазонов, а также заголовков, дает команда "Имя" из меню "Вставка".
1. Имя должно начинаться с буквы, обратной косой черты (\) или символа подчеркивания (_).
2. В имени можно использовать только буквы, цифры, обратную косую черту и символ подчеркивания.
3. Нельзя использовать имена, которые могут трактоваться как ссылки на ячейки (А1, С4).
4. В качестве имен могут использоваться одиночные буквы за исключением букв R, C.
5. Пробелы необходимо заменять символом подчеркивания.
Массивы в Excel используют для создания формул, которые возвращают некоторое множество результатов или оперируют множеством значений. Разберем несколько примеров для того, чтобы лучше понять массивы.
Давайте вычислим, используя массивы, сумму значений в строках для каждого столбца. Для этого сделайте следующее:
1. Введите в диапазон A1:D2 числовые значения.
2. Выделите диапазон A3:D3.
3. В строке формул введите =A1:D1+A2:D2.
4. Нажмите комбинацию клавиш Ctrl+Shift+Enter.
Рис. . – Применение массивов.
Ячейки A3:D3 образуют диапазон массива, а формула массива хранится в каждой ячейке этого диапазона. Массив аргументов - это ссылки на диапазоны A1:D1 и A2:D2
В предыдущем примере формулы массива размещались в горизонтальном одномерном массива. Можно создавать массивы, которые содержат несколько строк и столбцов. Такие массивы называются двумерными ().
Рис. . – Двумерные массивы
1. Перед вводом формулы массива надо выделить ячейку или диапазон ячеек, который будет содержать результаты. Если формула возвращает несколько значений, необходимо выделить диапазон такого же размера и формы, как диапазон с исходными данными.
2. Нажмите клавиши Ctrl+Shift+Enter для фиксации ввода формулы массива. При этом Excel заключит формулу в фигурные скобки в строке формул. НЕ ВВОДИТЕ ФИГУРНЫЕ СКОБКИ ВРУЧНУЮ!
3. В диапазоне нельзя изменять, очищать или перемещать отдельные ячейки, а также вставлять или удалять ячейки. Все ячейки в диапазоне массива надо рассматривать как единое целое и редактировать все их сразу.
4. Для изменения или очистки массива надо выделить весь массив и активизировать строку формул. После изменения формулы надо нажать комбинацию клавиш Ctrl+Shift+Enter.
5. Чтобы переместить содержимое диапазона массива, надо выделить весь массив и в меню "Правка" выбрать команду "Вырезать". Затем выделите новый диапазон и в меню "Правка" выберите команду "Вставить".
6. Вырезать, очищать или редактировать часть массива не разрешается, но можно назначать разные форматы отдельным ячейкам в массиве.
В наборе инструментов не хватает
именно того гаечного ключа или сверла, которые нужны.
Из законов Мэрфи
Форматирование в Excel применяется для облегчения восприятия данных, что играет немаловажную роль в производительности труда.
Для назначения формата:
1. Выделите ячейку (диапазон ячеек).
2. Выберите команду "Формат"-"Ячейки" (Ctrl+1).
3. В появившемся окне диалога (подробно окно будет рассмотрено позже) введите нужные параметры форматирования.
4. Нажмите кнопку "Ок".
Форматированная ячейка сохраняет свой формат, пока к ней не будет применен новый формат или не удален старый. При вводе значения в ячейку к нему применяется уже используемый в ячейке формат.
Для удаления формата:
1. Выделите ячейку (диапазон ячеек).
2. Выберите команду "Правка"-"Очистить"-"Форматы".
3. Для удаления значений в ячейках надо выбрать команду "Все" подменю "Очистить".
Следует учитывать, что при копировании ячейки наряду с ее содержимым копируется и формат ячейки. Таким образом, можно сберечь время, форматируя исходную ячейку до использования команд копирования и вставки.
Наиболее часто используемые команды форматирования вынесены на панель инструментов "Форматирование". Чтобы применить формат с помощью кнопки панели инструментов, выделите ячейку или диапазон ячеек и затем нажмите кнопку мышью. Для удаления формата надо нажать кнопку повторно.
Для быстрого копирования форматов из выделенных ячеек в другие ячейки можно использовать кнопку "Формат по образцу" панели "Форматирование".
Форматирование можно применять к отдельным символам текстового значения в ячейке так же, как и ко всей ячейке. Для этого выделите нужные символы и затем в меню "Формат" выберите команду "Ячейки". Установите нужные атрибуты и нажмите кнопку "Ок". Нажмите клавишу Enter, чтобы увидеть результаты своего труда.
Окно диалога "Формат ячеек" (Ctrl+1) позволяет управлять отображением числовых значений и изменять вывод текста.
Перед открытием окна диалога выделяйте ячейку, содержащую число, которое надо форматировать. В этом случае всегда будет виден результат в поле "Образец". Не следует забывать о различии между хранимыми и отображаемыми значениями. На хранимые числовые или текстовые значения в ячейках форматы на действуют.
Любое введенное текстовое или числовое значение по умолчанию отображается в формате "Общий". При этом оно отображается точно так, как было введено в ячейку за исключением трех случаев:
1. Длинные числовые значения отображаются в экспоненциальной форме записи или округляются.
2. Формат не отображает незначащие нули (456,00 = 456).
3. Десятичная дробь, введенная без числа слева от десятичной запятой, выводится с нулем (,23 = 0,23).
Рис. . – Форматирование ячеек.
Этот формат позволяет выводить числовые значения в виде целых чисел или чисел с фиксированной запятой, а также выделять отрицательные числа с помощью цвета.
Эти форматы аналогичны числовым форматам за исключением того, что вместо разделителя групп разрядов они позволяют управлять выводом символа денежной единицы, который можно выбрать в списке "Обозначение".
Финансовый формат в основном соответствует денежным форматам - можно вывести число с денежной единицей или без нее с заданным количеством десятичных знаков. Основное различие состоит в том, что финансовый формат выводит денежную единицу с выравниванием по левому краю, в то время как само число выравнивается по правому краю ячейки. В результате и денежная единица, и числа вертикально выравниваются в столбце.
Данный формат выводит числа в виде процентов. Десятичная запятая в форматируемом числе сдвигается на два знака вправо, а знак процента выводится в конце числа.
Данный формат выводит дробные значения как обычные, а не десятичные дроби. Эти форматы особенно полезны при воде биржевых цен или измерений.
Экспоненциальные форматы отображают числа в экспоненциальной записи. Данный формат очень удобно использовать для отображения и вывода очень малых или очень больших чисел.
Применение к ячейке текстового формата означает, что значение в этой ячейке должно трактоваться как текст, о чем свидетельствует выравнивание по левому краю ячейки. Не беда, если числовое значение отформатирована как текст, т.к. Excel способен распознавать числовые значения. Ошибка будет, если в ячейке, имеющей текстовый формат, стоит формула. В этом случае формула рассматривается как простой текст, поэтому возможны ошибки.
Эта категория содержит два формата почтовых индексов, формат номера телефона и формат табельного номера. Эти форматы позволяют быстро вводить числа без вода специальных символов.
Условное форматирование позволяет применять форматы к конкретным ячейкам, которые остаются "спящими", пока значения в этих ячейках не достигнут некоторых контрольных значений.
Выделите ячейки, предназначенные для форматирования, затем в меню "Формат" выберите команду "Условное форматирование", перед вами появится окно диалога, представленное ниже ().
Рис. . – Условное форматирование ячеек.
Первое поле со списком в окне диалога "Условное форматирование" позволяет выбрать, к чему должно применяться условие: к значению или самой формуле. Обычно выбирается параметр "Значение", при котором применение формата зависит от значений выделенных ячеек. Параметр "Формула" применяется в тех случаях, когда нужно задать условие, в котором используются данные из невыделенных ячеек, или надо создать сложное условие, включающее в себя несколько критериев. В этом случае во второе поле со списком следует ввести логическую формулу, принимающую значение ИСТИНА или ЛОЖЬ. Второе поле со списком служит для выбора оператора сравнения, используемого для задания условия форматирования. Третье поле используется для задания сравниваемого значения. Если выбран оператор "Между" или "Вне", то в окне диалога появляется дополнительное четвертое поле. В этом случае в третьем и четвертом полях необходимо указать нижнее и верхнее значения.
После задания условия нажмите кнопку "Формат". Откроется окно диалога "Формат ячеек", в котором можно выбрать шрифт, границы и другие атрибуты формата, который должен применяться при выполнении заданного условия.
В приведенном ниже примере () задан следующий формат: цвет шрифта - красный, шрифт - полужирный. Условие: если значение в ячейке превышает "100".
Рис. . – Применение условного форматирования.
Иногда трудно определить, где было применено условное форматирование, Чтобы в текущем листе выделить все ячейки с условным форматированием, выберите команду "Перейти" в меню "Правка", нажмите кнопку "Выделить", затем установите переключатель "Условные форматы" ().
Рис. . – Выделение ячеек с условным форматированием
Чтобы удалить условие форматирования, выделите ячейку или диапазон и затем в меню "Формат" выберите команду "Условное форматирование". Укажите условия, которые хотите удалить, и нажмите "ОК" ().
Рис. . – Удаление условного форматирования.
Сетка является очень важным конструктивным элементом оформления электронной таблицы. Иногда для достижения нужного эффекта бывает необходимо форматировать сетку специальным образом. Excel позволяет объединять ячейки, что придает сетке новые возможности, которые можно использовать для создания более четких форм и отчетов.
При объединении ячеек образуется одна ячейка, размеры которой совпадают с размерами первоначального выделения. Объединенная ячейка получает адрес верхней левой ячейки исходного диапазона. Остальные исходные ячейки практически перестают существовать. Если в формуле встречается ссылка на такую ячейку, она рассматривается как пустая, и в зависимости от типа формулы ссылка может возвратить нулевое или ошибочное значение.
Чтобы объединить ячейки, надо выполнить следующее:
- выделить исходные ячейки;
- в меню "Формат" выбрать команду "Ячейки";
- на вкладке "Выравнивание" окна диалога "Формат ячеек" установить флажок "Объединение ячеек";
- нажать "ОК" (, ).
Рис. . – Объединение ячеек.
Рис. . – Результат объединения.
Если данной командой приходится пользоваться довольно часто, то гораздо удобнее "вытащить" ее на панель инструментов. Для этого надо выбрать меню "Сервис"-"Настройка…", в появившемся окне надо перейти на вкладку "Команды" и в правом окне выбрать категорию "Форматирование" (). В левом окне "Команды", используя полосу прокрутки, найти "Объединение ячеек" и перетащить данную иконку (используя левую кнопку мыши) на панель инструментов "Форматирование" ().
Рис. . – Настройка панели инструментов.
Рис. . – Измененная панель.
Объединение ячеек имеет ряд последствий, и самым наглядным является нарушение сетки - одного из основных атрибутов электронных таблиц. При этом следует учитывать некоторые нюансы:
- если только одна ячейка в выделенном диапазоне является непустой, то при объединении ее содержимое переразмещается в объединенной ячейке. Так, например, при объединении ячеек диапазона А1:В5, где ячейка А2 является непустой, эта ячейка будет перенесена в объединенную ячейку А1;
- если несколько ячеек в выделенном диапазоне содержат значения или формулы, то при объединении сохраняется только содержимое левой верхней ячейки, которое переразмещается в объединенной ячейке. Содержимое остальных ячеек удаляется. Если надо сохранить данные в этих ячейках, то перед объединением следует добавить их в левую верхнюю ячейку или переместить в другое место вне выделения;
- если диапазон объединения содержит формулу, которая переразмещается в объединенной ячейке, то относительные ссылки в ней настраиваются автоматически;
- объединенные ячейки Excel можно копировать, вырезать и вставлять, удалять и перетаскивать, как и обычные ячейки. После копирования или перемещения объединенной ячейки она занимает в новом месте такое же количество ячеек. На месте вырезанной или удаленной объединенной ячейки восстанавливается стандартная структура ячеек;
- при объединении ячеек все границы удаляются, за исключением внешней границы всего выделенного диапазона, а также границы, которая применена к любому краю выделения целиком.
Ошибка? Это не ошибка, это системная функция!
Из законов Мэрфи
Мы уже делали первое знакомство с функциями Excel (см. п. ). Теперь пришло время более тщательно изучить этот мощный инструментарий электронных таблиц.
Функции Excel - это специальные, заранее созданные формулы, которые позволяют легко и быстро выполнять сложные вычисления. Их можно сравнить со специальными клавишами на калькуляторах, предназначенных для вычисления квадратных корней, логарифмов и проч.
Excel имеет несколько сотен встроенных функций, которые выполняют широкий спектр различных вычислений. Некоторые функции являются эквивалентами длинных математических формул, которые можно сделать самому. А некоторые функции в виде формул реализовать невозможно.
Функции состоят из двух частей: имени функции и одного или нескольких аргументов. Имя функции, например СУММ, - описывает операцию, которую эта функция выполняет. Аргументы задают значения или ячейки, используемые функцией. В формуле, приведенной ниже: СУММ - имя функции; В1:В5 - аргумент. Данная формула суммирует числа в ячейках В1, В2, В3, В4, В5.
=СУММ(В1:В5)
Знак равенства в начале формулы означает, что введена именно формула, а не текст. Если знак равенства будет отсутствовать, то Excel воспримет ввод просто как текст.
Аргумент функции заключен в круглые скобки. Открывающая скобка отмечает начало аргумента и ставится сразу после имени функции. В случае ввода пробела или другого символа между именем и открывающей скобкой в ячейке будет отображено ошибочное значение #ИМЯ? Некоторые функции не имеют аргументов. Даже в этом случае функция должна содержать круглые скобки:
=С5*ПИ()
При использовании в функции нескольких аргументов они отделяются один от другого точкой с запятой. Например, следующая формула указывает, что необходимо перемножить числа в ячейках А1, А3, А6:
=ПРОИЗВЕД(А1;А3;А6)
В функции можно использовать до 30 аргументов, если при этом общая длина формулы не превосходит 1024 символов. Однако любой аргумент может быть диапазоном, содержащим произвольное число ячеек листа. Например:
=СУММ(А2:А5;В4:В8)
Указанные в ссылке ячейки в свою очередь могут содержать формулы, которые ссылаются на другие ячейки или диапазоны. Используя аргументы, можно легко создавать длинные цепочки формул для выполнения сложных операций.
В приведенных ранее примерах все аргументы были ссылками на ячейки или диапазоны. Но в качестве аргументов можно также использовать числовые, текстовые и логические значения, имена диапазонов, массивы и ошибочные значения. Некоторые функции возвращают значения этих типов, и их в дальнейшем можно использовать в качестве аргументов в других функциях.
Аргументы функции могут быть числовыми. Например, функция СУММ в следующей формуле суммирует числа 24, 987, 49:
=СУММ(24;987;49)
В качестве аргумента функции могут использоваться текстовые значения. Например:
=ТЕКСТ(ТДАТА();"Д МММ ГГГГ")
В этой формуле второй аргумент функции ТЕКСТ является текстовым и задает шаблон для преобразования десятичного значения даты, возвращаемого функцией ТДАТА(NOW), в строку символов. Текстовый аргумент может быть строкой символов, заключенной в двойные кавычки, или ссылкой на ячейку, которая содержит текст.
Аргументы ряда функций могут принимать только логические значения ИСТИНА или ЛОЖЬ. Логическое выражение возвращает значение ИСТИНА или ЛОЖЬ в ячейку или формулу, содержащую это выражение. Например:
=ЕСЛИ(А1=ИСТИНА;"Повышение";"Понижение")&" цены"
В качестве аргумента функции можно указать имя диапазона. Например, если диапазону ячеек А1:А5 присвоено имя "Дебет" (Вставка-Имя-Присвоить), то для вычисления суммы чисел в ячейках с А1 по А5 можно использовать формулу
=СУММ(Дебет)
В одной функции можно использовать аргументы различных типов. Например:
=СРЗНАЧ(Дебет;С5;2*8)
Можно вводить функции в рабочем листе прямо с клавиатуры или с помощью команды "Функция" меню "Вставка". При вводе функции с клавиатуры лучше использовать строчные буквы. Когда ввод функции закончен, Excel изменит буквы в имени функции на прописные, если оно было введено правильно. Если буквы не изменяются, значит, имя функции введено неверно.
Если вы выделите ячейку и выберите в меню "Вставка" команду "Функция", Excel выведет окно диалога "Мастер функций" (). Немного быстрее можно этого добиться, нажав клавишу со значком функции в строке формул.
Рис. . – Выбор имени функции
Открыть это окно можно также с помощью кнопки "Вставка функции" на стандартной панели инструментов. В окне сначала выберите категорию в списке "Категория" и затем в алфавитном списке "Функция" укажите нужную функцию.
Excel введет знак равенства, имя функции и пару круглых скобок. Затем Excel откроет второе окно диалога мастера функций ().
Рис. . – Окно ввода аргументов функции
Второе окно диалога мастера функций содержит по одному полю для каждого аргумента выбранной функции. Если функция имеет переменное число аргументов, это окно диалога при воде дополнительных аргументов расширяется. Описание аргумента, поле которого содержит точку вставки, выводится в нижней части окна диалога.
Справа от каждого поля аргумента отображается его текущее значение. Это очень удобно, когда вы используете ссылки или имена. Текущее значение функции отображается внизу окна диалога.
Нажмите кнопку "ОК" и созданная функция появится в строке формул.
Здесь приведены наиболее часто используемые математические функции Excel (краткая справка). Дополнительную информацию о функциях можно найти в окне диалога мастера функций, а также в справочной системе Excel. Кроме того, множество математических функций включено в надстройку "Пакет анализа". В скобках приведены названия функций для английской версии Excel.
Функция СУММ (SUM) суммирует множество чисел. Эта функция имеет следующий синтаксис:
=СУММ(числа)
Аргумент числа может включать до 30 элементов, каждый из которых может быть числом, формулой, диапазоном или ссылкой на ячейку, содержащую или возвращающую числовое значение. Функция СУММ игнорирует аргументы, которые ссылаются на пустые ячейки, текстовые или логические значения. Аргументы не обязательно должны образовывать непрерывные диапазоны ячеек. Например, чтобы получить сумму чисел в ячейках А2, В10 и в ячейках от С5 до К12, введите каждую ссылку как отдельный аргумент:
=СУММ(А2;В10;С5:К12)
Функция ОКРУГЛ (ROUND) округляет число, задаваемое ее аргументом, до указанного количества десятичных разрядов и имеет следующий синтаксис:
=ОКРУГЛ(число;количество_цифр)
Аргумент число может быть числом, ссылкой на ячейку, в которой содержится число, или формулой, возвращающей числовое значение. Аргумент количство_цифр, который может быть любым положительным или отрицательным целым числом, определяет, сколько цифр будет округляться. Задание отрицательного аргумента количество_цифр округляет до указанного количества разрядов слева от десятичной запятой, а задание аргумента количество_цифр равным 0 округляет до ближайшего целого числа. Excel цифры, которые меньше 5, с недостатком (вниз), а цифры, которые больше или равны 5, с избытком (вверх).
Функции ОКРУГЛВНИЗ (ROUNDDOWN) и ОКРУГЛВВЕРХ (ROUNDUP) имеют такой же синтаксис, как и функция ОКРУГЛ. Они округляют значения вниз (с недостатком) или вверх (с избытком).
Для выполнения операций округления можно использовать функции ЧЁТН (EVEN) и НЕЧЁТ (ODD). Функция ЧЁТН округляет число вверх до ближайшего четного целого числа. Функция НЕЧЁТ округляет число вверх до ближайшего нечетного целого числа. Отрицательные числа округляются не вверх, а вниз. Функции имеют следующий синтаксис:
=ЧЁТН(число)
=НЕЧЁТ(число)
Функции ОКРВНИЗ (FLOOR) и ОКРВВЕРХ (CEILING) тоже можно использовать для выполнения операций округления. Функция ОКРВНИЗ округляет число вниз до ближайшего кратного для заданного множителя, а функция ОКРВВЕРХ округляет число вверх до ближайшего кратного для заданного множителя. Эти функции имеют следующий синтаксис:
=ОКРВНИЗ(число;множитель)
=ОКРВВЕРХ(число;множитель)
Значения число и множитель должны быть числовыми и иметь один и тот же знак. Если они имеют различные знаки, то будет выдана ошибка.
Функция ЦЕЛОЕ (INT) округляет число вниз до ближайшего целого и имеет следующий синтаксис:
=ЦЕЛОЕ(число)
Аргумент - число - это число, для которого надо найти следующее наименьшее целое число.
Рассмотрим формулу:
=ЦЕЛОЕ(10,0001)
Эта формула возвратит значение 10, как и следующая:
=ЦЕЛОЕ(10,999)
Функция ОТБР (TRUNC) отбрасывает все цифры справа от десятичной запятой независимо от знака числа. Необязательный аргумент количество_цифр задает позицию, после которой производится усечение. Функция имеет следующий синтаксис:
=ОТБР(число;количество_цифр)
Если второй аргумент опущен, он принимается равным нулю. Следующая формула возвращает значение 25:
=ОТБР(25,490)
Функции ОКРУГЛ, ЦЕЛОЕ и ОТБР удаляют ненужные десятичные знаки, но работают они различно. Функция ОКРУГЛ округляет вверх или вниз до заданного числа десятичных знаков. Функция ЦЕЛОЕ округляет вниз до ближайшего целого числа, а функция ОТБР отбрасывает десятичные разряды без округления. Основное различие между функциями ЦЕЛОЕ и ОТБР проявляется в обращении с отрицательными значениями. Если вы используете значение -10,900009 в функции ЦЕЛОЕ, результат оказывается равен -11, но при использовании этого же значения в функции ОТБР результат будет равен -10.
Функция СЛЧИС (RAND) генерирует случайные числа, равномерно распределенные между 0 и 1, и имеет следующий синтаксис:
=СЛЧИС()
Функция СЛЧИС является одной из функций EXCEL, которые не имеют аргументов. Как и для всех функций, у которых отсутствуют аргументы, после имени функции необходимо вводить круглые скобки.
Значение функции СЛЧИС изменяется при каждом пересчете листа. Если установлено автоматическое обновление вычислений, значение функции СЛЧИС изменяется каждый раз при воде данных в этом листе.
Функция СЛУЧМЕЖДУ (RANDBETWEEN), которая доступна, если установлена надстройка "Пакет анализа", предоставляет больше возможностей, чем СЛЧИС. Для функции СЛУЧМЕЖДУ можно задать интервал генерируемых случайных целочисленных значений.
Синтаксис функции:
=СЛУЧМЕЖДУ(начало;конец)
Аргумент начало задает наименьшее число, которое может возвратить любое целое число от 111 до 529 (включая и оба эти значения):
=СЛУЧМЕЖДУ(111;529)
Функция ПРОИЗВЕД (PRODUCT) перемножает все числа, задаваемые ее аргументами, и имеет следующий синтаксис:
=ПРОИЗВЕД(число1;число2...)
Эта функция может иметь до 30 аргументов. Excel игнорирует любые пустые ячейки, текстовые и логические значения.
Функция ОСТАТ (MOD) возвращает остаток от деления и имеет следующий синтаксис:
=ОСТАТ(число;делитель)
Значение функции ОСТАТ - это остаток, получаемый при делении аргумента число на делитель. Например, следующая функция возвратит значение 1, то есть остаток, получаемый при делении 19 на 14:
=ОСТАТ(19;14)
Если число меньше чем делитель, то значение функции равно аргументу число. Например, следующая функция возвратит число 25:
=ОСТАТ(25;40)
Если число точно делится на делитель, функция возвращает 0. Если делитель равен 0, функция ОСТАТ возвращает ошибочное значение.
Функция КОРЕНЬ (SQRT) возвращает положительный квадратный корень из числа и имеет следующий синтаксис:
=КОРЕНЬ(число)
Аргумент число должен быть положительным числом. Например, следующая функция возвращает значение 4:
КОРЕНЬ(16)
Если число отрицательное, КОРЕНЬ возвращает ошибочное значение.
Функция ЧИСЛОКОМБ (COMBIN) определяет количество возможных комбинаций или групп для заданного числа элементов. Эта функция имеет следующий синтаксис:
=ЧИСЛОКОМБ(число;число_выбранных)
Аргумент число - это общее количество элементов, а число_выбранных - это количество элементов в каждой комбинации. Например, для определения количества команд с 5 игроками, которые могут быть образованы из 10 игроков, используется формула:
=ЧИСЛОКОМБ(10;5)
Результат будет равен 252. Т.е., может быть образовано 252 команды.
Функция ЕЧИСЛО (ISNUMBER) определяет, является ли значение числом, и имеет следующий синтаксис:
=ЕЧИСЛО(значение)
Пусть вы хотите узнать, является ли значение в ячейке А1 числом. Следующая формула возвращает значение ИСТИНА, если ячейка А1 содержит число или формулу, возвращающую число; в противном случае она возвращает ЛОЖЬ:
=ЕЧИСЛО(А1)
Функция LOG возвращает логарифм положительного числа по заданному основанию. Синтаксис:
=LOG(число;основание)
Если аргумент основание не указан, то Excel примет его равным 10.
Функция LN возвращает натуральный логарифм положительного числа, указанного в качестве аргумента. Эта функция имеет следующий синтаксис:
=LN(число)
Функция EXP вычисляет значение константы e, возведенной в заданную степень. Эта функция имеет следующий синтаксис:
EXP(число)
Функция EXP является обратной по отношению к LN. Например, пусть ячейка А2 содержит формулу:
=LN(10)
Тогда следующая формула возвращает значение 10:
=EXP(А2)
Функция ПИ (PI) возвращает значение константы пи с точностью до 14 десятичных знаков. Синтаксис:
=ПИ()
Тригонометрические функции используют углы, выраженные в радианах, а не в градусах. Измерение углов в радианах основывается на константе пи и при этом 180 градусов равны пи радиан. Excel предоставляет две функции, РАДИАНЫ (RADIANS) и ГРАДУСЫ (DEGREES), чтобы облегчить работу с тригонометрическими функциями.
Вы можете преобразовать радианы в градусы, используя функцию ГРАДУСЫ. Синтаксис:
=ГРАДУСЫ(угол)
Здесь - угол - это число, представляющее собой угол, измеренный в радианах. Для преобразования градусов в радианы используется функция РАДИАНЫ, которая имеет следующий синтаксис:
=РАДИАНЫ(угол)
Здесь - угол - это число, представляющее собой угол, измеренный в градусах. Например, следующая формула возвращает значение 180:
=ГРАДУСЫ(3,14159)
В то же время следующая формула возвращает значение 3,14159:
=РАДИАНЫ(180)
Функция SIN возвращает синус угла и имеет следующий синтаксис:
=SIN(число)
Здесь число - угол в радианах.
Функция COS возвращает косинус угла и имеет следующий синтаксис:
=COS(число)
Здесь число - угол в радианах.
Функция TAN возвращает тангенс угла и имеет следующий синтаксис:
=TAN(число)
Здесь число - угол в радианах.
Здесь рассмотрены наиболее часто используемые текстовые функции Excel (краткая справка). Дополнительную информацию о функциях можно найти в окне диалога мастера функций, а также в справочной системе Excel.
Текстовые функции преобразуют числовые текстовые значения в числа и числовые значения в строки символов (текстовые строки), а также позволяют выполнять над строками символов различные операции.
Функция ТЕКСТ (TEXT) преобразует число в текстовую строку с заданным форматом. Синтаксис:
=ТЕКСТ(значение;формат)
Аргумент значение может быть любым числом, формулой или ссылкой на ячейку. Аргумент формат определяет, в каком виде отображается возвращаемая строка. Для задания необходимого формата можно использовать любой из символов форматирования за исключением звездочки. Использование формата Общий не допускается. Например, следующая формула возвращает текстовую строку 25,25:
=ТЕКСТ(101/4;"0,00")
Функция РУБЛЬ (DOLLAR) преобразует число в строку. Однако РУБЛЬ возвращает строку в денежном формате с заданным числом десятичных знаков. Синтаксис:
=РУБЛЬ(число;число_знаков)
При этом Excel при необходимости округляет число. Если аргумент число_знаков опущен, Excel использует два десятичных знака, а если значение этого аргумента отрицательное, то возвращаемое значение округляется слева от десятичной запятой.
Функция ДЛСТР (LEN) возвращает количество символов в текстовой строке и имеет следующий синтаксис:
=ДЛСТР(текст)
Аргумент текст должен быть строкой символов, заключенной в двойные кавычки, или ссылкой на ячейку. Например, следующая формула возвращает значение 6:
=ДЛСТР("голова")
Функция ДЛСТР возвращает длину отображаемого текста или значения, а не хранимого значения ячейки. Кроме того, она игнорирует незначащие нули.
Любой компьютер для представления символов использует числовые коды. Наиболее распространенной системой кодировки символов является ASCII. В этой системе цифры, буквы и другие символы представлены числами от 0 до 127 (255). Функции СИМВОЛ (CHAR) и КОДСИМВ (CODE) как раз и имеют дело с кодами ASCII. Функция СИМВОЛ возвращает символ, который соответствует заданному числовому коду ASCII, а функция КОДСИМВ возвращает код ASCII для первого символа ее аргумента. Синтаксис функций:
=СИМВОЛ(число)
=КОДСИМВ(текст)
Если в качестве аргумента текст вводится символ, обязательно надо заключить его в двойные кавычки: в противном случае Excel возвратит ошибочное значение.
Часто начальные и конечные пробелы не позволяют правильно отсортировать значения в рабочем листе или базе данных. Если вы используете текстовые функции для работы с текстами рабочего листа, лишние пробелы могут мешать правильной работе формул. Функция СЖПРОБЕЛЫ (TRIM) удаляет начальные и конечные пробелы из строки, оставляя только по одному пробелу между словами. Синтаксис:
=СЖПРОБЕЛЫ(текст)
Функция ПЕЧСИМВ (CLEAN) аналогична функции СЖПРОБЕЛЫ за исключением того, что она удаляет все непечатаемые символы. Функция ПЕЧСИМВ особенно полезна при импорте данных из других программ, поскольку некоторые импортированные значения могут содержать непечатаемые символы. Эти символы могут проявляться на рабочих листах в виде небольших квадратов или вертикальных черточек. Функция ПЕЧСИМВ позволяет удалить непечатаемые символы из таких данных. Синтаксис:
=ПЕЧСИМВ(текст)
Функция СОВПАД (EXACT) сравнивает две строки текста на полную идентичность с учетом регистра букв. Различие в форматировании игнорируется. Синтаксис:
=СОВПАД(текст1;текст2)
Если аргументы текст1 и текст2 идентичны с учетом регистра букв, функция возвращает значение ИСТИНА, в противном случае - ЛОЖЬ. Аргументы текст1 и текст2 должны быть строками символов, заключенными в двойные кавычки, или ссылками на ячейки, в которых содержится текст.
В Excel имеются три функции, позволяющие изменять регистр букв в текстовых строках: ПРОПИСН (UPPER), СТРОЧН (LOWER) и ПРОПНАЧ (PROPER). Функция ПРОПИСН преобразует все буквы текстовой строки в прописные, а СТРОЧН - в строчные. Функция ПРОПНАЧ заменяет прописными первую букву в каждом слове и все буквы, следующие непосредственно за символами, отличными от букв; все остальные буквы преобразуются в строчные. Эти функции имеют следующий синтаксис:
=ПРОПИСН(текст)
=СТРОЧН(текст)
=ПРОПНАЧ(текст)
При работе с уже существующими данными довольно часто возникает ситуация, когда нужно модифицировать сами исходные значения, к которым применяются текстовые функции. Можно ввести функцию в те же самые ячейки, где находятся эти значения, поскольку введенные формулы заменят их. Но можно создать временные формулы с текстовой функцией в свободных ячейках в той же самой строке и скопируйте результат в буфер обмена. Чтобы заменить первоначальные значения модифицированными, выделите исходные ячейки с текстом, в меню "Правка" выберите команду "Специальная вставка", установите переключатель "Значения" и нажмите кнопку ОК. После этого можно удалить временные формулы.
Функции ЕТЕКСТ (ISTEXT) и ЕНЕТЕКСТ (ISNOTEXT) проверяют, является ли значение текстовым. Синтаксис:
=ЕТЕКСТ(значение)
=ЕНЕТЕКСТ(значение)
Предположим, надо определить, является ли значение в ячейке А1 текстом. Если в ячейке А1 находится текст или формула, которая возвращает текст, можно использовать формулу:
=ЕТЕКСТ(А1)
В этом случае Excel возвращает логическое значение ИСТИНА. Аналогично, если использовать формулу:
ЕНЕТЕКСТ(А1)
Excel возвращает логическое значение ЛОЖЬ.
Следующие функции находят и возвращают части текстовых строк или составляют большие строки из небольших: НАЙТИ (FIND), ПОИСК (SEARCH), ПРАВСИМВ (RIGHT), ЛЕВСИМВ (LEFT), ПСТР (MID), ПОДСТАВИТЬ (SUBSTITUTE), ПОВТОР (REPT), ЗАМЕНИТЬ (REPLACE), СЦЕПИТЬ (CONCATENATE).
Функции НАЙТИ (FIND) и ПОИСК (SEARCH) используются для определения позиции одной текстовой строки в другой. Обе функции возвращают номер символа, с которого начинается первое вхождение искомой строки. Эти две функции работают одинаково за исключением того, что функция НАЙТИ учитывает регистр букв, а функция ПОИСК допускает использование символов шаблона. Функции имеют следующий синтаксис:
=НАЙТИ(искомый_текст;просматриваемый_текст;
нач_позиция)
=ПОИСК(искомый_текст;просматриваемый_текст;
нач_позиция)
Аргумент искомый_текст задает текстовую строку, которую надо найти, а аргумент просматриваемый_текст - текст, в котором производится поиск. Любым из этих аргументов может быть строка символов, заключенная в двойные кавычки, или ссылка на ячейку. Необязательный аргумент нач_позиция задает позицию в просматриваемом тексте, с которой начинается поиск. Аргумент нач_позиция следует использовать в том случае, когда просматриваемый_текст содержит несколько вхождений искомого текста. Если данный аргумент опущен, Excel возвращает позицию первого вхождения.
Эти функции возвращают ошибочное значение, когда искомый_текст не содержится в просматриваемом тексте, либо нач_позиция меньши или равна нулю, либо нач_позиция превышает количество символов в просматриваемом тексте, либо нач_позиция больше позиции последнего вхождения искомого текста.
Например, чтобы определить позицию буквы "ж" в строке "Гаражные ворота", надо использовать формулу:
=НАЙТИ("ж";"Гаражные ворота")
Эта формула возвращает 5.
Если точная последовательность символов искомого текста неизвестна, можно использовать функцию ПОИСК и включить в строку искомый_текст символы шаблона: знак вопроса (?) и звездочку (*). Знак вопроса соответствует одному произвольно набранному символу, а звездочка заменяет любую последовательность символов в указанной позиции. Например, чтобы найти позицию имен Анатолий, Алексей, Акакий в тексте, находящемся в ячейке А1, надо использовать формулу:
=ПОИСК("А*й";А1)
Функция ПРАВСИМВ (RIGHT) возвращает крайние правые символы строки аргумента, в то время как функция ЛЕВСИМВ (LEFT) возвращает первые (левые) символы. Синтаксис:
=ПРАВСИМВ(текст;количество_символов)
=ЛЕВСИМВ(текст;количество_символов)
Аргумент количество_символов задает число символов, извлекаемых из аргумента текст. Эти функции учитывают пробелы и поэтому, если аргумент текст содержит пробелы в начале или конце строки, в аргументах функций следует использовать функцию СЖПРОБЕЛЫ.
Аргумент количестов_символов должен быть больше или равен нулю. Если этот аргумент опускается, Excel считает его равным 1. Если количество_символов больше числа символов в аргументе текст, то возвращается весь аргумент.
Функция ПСТР (MID) возвращает заданное число символов из строки текста, начиная с указанной позиции. Эта функция имеет следующий синтаксис:
=ПСТР(текст;нач_позиция;количество_символов)
Аргумент текст - это текстовая строка, содержащая извлекаемые символы, нач_позиция - это позиция первого символа, извлекаемого из текста (относительно начала строки), а количество_символов - это число извлекаемых символов.
Эти две функции заменяют символы в тексте. Функция ЗАМЕНИТЬ (REPLACE) замещает часть текстовой строки другой текстовой строкой и имеет синтаксис:
=ЗАМЕНИТЬ(старый_текст;нач_позиция;
количество_символов;новый_текст)
Аргумент старый_текст - это текстовая строка, а которой надо заменить символы. Следующие два аргумента задают символы, которые нужно заменить (относительно начала строки). Аргумент новый_текст задает вставляемую текстовую строку.
Например, ячейка А2 содержит текст "Вася Иванов". Чтобы поместить этот же текст в ячейку А3, заменив имя, надо в ячейку А3 вставить следующую функцию:
=ЗАМЕНИТЬ(А2;1;5;"Петя")
В функции ПОДСТАВИТЬ (SUBSTITUTE) начальная позиция и число заменяемых символов не задаются, а явно указывается замещаемый текст. Функция ПОДСТАВИТЬ имеет следующий синтаксис:
=ПОДСТАВИТЬ(текст;старый_текст;новый_текст;номер_вхождения)
Аргумент номер_вхождения является необязательным. Он предписывает Excel заменить только заданное вхождение строки старый_текст.
Например, ячейка А1 содержит текст "Ноль меньше восьми". Надо заменить слово "ноль" на "нуль".
=ПОДСТАВИТЬ(А1;"о";"у";1)
Число 1 в этой формуле указывает, что надо изменить только первое "о" в строке ячейки А1. Если аргумент номер_вхождения опущен, Excel заменяет все вхождения строки старый_текст на строку новый_текст.
Функция ПОВТОР (REPT) позволяет заполнить ячейку строкой символов, повторенной заданное количество раз. Синтаксис:
=ПОВТОР(текст;число_повторений)
Аргумент текст - это размноженная строка символов, заключенная в кавычки. Аргумент число_повторений указывает, сколько раз нужно повторить текст. Если аргумент число_повторений равен 0, функция ПОВТОР оставляет ячейку пустой, а если он не является целым числом, эта функция отбрасывает десятичные знаки после запятой.
Функция СЦЕПИТЬ (CONCATENATE) является эквивалентом текстового оператора & и используется для объединения строк. Синтаксис:
=СЦЕПИТЬ(текст1;текст2;...)
В функции можно использовать до 30 аргументов.
Например, ячейка А5 содержит текст "первое полугодие", следующая формула возвращает текст "Всего за первое полугодие":
=СЦЕПИТЬ("Всего за ";А5)
Логические выражения используются для записи условий, в которых сравниваются числа, функции, формулы, текстовые или логические значения. Любое логическое выражение должно содержать по крайней мере один оператор сравнения, который определяет отношение между элементами логического выражения. Ниже представлен список операторов сравнения Excel:
= Равно
> Больше
< Меньше
>= Больше или равно
<= Меньше или равно
<> Не равно
Результатом логического выражения является логическое значение ИСТИНА (1) или логическое значение ЛОЖЬ (0).
Функция ЕСЛИ (IF) имеет следующий синтаксис:
=ЕСЛИ(логическое_выражение;значение_если_истина;
значение_если_ложь)
Следующая формула возвращает значение 10, если значение в ячейке А1 больше 3, а в противном случае - 20:
=ЕСЛИ(А1>3;10;20)
В качестве аргументов функции ЕСЛИ можно использовать другие функции. В функции ЕСЛИ можно использовать текстовые аргументы. Например:
=ЕСЛИ(А1>=4;"Зачет сдал";"Зачет не сдал")
Можно использовать текстовые аргументы в функции ЕСЛИ, чтобы при невыполнении условия она возвращала пустую строку вместо 0.
Например:
=ЕСЛИ(СУММ(А1:А3)=30;А10;"")
Аргумент логическое_выражение функции ЕСЛИ может содержать текстовое значение. Например:
=ЕСЛИ(А1="Динамо";10;290)
Эта формула возвращает значение 10, если ячейка А1 содержит строку "Динамо", и 290, если в ней находится любое другое значение. Совпадение между сравниваемыми текстовыми значениями должно быть точным, но без учета регистра.
Функции И (AND), ИЛИ (OR), НЕ (NOT) - позволяют создавать сложные логические выражения. Эти функции работают в сочетании с простыми операторами сравнения. Функции И и ИЛИ могут иметь до 30 логических аргументов и имеют синтаксис:
=И(логическое_значение1;логическое_значение2...)
=ИЛИ(логическое_значение1;логическое_значение2...)
Функция НЕ имеет только один аргумент и следующий синтаксис:
=НЕ(логическое_значение)
Аргументы функций И, ИЛИ, НЕ могут быть логическими выражениями, массивами или ссылками на ячейки, содержащие логические значения.
Приведем пример. Пусть Excel возвращает текст "Прошел", если ученик имеет средний балл более 4 (ячейка А2), и пропуск занятий меньше 3 (ячейка А3). Формула примет вид:
=ЕСЛИ(И(А2>4;А3<3);"Прошел";"Не прошел")
Несмотря на то, что функция ИЛИ имеет те же аргументы, что и И, результаты получаются совершенно различными. Так, если в предыдущей формуле заменить функцию И на ИЛИ, то ученик будет проходить, если выполняется хотя бы одно из условий (средний балл более 4 или пропуски занятий менее 3). Таким образом, функция ИЛИ возвращает логическое значение ИСТИНА, если хотя бы одно из логических выражений истинно, а функция И возвращает логическое значение ИСТИНА, только если все логические выражения истинны.
Функция НЕ меняет значение своего аргумента на противоположное логическое значение и обычно используется в сочетании с другими функциями. Эта функция возвращает логическое значение ИСТИНА, если аргумент имеет значение ЛОЖЬ, и логическое значение ЛОЖЬ, если аргумент имеет значение ИСТИНА.
Иногда бывает очень трудно решить логическую задачу только с помощью операторов сравнения и функций И, ИЛИ, НЕ. В этих случаях можно использовать вложенные функции ЕСЛИ. Например, в следующей формуле используются три функции ЕСЛИ:
=ЕСЛИ(А1=100;"Всегда";ЕСЛИ(И(А1>=80;А1<100);
"Обычно";ЕСЛИ(И(А1>=60;А1<80);"Иногда";"Никогда")))
Если значение в ячейке А1 является целым числом, формула читается следующим образом: "Если значение в ячейке А1 равно 100, возвратить строку "Всегда". В противном случае, если значение в ячейке А1 находится между 80 и 100, возвратить "Обычно". В противном случае, если значение в ячейке А1 находится между 60 и 80, возвратить строку "Иногда". И, если ни одно из этих условий не выполняется, возвратить строку "Никогда". Всего допускается до 7 уровней вложения функций ЕСЛИ.
Функции ИСТИНА (TRUE) и ЛОЖЬ (FALSE) предоставляют альтернативный способ записи логических значений ИСТИНА и ЛОЖЬ. Эти функции не имеют аргументов и выглядят следующим образом:
=ИСТИНА()
=ЛОЖЬ()
Например, ячейка А1 содержит логическое выражение. Тогда следующая функция возвратить значение "Проходите", если выражение в ячейке А1 имеет значение ИСТИНА:
=ЕСЛИ(А1=ИСТИНА();"Проходите";"Стоп")
В противном случае формула возвратит "Стоп".
Если нужно определить, является ли ячейка пустой, можно использовать функцию ЕПУСТО (ISBLANK), которая имеет следующий синтаксис:
=ЕПУСТО(значение)
Аргумент значение может быть ссылкой на ячейку или диапазон. Если значение ссылается на пустую ячейку или диапазон, функция возвращает логическое значение ИСТИНА, в противном случае ЛОЖЬ.
Никогда не рисуйте то, что можно скопировать.
Никогда не копируйте то, что можно обвести.
Никогда не обводите то, что можно вырезать и наклеить.
Из законов Мэрфи.
В Excel термин "диаграмма" используется для обозначения всех видов графического представления числовых данных. Построение графического изображения производится на основе ряда данных. Так называют группу ячеек с данными в пределах отдельной строки или столбца. На одной диаграмме можно отображать несколько рядов данных.
Диаграмма представляет собой вставной объект, внедренный на один из листов рабочей книги. Она может располагаться на том же листе, на котором находятся данные, или на любом другом листе (часто для отображения диаграммы отводят отдельный лист). Диаграмма сохраняет связь с данными, на основе которых она построена, и при обновлении этих данных немедленно изменяет свой вид.
Для построения диаграммы обычно используют Мастер диаграмм, запускаемый щелчком на кнопке "Мастер диаграмм" на стандартной панели инструментов Часто удобно заранее выделить область, содержащую данные, которые будут отображаться на диаграмме, но задать эту информацию можно и в ходе работы мастера.
На первом этапе работы мастера выбирают форму диаграммы. Доступные формы перечислены в списке "Тип" на вкладке "Стандартные". Для выбранного типа диаграммы справа указывается несколько вариантов представления данных (палитра "Вид"), из которых следует выбрать наиболее подходящий. На вкладке "Нестандартные" отображается набор полностью сформированных типов диаграмм с готовым форматированием. После задания формы диаграммы следует щелкнуть на кнопке "Далее".
Второй этап работы мастера служит для выбора данных, по которым будет строиться диаграмма. Если диапазон данных был выбран заранее, то в области предварительного просмотра в верхней части окна мастера появится приблизительное отображение будущей диаграммы. Если данные образуют единый прямоугольный диапазон, то их удобно выбирать при помощи вкладки "Диапазон данных". Если данные не образуют единой группы, то информацию для обрисовки отдельных рядов данных задают на вкладке "Ряд". Предварительное представление диаграммы автоматически обновляется при изменении набора отображаемых данных.
Третий этап работы мастера (после щелчка на кнопке "Далее") состоит в выборе оформления диаграммы. На вкладках окна мастера задаются:
• название диаграммы, подписи осей (вкладка "Заголовки");
• отображение и маркировка осей координат (вкладка "Оси");
• отображение сетки линий, параллельных осям координат (вкладка "Линии сетки");
• описание построенных графиков (вкладка "Легенда");
• отображение надписей, соответствующих отдельным элементам данных на графике (вкладка "Подписи данных");
• представление данных, использованных при построении графика, в виде таблицы (вкладка "Таблица данных").
В зависимости от типа диаграммы некоторые из перечисленных вкладок могут отсутствовать.
На последнем этапе работы мастера (после щелчка на кнопке "Далее") указывается, следует ли использовать для размещения диаграммы новый рабочий лист или один из имеющихся. Обычно этот выбор важен только для последующей печати документа, содержащего диаграмму. После щелчка на кнопке "Готово" диаграмма строится автоматически и вставляется на указанный рабочий лист.
Готовую диаграмму можно изменить. Она состоит из набора отдельных элементов, таких, как сами графики (ряды данных), оси координат, заголовок диаграммы, область построения и прочее при щелчке на элементе диаграммы он выделяется маркерами, а при наведении на него указателя мыши — описывается всплывающей подсказкой Открыть диалоговое окно для форматирования элемента диаграммы можно через меню "Формат" (для выделенного элемента) или через контекстное меню (команда "Формат"). Различные вкладки открывшегося диалогового окна позволяют изменять параметры отображения выбранного элемента данных. Если требуется внести в диаграмму существенные изменения, следует вновь воспользоваться мастером диаграмм. Для этого следует открыть рабочий лист с диаграммой или выбрать диаграмму, внедренную в рабочий лист с данными. Запустив мастер диаграмм, можно изменить текущие параметры, которые рассматриваются в окнах мастера, как заданные по умолчанию.
Чтобы удалить диаграмму, можно удалить рабочий лист, на котором она расположена ("Правка" – "Удалить лист"), или выбрать диаграмму, внедренную в рабочий лист с данными, и нажать клавишу Del.
Рассмотрим диаграммы различных типов: круговые, столбчатые, линейные, нормированные и диаграммы площадей. На самом деле типов диаграмм гораздо больше, но эти — самые распространенные.
Круговая диаграмма служит для сравнения нескольких величин в одной точке. Она особенно полезна, если величины в сумме составляют нечто целое (100%).
|
A |
B |
|
|
1 |
Блокноты |
2 |
|
2 |
Карандаши |
13 |
|
3 |
Тетради |
45 |
Пример 1. Студент на каникулах торгует канцелярскими товарами: блокнотами, карандашами и тетрадями. Будем считать, что за день он продал 2 блокнота, 13 карандашей и 45 тетрадей. Построить круговую диаграмму, показывающую, какой товар покупался в течение дня чаще всего.
Выделим блок клеток А1:ВЗ, содержащий данные для графической обработки. Данные располагаются в столбцах. Первый столбец А1:АЗ выделенного блока является столбцом названий секторов; второй столбец В1:ВЗ выделенного блока содержит числовые данные диаграммы. Круговая диаграмма будет выглядеть следующим образом ():
Рис. . – Круговая диаграмма
Столбчатая диаграмма служит для сравнения нескольких величин в нескольких точках.
Пример 2. Студентка торговала газетами в течение недели и продавала следующее количество газет в день:
|
А |
В |
С |
D |
Е |
F |
G |
|
|
1 |
Пн |
Вт |
Cр |
Чт |
Пт |
Сб |
Bc |
|
2 |
20 |
25 |
32 |
30 |
23 |
30 |
20 |
Столбчатые диаграммы (как и следует из названия) состоят из столбиков. Высота столбиков определяется значениями сравниваемых величин. В нашем случае высота столбика будет определяться количеством газет, которое девушка продавала за день. Каждый столбик привязан к некоторой опорной точке. В нашем случае опорная точка будет соответствовать одному дню недели.
Выделим блок клеток A1-G2, содержащий данные для графической обработки. Данные располагаются в строках. Первая строка A1:G1 выделенного блока является строкой Х координат (опорные точки); вторая строка A2.G2 выделенного блока содержит Y координаты (высоты столбиков) диаграммы.
Укажем заголовок диаграммы: “Девушка торгует газетами”. Столбчатая диаграмма будет выглядеть следующим образом ():
Рис. . – Гистограмма.
Теперь рассмотрим более сложную задачу, для решения которой круговую диаграмму в принципе использовать нельзя. Это задача, в которой требуется несколько раз сравнить несколько величин. Пусть вместе о студенткой Наташей газетами торговали Маша и Света. Их успехи в торговле отражены в следующей таблице:
|
А |
В |
С |
D |
Е |
F |
G |
Н |
|
|
1 |
Пн |
Вт |
Ср |
Чт |
Пт |
Сб |
Bс |
|
|
2 |
Наташа |
20 |
25 |
32 |
30 |
23 |
30 |
20 |
|
3 |
Маша |
33 |
28 |
25 |
25 |
22 |
25 |
20 |
|
4 |
Света |
15 |
20 |
22 |
29 |
34 |
35 |
30 |
Построим столбчатую диаграмму, на которой будут отображены данные сразу обо всех трех продавщицах. По-прежнему высота столбца будет символизировать количество газет. По прежнему у нас будет 7 опорных точек — по одной для каждого дня недели. Разница с предыдущей диаграммой будет в том, что теперь в каждой опорной точке будут стоять не один столбик, а три — по одному для каждого продавца. Все столбики одного продавца будут закрашены одинаково.
Выделим блок клеток А1:Н4, содержащий данные для графической обработки. Данные располагаются в строках. Первая строка выделенного блока является строкой Х координат (опорные точки); следующие три строки выделенного блока содержат Y координаты (высоты столбиков) диаграммы. Укажем заголовок диаграммы: “Торговля газетами” ().
Рис. . – Сложная гистограмма.
Линейная диаграмма служит для того, чтобы проследить за изменением нескольких величин при переходе от одной точки к другой.
Пример 4. Построить линейную диаграмму, отражающую изменение количества проданных газет в течение недели (см. предыдущий пример). Построение линейной диаграммы аналогично построению столбчатой, но вместо столбиков просто отмечается их высота (точками, черточками, крестиками) и полученные отметки соединяются прямыми линиями (диаграмма — линейная). Вместо разной штриховки (закраски) столбиков используются разные отметки (ромбики, треугольники, крестики и т.д.), разная толщина и типы линий (сплошная, пунктирная и пр.), разный цвет ().
Рис. . – Линейная диаграмма.
Нормированная линейчатая диаграмма позволяет наглядно сравнить суммы нескольких величин в нескольких точках, и при этом показать вклад каждой величины в общую сумму.
Пример 5. Составленные нами диаграммы “Торговля газетами” (и столбчатая, и линейная) интересны в первую очередь продавцам газет, демонстрируют успешность их работы. Но кроме продавцов в торговле газетами заинтересованы и другие лица. Например, издателю газеты нужно знать не только то, сколько экземпляров газеты продал каждый из продавцов, но и сколько они продали все вместе. При этом сохраняется интерес и к отдельным величинам, составляющим общую сумму. Возьмем таблицу продажи газет и построим для нее ярусную диаграмму.
Порядок построения нормированной диаграммы очень напоминает порядок построения диаграммы столбчатой. Разница в том, что столбики в ярусной диаграмме ставятся не рядом друг с другом, а один на другой. Соответственно меняются правила расчета вертикального и горизонтального размера диаграммы. Вертикальный размер будет определяться не наибольшей величиной, а наибольшей суммой величин. Зато количество столбиков всегда будет равняться количеству опорных точек: в каждой опорной точке всегда будет стоять ровно один многоярусный столбик ().
Рис. . – Нормированная диаграмма.
Диаграмма с областями (диаграмма площадей) — гибрид нормированной диаграммы с линейной. Позволяет одновременно проследить изменение каждой из нескольких величин и изменение их суммы в нескольких точках.
Пример 6. Возьмем таблицу продажи газет и построим для нее диаграмму площадей. Диаграмма площадей отличается от линейной диаграммы тем же, чем нормированная диаграмма отличается от столбчатой. При построении нормированной диаграммы каждый следующий столбик откладывается не от горизонтальной оси, а от предыдущего столбика. То же самое происходит и при построении диаграммы площадей. Но вместо построения столбиков (как это было в нормированной диаграмме) отмечается их высота, а потом эти отметки соединяются линиями (как это было в линейной диаграмме). Вот как будет выглядеть в результате областная диаграмма “Торговля газетами” ():
Рис. . – Диаграмма площадей.
Отдельные столбики здесь сливаются, образуя непрерывные области. Каждая область соответствует какой-то одной величине, для указания на которую используется личная штриховка (раскраска).
1. Конюховский П.В., Колесов Д.К. Экономическая информатика. – СПб.:Питер, 2001. - 560с.
2. Стоцкий Ю. Самоучитель Оffiсе 2000. – СПб.: Питер, 1999. - 576с.
3. Златопольский Д.М. 1700 заданий по Microsoft Excel.- СПб.: БХВ Петербург, 2003. - 544с.