Вычислительные возможности табличного редактора Microsoft Excel

Лекция Основы Excel Вычислительные возможности табличного редактора Microsoft Excel План. 1. 2. 3. 4. 5. 6. 7. 8. Типы ссылок (типы адресации) Формулы в Microsoft Excel Виды представления ссылок Именованные ячейки Встроенные математические и статистические функции Ошибочные значения Трассировка ссылок и зависимостей Специальная вставка Ссылка в Microsoft Excel — адрес ячейки или связного диапазона ячеек. В каждом листе Excel может быть 256 (2 в 8 степени) столбцов и 65536 (2 в 16 степени) строк. Адрес ячейки определяется пересечением столбца и строки, как в шахматах или морском бое, например: A1, C16. Адрес диапазона ячеек задается адресом верхней левой ячейки и нижней правой, например: A1:C5. Типы ссылок (типы адресации) Ссылки в Excel бывают 3-х типов:  Относительные ссылки (пример: A1);  Абсолютные ссылки (пример: $A$1);  Смешанные ссылки (пример: $A1 или A$1, они наполовину относительные, наполовину абсолютные). Знак $ здесь никакого отношения к денежным единицам не имеет, это лишь способ указать Excel тип ссылки. Различия между разными типами ссылок можно увидеть, если потянуть за маркер автозаполнения активной ячейки или диапазона ячеек, содержащих формулу со ссылками. Относительные ссылки Если вы ставите в какой то ячейке знак "=", затем щелкаете левой кнопкой мыши на какой то ячейке, Excel подставляет после "=" относительную ссылку на эту ячейку. Эта ссылка "запоминает", на каком расстоянии (в строках и столбцах) Вы щелкнули ОТНОСИТЕЛЬНО положения ячейки, где поставили "=" (смещение в строках и столбцах). Например, вы щелкнули на ячеку 3-мя столбцами левее и на 2 строки выше. Если после нажатия потянуть вниз за маркер автозаполнения, эта формула скопируется во все ячейки, через которые мы протянули. И в каждой ячейке эта ссылка будет указывать на ячейку, расположенную на 3 столбца влево и 2 строки вверх ОТНОСИТЕЛЬНО положения ссылки. Просмотреть формулу, введенную в ячейку можно, дважды щелкнув на интересуемой ячейке, или выделив ее, и нажав , также после выделения ячейки с формулой формулу видно в строке формул. Для лучшего понимания вспомните, как ходит шахматный конь. Он ходит буквой «Г» и из центра доски бьёт 8 клеток. «Упростим» немного правило хода коня: представим, что он может ходить только одной буквой «Г» — 2 клетки вперед и одну вправо. На какую бы клетку доски мы не поставим коня, каждый раз он ОТНОСИТЕЛЬНО своего положения отсчитывает смещение в строках и столбцах — 2 строки вверх и один столбец влево. Точно таким же образом работают относительные ссылки, только правило их «хода» задает пользователь. Каждый раз, когда мы тянем за маркер автозаполнения формула, содержащую относительные ссылки, Excel пересчитывает адреса всех относительных ссылок 1 Лекция Основы Excel в ней в соответствии с их «правилом хода» (у каждой относительной ссылки в формуле может быть свое «правило»). Формула может содержать ссылки на ячейки, которые расположены на другом рабочем листе или даже в таблице другого файла. При этом перед ссылкой, входящей в формулу, появляется имя книги и листа, откуда производилось копирование: =ЛИСТ5!F4 =[Книга1]Лист3!$E$17 Абсолютные ссылки Как было сказано выше, если потянуть за маркер автозаполнения формулу, содержащую относительные ссылки, Excel пересчитает их адреса. Если же в формуле присутствуют абсолютные ссылки, их адрес останется неизменным. Проще говоря — абсолютная ссылка всегда указывает на одну и ту же ячейку. Чтобы сделать относительную ссылку абсолютной, достаточно поставить знак «$» перед буквой столбца и адресом строки, например $A$1. Более быстрый способ — выделить относительную ссылку и нажать один раз клавишу «F4», при этом Excel сам проставит знак «$». Если второй раз нажать «F4», ссылка станет смешанной такого типа A$1, если третий раз — такого $A1, если в четвертый раз — ссылка опять станет относительной. И так по кругу. Смешанные ссылки Смешанные ссылки являются наполовину абсолютными и наполовину относительными. Знак доллара в них стоит или перед буквой столбца или перед номером строки. Это самый сложный для понимания тип ссылки. Например, в ячейке записана формула «=A$1». Ссылка A$1 относительная по столбцу A и абсолютная по строке 1. Если мы потянем за маркер автозаполнения эту формулу вниз или вверх, то ссылки во всех скопированных форумулах будут указывать на ячейку A1, то есть будет вести себя как абсолютные. Однако, если потянем вправо или влево — ссылка будет вести себя как относительная, то есть Excel будет пересчитывать ее адрес. Таким образом, формулы, созданные автозаполнением, будут использовать один и тот же номер строки ($1), но изменится номер столбца (A, B, C...). Формулы в Microsoft Excel Возможность использования формул и функций является одним из важнейших свойств табличного редактора. Это, в частности, позволяет проводить математическую и статистическую обработку (анализ) данных листа Excel. Под формулой в электронной таблице понимают выражение, которое начинается со знака равно (=) и состоит из операндов и операций. Формулы строятся как выражение для вычисления нового значения. Тип значения, полученного в результате вычисления по формуле, определяется типом операндов выражения. В качестве операндов используются: 1. числа; 2. тексты (вводятся в двойных кавычках, например «Неявка»), 3. логические значения (например, ИСТИНА и ЛОЖЬ, условия типа А23=А45 и т.д.); 4. ссылки — адреса ячеек. При перечислении ссылки разделяются точкой с запятой, например: А4; С5; С10: Е20; 5. встроенные функции Excel. Excel - программируемый табличный калькулятор. Все расчеты в Excel выполняют формулы. Если в ячейке написать просто "1+1", Excel не будет вычислять это выражение. Однако, если написать "=1+1" и нажать , в ячейке появится результат вычисления выражения - число 2. После нажатия 2 Лекция Основы Excel формула не пропадает, ее можно увидеть снова, если сделать двойной щелчок по ячейке, или если выделить ее и нажать или просто нажать +Апостроф. Также ее можно увидеть в панели инструментов «Строка формул», если опять же выделить ячейку. После двойного щелчка, нажатия или после щелчка в строке формул, можно изменить формулу, и для завершения нажать клавишу . В формуле можно использовать различные типы операторов (арифметические и т. п.), текст, ссылки на ячейку или диапазон ячеек, круглые скобки, именованные диапазоны. Естественно, в формулах соблюдается приоритет выполнения операций (умножение выполняется раньше сложения и т. п.). Для изменения порядка выполнения операций используются круглые скобки. Для того, чтобы вставить в формулу адрес ячейки (ссылку на ячейку), не обязательно писать его вручную. Проще поставить знак «=», затем левой кнопкой щелкнуть на нужной ячейке или выделить нужный диапазон ячеек. При этом Excel подставит в формулу ссылку автоматически. Если в формуле используется несколько ссылок, то каждой из них Excel дает свой цвет. Это очень удобно. Пример: напишите в какой либо ячейке формулу «=A1+D1», нажмите Enter, затем два раза щелкнете по ячейке. В ячейке вы увидите формулу с разноцветными ссылками, а вокруг ячеек A1 и D1 будут прямоугольники соответствующих цветов. Гораздо проще найти, куда указывает ссылка, по цвету прямоугольника, чем просматривать буквы столбцов и номера строк. Наведите курсор мыши на один из разноцветных прямоугольников и перетащите левой кнопкой за границу в другое место. Вы увидите, что при этом меняются и адреса ячеек в формуле — часто это самый быстрый способ подправить адреса в формуле, особенно после копирования маркером автозаполнения. Формулы можно копировать в другие ячейки. При этом в зависимости от типа ссылок, входящих в копируемую формулу, осуществляется их настройка: автоматическая (для относительных ссылок) или полуавтоматическая (для частично абсолютных ссылок). Однажды введенная формула может быть в любое время модифицирована. Кроме этого, программа Excel позволяет работать со сложными формулами, содержащими несколько операций. Операторы Операнды в формулах соединяются с помощью символов операторов. Операторы в Excel бывают бинарные и унарные. Бинарные операторы работают 2 значениями. Например, оператор «*» умножает число слева от себя на число справа от себя. Если число слева или справа опустить, то Excel выдаст ошибку. Унарные операторы оперируют одним значением. Пример унарных операторов: унарный «+» (ничего не делает), унарный «-» (меняет знак числа справа на противоположный) или знак «%» (делит число слева на 100). Благодаря использованию арифметических операций, Excel может работать как калькулятор. Арифметические операторы  «+» — сложение (Пример: «=1+1»);  «-» — вычитание (Пример: «=1-1»);  «*» — умножение (Пример: «=2*3»);  «/» — Деление (Пример: «=1/3»);  «^» — Возведение в степень (Пример: «=2^10»);  «%» — Процент (Пример: «=3 %» — преобразуется в 0,03; «=37*8 %» — нашли 8 % от 37). То есть если мы дописываем после числа знак «%», то число делится на 100. Результатом вычисления любого арифметического выражения будет число 3 Лекция Основы Excel Логические операторы  ">" — больше;  "<" — меньше;  ">=" — больше, либо равно;  "<=" — меньше, либо равно;  "=" — равно (проверка на равенство);  "<>" — неравно (проверка на неравенство). Оператор объединения 2-х строк текста в одну  Оператор «&» (амперсанд) служит для «склеивания» между собой двух текстовых строк. Например, в ячейке A1 текст «мама», в ячейке A2 текст «мыла раму». В A3 пишем формулу «=A1 & A2». В результате в ячейке A3 появится текст «мамамыла раму». Как видим, пробел между двумя строками автоматически не ставится. Чтобы вставить этот пробел, нужно изменить формулу вот так: «=A1 & " " & A2». Точно так же работает оператор "СЦЕПИТЬ", выглядеть формула с его участием будет так: «=Сцепить(A1;" ";A2)». Операторы ссылок  : (двоеточие). Ставится между ссылками на первую и последнюю ячейку диапазона. Такое сочетание является ссылкой на диапазон (A1:A15);  ; (точка с запятой). Объединяет несколько ссылок в одну ссылку (СУММ(A1:A15;B1:B15));  (пробел). Оператор пересечения множеств. Служит для ссылки на общие ячейки двух диапазонов (B7:D7 C6:C8). Выражения Выражения в Excel бывают арифметические и логические. Арифметическое выражение (например, «=2*(2+5)», результат — 14) в результате дает числовое значение (положительное, отрицательное, дробное число). Логическое выражение (например, «=3>5», результат — логическое значение «ЛОЖЬ») в результате может дать лишь 2 значения: «ЛОЖЬ» или «ИСТИНА» (одно число либо больше другого, либо не больше, других вариантов нет). Функции в Microsoft Excel В формулах Microsoft Excel можно использовать функции. Сам термин «функция» здесь используется в том же значении, что и «функция» в программировании. Функция представляет собой готовый блок (кода), предназначенный для решения каких-то задач. Функция – это заранее определенная формула, которая оперирует с одним или несколькими значениями и возвращает значение. Все функции в Excel характеризуются:  Названием (имя);  Предназначением (что, собственно, она делает);  Количеством аргументов (параметров). Аргументы задают значения или ячейки, используемые функцией. Аргумент функции заключается в круглые скобки. Открывающая скобка отмечает начало аргумента и ставится сразу после имени функции. В некоторых функциях нет аргументов (ПИ, ИСТИНА), но при записи она 4 Лекция Основы Excel все равно должна содержать круглые скобки: =Al*ПИ().При использовании в функции нескольких аргументов они отделяются один от другого точкой с запятой, а при использовании диапазона – двоеточием. В функции можно использовать до 30 аргументов, если при этом общая длина не превосходит 1024 символа. Однако любой аргумент может быть диапазоном, содержащим произвольное число ячеек листа. Указанные в ссылке ячейки в свою очередь могут содержать формулы, которые ссылаются на другие ячейки или диапазоны;  Типом аргументов (параметров);  Типом возвращаемого значения. В качестве примера разберем функцию «СТЕПЕНЬ»  Название: СТЕПЕНЬ;  Предназначение: возводит указанное число в указанную степень;  Количество аргументов: РАВНО два (ни меньше, ни больше, иначе Excel выдаст ошибку!);  Тип аргументов: оба аргумента должны быть числами, или тем, что в итоге преобразуется в число. Если вместо одного из них вписать текст, Excel выдаст ошибку. А если вместо одно из них написать логические значения «ЛОЖЬ» или «ИСТИНА», ошибки не будет, потому что Excel считает «ЛОЖЬ» равно 0, а истину — любое другое ненулевое значение, даже −1 равно «ИСТИНА». То есть логические значения в итоге преобразуются в числовые;  Тип возвращаемого значения: число — результат возведения в степень. Пример использования: «=СТЕПЕНЬ(2;10)». Если написать эту формулу в ячейке и нажать Enter, в ячейке будет число 1024. Здесь 2 и 10 — аргументы (параметры), а 1024 — возвращаемое функцией значение. Пример формулы для вычисления длины окружности, содержащую функцию ПИ(): Синтаксис записи функции Чтобы Excel не выдал ошибку, функция должна соответствовать определенному набору правил. Этот набор правил называется синтаксис записи функции. Общий синтаксис записи любой функции в Excel: имя_функции([аргумент_1; аргумент_2; … ; аргумент_N]) Список аргументов заключен в квадратные скобки, что говорит о том, что это необязательная часть.  Некоторые функции вообще не принимают аргументов. Например, функция ПИ() просто возвращает в ячейку значение константы «3,1415…», а функция СЕГОДНЯ() вставляет в ячейку текущую дату. Однако, даже если функция не принимает аргументов, пустые круглые скобки писать обязательно, иначе Excel выдаст ошибку!  Некоторые функции принимают РОВНО ОДИН аргумент. Например функции sin(число), cos(число) и т. п. 5 Лекция Основы Excel  Некоторые функции принимают больше, чем один аргумент. В таком случае аргументы разделяются между собой точкой с запятой «;». В общем случае, аргументами функции могут быть константы (числа, введенные вручную), ссылки на ячейки, ссылки на диапазон ячеек, именованные ссылки и другие функции (вложенные функции). Ввод функций вручную Для набора простейших формул, содержащий функции, можно не пользоваться специальными средствами, а просто писать их вручную. Однако, этот способ плохо подходит для набора длинных формул, таких, как на рисунке: К недостаткам набора формул вручную можно отнести:  руками ставить «=»;  набирать имя функции;  открывать/закрывать круглые скобки;  расставлять точки с запятой;  следить за порядком вложенности  заключать текст в двойные кавычки;  не иметь возможность посмотреть промежуточные расчеты;  и т. п. Слишком большая вероятность допустить ошибку, набирая вручную сложные и длинные формулы, и на это уходит много времени. Работа с мастером функций В любых версиях Excel для различных типов вычислений имеется большое число встроенных функций: математических, статистических, логических, текстовых, финансовых и др. Функции вводятся обычным набором с клавиатуры или более предпочтительным способом — с помощью Мастера функций. В нем все функции разделены на категории, каждая из которых включает в себя определенный набор функций. Для каждой категории функций в окне Мастера функций показан их состав. Выбирается категория функции, имя функции, внизу дается краткий синтаксис функции. Если функция использует несколько однотипных аргументов, указан символ многоточия (...). После нажатия кнопки <ОК> появляется следующее диалоговое окно и осуществляется построение функции, т.е. указание ее аргументов. Каждый аргумент вводится в специально предназначенную для него строку (например, так, как показано на рис). При написании сложных формул, особенно использующих вложенные функции, использование мастера функций — наилучшее решение. Он очень облегчает и ускоряет ввод формул, и делает многие 6 Лекция Основы Excel вещи за нас: автоматически вставляет знак "равно", имя функции, круглые скобки, расставляет точки с запятой. Позволяет просматривать значение ссылок и результаты промежуточных вычислений. Существует 3 способа запуска мастера функций: 1. С помощью кнопки в строке формул; 2. С помощью команды "Другие функции..." кнопки 3. С помощью пункта меню Вставка/Функция; ; Первый шаг После выполнения одного из этих действий откроется окно мастера функций: На первом шаге мы выбираем нужную нам функцию, пользуясь поиском или фильтром категорий. После выбора нужной функции нажимаем ОК и попадаем на второй шаг. Второй шаг Открывается следующее окно: 7 Лекция Основы Excel Вставка вложенной функции В одну функцию можно вставить другую функцию. Допускается до 7-ми уровней вложения функций (в Office 2007 — до 64). Конечно, функцию можно записать вручную (писать название вложенной функции, открывать скобки, ставить точки с запятой). Однако это противоречит самой идеологии мастера функций, который должен облегчать написание формул, защищать пользователя от ошибок и свести к минимуму ручную работу. Существует более удобный способ вложить функцию — специальная кнопка на панели Строка формул: После выбора нужной функции из выпадающего списка Excel вставит называние функции и круглые скобки в указанное место в формуле (в активное текстовое поле аргумента). После этого окно мастера функций для предыдущей функции (в этом примере "СУММ") сменится на окно для вставляемой функции ("СТЕПЕНЬ"), и ее название в формуле сделается жирным: Переключение на другую функцию в формуле Чтобы опять вернуться к окну для функции "СУММ", достаточно просто щелкнуть в строке формул на ее названии, и окно для степени сменится на окно для "СУММ". После этого функция "СУММ" в названии станет жирной, показывая, что в данный момент окно открыто именно для нее. Пример записи функции Типичные ошибки при работе с мастером функций 1. Перед вставкой функции забывают переключиться (ткнуть мышкой) в нужное текстовое поле (в нужный аргумент). При этом в активное текстовое поле вставляется знак "+", затем название функции; 2. После переключения на другую функцию (щелчка на ней в строке формул) забывают щелкнуть мышкой в одно из текстовых полей мастера функций и вставляют новую функцию. При этом название активной функции в строке формул заменяется на название вставляемой функции; 8 Лекция Основы Excel Полезные советы по работе с мастером функций  Окно мастера функций можно перетаскивать за любую точку;  В окне мастера функций, выделяя ссылку и нажимая "F4" один, или несколько раз, можно поменять тип ссылки (сделать абсолютной или смешанной);  Если в окне мастера функций нажать "F3", откроется окно для вставки именованных ссылок (если они существуют);  Если какую-то функцию в формуле нужно заменить на другую, выделяем эту функцию в строке формул и вставляем нужную функцию. Выделенная функция заменится на вставляемую;  Клавиша "Tab" служит для переключения на следующий аргумент (текстовое поле), а сочетание "Shift+Tab" — на предыдущее;  Если выделить ячейку, содержащую формулы с функциями и нажать кнопку Fx, то откроется окно мастера функций для одной из функций в формуле;  Для того, чтобы написать формулу типа "=СУММ(J1:J3)/СУММ(K1:K3)", откройте мастер функций, выберете функцию "СУММ", выделите нужный диапазон, затем щелкнете мышкой в строку формул и в ней вручную наберите "/", затем нажмите на кнопку для вставки функции, и вставьте вторую функцию "СУММ". Все будет работать;  Если в текстовом поле нужно ввести ТОЛЬКО текст, не обязательно вручную ставить двойные кавычки. Можно написать текст без кавычек и нажать Tab, или просто щелкнуть в другое поле. Excel проставит кавычки автоматом (работает не во всех текстовых полях);  В тех полях, где требуется ввести логическое значение "ЛОЖЬ" или "ИСТИНА", достаточно ввести "0" для "ЛОЖЬ", и любое ненулевое значение для "ИСТИНА" (принято использовать 1). Ехсеl позволяет отображать введённые формулы не только в строке формул, но и в ячейках рабочего листа. Отображение формул обеспечивается установкой флага формулы, доступного через пункт меню Сервис/Параметры/(вкладка)Вид. Этот режим может быть использован также для документирования работы, если после его установки напечатать лист с содержащимися в нём формулами. Виды представления ссылок в Microsoft Excel Есть два вида представления ссылок в Microsoft Excel:  Классический;  Стиль ссылок R1C1 (здесь R — row (строка), C — column (столбец)). Включить стиль ссылок R1C1 можно в настройках Сервис/Параметры и на вкладке «Общие» установить галочку «Стиль ссылок R1C1» Если включен классический вид и в ячейке с адресом A1 находится формула «=B3» (B3 — относительная ссылка), то после переключения в вид R1C1 она примет вид «=R[2]C[1]». В режиме R1C1 в квадратных скобках указывается относительное смещение. В скобках после R указано смещение в строках (row) — две строки вниз (вниз, потому что число положительное, если бы стояло «R[-2]» — было бы вверх). В скобках после C указано смещение в столбцах — 1 столбец вправо (если бы было C[1] — влево). В общем случае направления вниз и вправо считаются положительными, вверх и влево — отрицательными. 9 Лекция Основы Excel Если включен классический вид и в ячейке с адресом A1 находится формула «=$B$3» ($B$3 — абсолютная ссылка), то после переключения в вид R1C1 она примет вид «=R3C2». Квадратных скобок нет, то есть указано не относительно смещение по отношению к положению формула, а абсолютное смещение по отношению к всему листу (вспомните абсолютную и относительную системы координат). Именованные ячейки Для упрощения работы с ячейками или диапазоном ячеек можно дать ей/им имя, и затем обращаться к ячейке или диапазону не по его адресу, а по символьному имени. Именованные ячейки можно использовать везде, где можно использовать то значение, на которое указывает ссылка. Примечание! Именованные ячейки по умолчанию являются абсолютными ссылками. Для создания именованной ячейки нужно выделить нужную ячейку или диапазон, затем щелкнуть в текстовое поле (см. рис. ниже) ввести свое имя и нажать . Можно использовать русские имена. Имя не может содержать пробелов и начинаться с цифры. Именованная ячейка может ссылаться на несвязный диапазон ячеек (выделенный с ). Для вставки именованной ячейки можно воспользоваться кнопкой со стрелкой вниз: или нажать клавишу , откроется следующее окно: Пример использования: "=СУММ(tablica_1)" (результат=218) Для того что бы убрать имя именованной ячейки (например: чтобы присвоить другой ячейке это имя) Вставка/Имя/Присвоить, в появившемся диалоговом окне Присвоение имени выделить ненужное имя и нажать на кнопке Удалить. 10 Лекция Основы Excel Встроенные математические и статистические функции В Excel имеется целый ряд встроенных математических функций, существенно облегчающих решение задач. Таблица. Обзор математических функций Функция Описание функции Суммирует множество чисел. Синтаксис: СУММ(число1; [число2]; СУММ() ОКРУГЛ() ЧЕТН(число) НЕЧЕТН(число) ЦЕЛОЕ(число) ОТБР(число, количество_цифр) СЛЧИС() ПРОИЗВ(число1; число2; …) …) Округляет число, задаваемое ее аргументом до указанного количества десятичных разрядов. Синтаксис: ОКРУГЛ (число; число_разрядов) Отрицательное значение числа разрядов вызывает округление целой части, ноль – округление до ближайшего целого числа. Пример: =ОКРУГЛ(123,4567;-2)  100 =ОКРУГЛ(123,4567;-1)  120 =ОКРУГЛ(123,4567;0)  123 =ОКРУГЛ(123,4567;1)  123,5 Функция ОКРУГЛ отличается от установки формата ячеек. Так при задании в формате ячеек числа десятичных знаков равное двум в ячейке после запятой будет отражаться два десятичных знака, а в расчетах будет участвовать само число Округляет вверх до ближайшего четного целого числа. Положительные числа округляются в сторону увеличения, отрицательные числа округляются в сторону уменьшения. Пример: =ЧЕТН(23,4)  24 =ЧЕТН(2)  2 =ЧЕТН(3)  4 =ЧЕТН(-3)  -4 Округляет число вверх до ближайшего нечетного целого числа. Положительные числа округляются в сторону увеличения, отрицательные - в сторону уменьшения Округляет число вниз до ближайшего целого. Пример: = ЦЕЛОЕ (10,9999)  10 = ЦЕЛОЕ (-100,999)  -101 Отбрасывает все цифры справа от десятичной запятой независимо от знака числа. Необязательный аргумент Количество_цифр задает позицию, после десятичной запятой, после которой все прочие цифры отбрасываются. Если второй аргумент опущен, он принимается равным нулю. Например, формула =ОТБР(45,978) возвращает значение 45, а формула =ОТБР(45,978;1) – число 45,9 Генерирует случайные числа, равномерно распределенные между 0 и 1. Функция не имеет аргументов. Значение функции изменяется при каждом пересчете листа Перемножает числа, задаваемые её аргументами. Функция может иметь до 30 аргументов 11 Лекция Основы Excel ОСТАТ(число; делитель) КОРЕНЬ(число) LOG(число; основание) LN(число) EXP(число) ПИ() ГРАДУСЫ(угол) РАДИАНЫ(угол) SIN(число) COS(число) TAN(число) СУММЕСЛИ (диапазон; критерий; диапазон_суммирования) Возвращает остаток от деления. Значение функции – это остаток, получаемый при делении числа на делитель Возвращает положительный квадратный корень из числа Возвращает логарифм положительного числа по заданному основанию Находит натуральный логарифм числа, указанного в качестве аргумента Вычисляет значение е возведенной в заданную степень Возвращает значение pi = 3,14 Перевод заданный угол из радиан в градусы Перевод заданный угол из градусов в радианы Вычисляет синус угла, заданного в радианах Вычисляет косинус угла, заданного в радианах Вычисляет тангенс угла, заданного в радианах Подсчитывает сумму значений из диапазона, удовлетворяющих заданному условию (критерию) При обработке коммерческих данных довольно часто возникает необходимость определения различных статистических характеристик. Поэтому в состав Excel встроен ряд функций, применимых при решении задач статистического анализа наборов данных. Среди всего многообразия статистических функций наибольший интерес для нас пока представляют функции МИН, МАКС, СРЗНАЧ, СЧЕТ. Таблица. Обзор статистических функций Функция Описание функции Возвращает среднее арифметическое своих аргументов ( x1  ...  xn ) n , СРЗНАЧ(x1,…,xn) которые могут быть числами или именами, массивами или ссылками на ячейки с числами. Синтаксис: =СРЗНАЧ(число1;число2;…) СРЗНАЧА(x1,…,xn) Вычисляет среднее арифметическое ( x1  ...  xn ) n . Помимо чисел обрабатываются текстовые и логические значения:  пустой текст “”, а также массивы и ссылки, содержащие текст, интерпретируются как ноль;  логическое значение ИСТИНА интерпретируется как 1, а ЛОЖЬ – как 0 Возвращает минимальное (максимальное) значение из списка аргументов, при этом логические значения и текст игнорируются. Синтаксис: МИН() МАКС() СЧЕТ() СЧЁТЗ(х1; х2;xn) МОДА(х1; х2;…;xn) =МИН(число1;число2;…) Подсчитывает количество ячеек в заданном диапазоне, содержащих непосредственно числовые значения, а также даты и формулы, возвращающие числа. Синтаксис: =СЧЕТ(значение1;значение2;…) Подсчитывает число непустых ячеек в заданном диапазоне независимо от их содержимого Возвращает наиболее часто встречающееся или повторяющееся значение в массиве или интервале данных. Функция МОДА является мерой взаимного расположения значений. Аргументы должны быть числами, именами, массивами или ссылками, которые содержат числа. Если аргумент содержит текст, логические значения или пустые ячейки, эти значения игнорируются; ячейки, содержащие нулевые значения, учитываются. 12 Лекция Основы Excel Если множество данных не содержит одинаковых данных, то функция МОДА возвращает значение ошибки #Н/Д. Возвращает медиану заданных чисел. Медиана — это число, которое является серединой множества чисел, то есть половина чисел имеют значения большие, чем медиана, а половина чисел имеют значения меньшие, чем медиана. Аргументы должны быть числами или именами, массивами или ссылками, МЕДИАНА(х1; …; xn) содержащими числа. Если аргумент содержит текст, логические значения или пустые ячейки, эти значения игнорируются; ячейки, содержащие нулевые значения, учитываются. Если в множестве четное количеств чисел, то функция МЕДИАНА вычисляет среднее двух чисел, находящихся в середине множества. СЧЁТЕСЛИ(диапазон; Подсчитывает количество ячеек в диапазоне, удовлетворяющих заданному критерий) условию (критерию) Пример. 1. СЧЁТЕСЛИ(B7:B31;"к*") - подсчитает количество сделок с товарами, начинающимися с буквы "к" 2. СЧЁТЕСЛИ(B7:B31;"????") - подсчитает количество сделок с товарами, наименование которых состоит из 4-х букв 3. СЧЁТЕСЛИ(C7:C31;">50") - подсчитает количество крупных сделок с объемом партии больше 50 4. СЧЁТЕСЛИ(D7:D31;">10.10.2004") - подсчитает количество сделок, совершенных позже 10 октября 2004 года Ошибочные значения Если при составлении формулы была допущена ошибка, то в ячейке появляется информация об ошибочном значении в формуле. Ошибочное значение - это результат формулы, которую Ехсе1 не может вычислить. В Ехсе1 определены семь ошибочных значений (табл). Сообщение об ошибке всегда начинается со знака #. Таблица. Ошибочные значения Ехсel Ошибка Описание Пример # ДЕЛ/О! Попытка деления на ноль. Ошибка обычно связана со ссылкой делителя на пустую ячейку =A2/0 #ИМЯ? В формуле используется имя, отсутствующее в списке имён окна диалога Присвоение имени. Ехсеl также выводит это ошибочное значение в том случае, когда строка символов не заключена в двойные кавычки =ADD7+90 13 Лекция Основы Excel #ЗНАЧ! Введена математическая формула, которая ссылается на текстовое значение. Используется аргумент недопустимого типа =A1+”A2” #ССЫЛКА! Отсутствует диапазон ячеек, на который ссылается формула #Н/Д Нет данных для вычислений. При построении модели можно ввести #Н/Д в ячейки и тем самым показать, что они зарезервированы для ожидаемых в дальнейшем данных. Любая формула, которая ссылается на ячейки, содержащие #Н/Д, возвращает значение #Н/Д #ЧИСЛО! Задан неправильный аргумент функции. #ЧИСЛО! Может указывать также на то, что результат формулы слишком велик или слишком мал и не может быть представлен в ячейке #ПУСТО! В формуле указано пересечение диапазонов, но эти диапазоны не =СУММ(A1 A2) имеют общих ячеек =COS(1E+10) В состав Excel включена совокупность средств, обеспечивающих синтаксический контроль за вводом формул, контроль за правильностью ссылок и типами данными, обрабатываемых с помощью формул. Для удобства восприятия формулы Excel позволяет разместить её на нескольких строках в строке формул. Включение в формулу разрыва строки обеспечивается нажатием комбинации клавиш +. В Excel предусмотрен контроль за вводом формул в случае ошибки записи формулы выводится сообщение: Обнаружены ошибки во введённой формуле. Принять предлагаемые исправления? и предлагается свой вариант записи формулы. Если при вычислении было обнаружено одно из описанных выше ошибочных значений, то рядом с ячейкой появится значок в виде ромбика с восклицательным знаком внутри. Щелчок на этом значке позволит выбрать один из вариантов обработки этой ошибки (например, с помощью команды Показать этапы вычисления можно увидеть в каком именно месте возникла ошибка): 14 Лекция Основы Excel Трассировка ссылок и зависимостей При вложении одной формулы в другую легко допустить ошибку. Для контроля за правильностью потоков данных и источниками ошибок в Ехсеl используется трассировщик ячеек. При трассировке отмечаются влияющие и зависимые ячейки. Влияющие — это ячейки, значения которых используются формулой в выделенной ячейке. Ячейка, для которой определены влияющие ячейки, всегда содержит формулу Зависимые — это ячейки, которые используют значение выделенной ячейки. Ячейка, для которой определены зависимые ячейки, может содержать формулу или константу. Для ячейки E3 отобразили влияющие ячейки Для ячейки E3 отобразили зависимые ячейки Для определения зависимостей поместите табличный курсор в рассматриваемую ячейку и вызовите команду Сервис/ Зависимости формул/ Зависимые ячейки или Влияющие ячейки. После этого между зависимыми ячейками появятся стрелки. Они показывают непосредственное влияние содержимого одних ячеек на формирование результата в других ячейках. Неоднократный последовательный выбор пунктов Зависимые ячейки либо Влияющие ячейки позволяет проследить косвенно зависимые и косвенно влияющие ячейки. В случае, когда нужно проследить большое число зависимостей, удобно применить панель инструментов Зависимости. Специальная вставка Специальное копирование данных между файлами в Excel включает в себя команду Специальная вставка в меню Правка. В отличие от обычного копирования данных с помощью команды Вставить команда Специальная вставка может быть использована для вычислений и преобразования информации, а также для связывания данных рабочих книг Excel. Команда Специальная вставка часто используется и для копирования атрибутов форматирования ячейки. Специальная вставка выполняется в несколько шагов: 1. Выделите ячейку или ячейки для копирования. 2. Выберите Правка/ Копировать (или из контекстного меню). 3. Выделите ячейку или ячейки, в которые будут помещены исходные данные. 15 Лекция Основы Excel 4. Выберите Правка/Специальная вставка. Диалоговое окно Специальная вставка содержит несколько параметров для вставки данных. 5. Установите необходимые параметры, например форматы (при копировании форматов изменяется только форматирование, а не значение ячеек). 6. Выберите ОК . Первая группа параметров диалогового окна Специальная вставка позволяет выбрать содержимое или атрибуты форматирования, которые необходимо вставлять. При выборе параметра Все вставляются содержимое и атрибуты каждой копируемой ячейки на новое место. Другие варианты позволяют вставлять разные комбинации содержимого и/или атрибутов. Вторая группа параметров применяется только при вставке формул или значений и описывает выполняемые операции над вставляемой информацией в ячейки, которые уже содержат данные (табл.). Таблица. Параметры команды Специальная вставка Параметр Результат вставки Сложить Вставляемая информация будет складываться с существующими значениями Вычесть Вставляемая информация будет вычитаться из существующих значений Умножить Существующие значения будут умножены на вставляемую информацию Разделить Существующие значения будут поделены на вставляемую информацию Пропускать пустые ячейки Можно выполнить действия только для ячеек, содержащих информацию, т. е. при специальном копировании пустые ячейки не разрушат существующие данные Транспонировать Ориентация вставляемой области будет переключена со строк на столбцы и наоборот Выбор Нет означает, что копируемая информация просто замещает содержимое ячеек. Выбирая другие варианты операций, получим, что текущее содержимое будет объединено со вставляемой информацией и результатом такого объединения будет новое содержимое ячеек. Пример использования команды Специальная вставка 1. Введите данные, как показано на рисунке 2. 3. 4. 5. 6. 7. Выделите область для копирования А2:А4. Выберите Правка/Копировать. Щелкните ячейку В2 (верхний левый угол области, в которую будут помещены данные). Выберите Правка/Специальная вставка. Установите параметр Умножить. Нажмите ОК. Обратите внимание, что на экране осталась граница области выделения и данные изменились: произошло умножение 16 Лекция Основы Excel 8. Щелкните ячейку С2, которая будет началом области вставки. 9. Выберите Правка/Специальная вставка и установите параметр Транспонировать. В рассмотренном примере с помощью команды специальная вставка произошло умножение значений одного столбца на значения другого, а можно умножить содержимое столбца на одно значение, например, увеличим оклад работников на некоторый множитель. Данный метод расчета нового размера окладов с помощью специальной вставки достаточно прост: 1. В любую свободную ячейку рабочего листа (например, G4) со списком введите значение индекса увеличения оклада (1,077). 2. Скопируйте содержимое данной ячейки. 3. Выделите диапазон ячеек Е2:Е11 и вызовите диалоговое окно Специальная вставка. 4. В области Вставить появившегося окна активизируйте переключатель Значения, в области Операция - переключатель Умножить, после чего нажмите кнопку ОК. В результате все числа, указанные в ячейках Е2:Е11, будут умножены на значение 1,077, введенное в ячейку G4. На рисунке список содержит уже новые оклады. 17