3869
Формулы в Microsoft Excel
Лекция
Информатика, кибернетика и программирование
Формулы в Microsoft Excel Общие сведения Excel - программируемый табличный калькулятор. Все расчеты в Excel выполняют формулы. Формулой Excel считает все, что начинается со знака "=". Если в ячейке написать просто "1+1", Excel не будет вычислять это...
Русский
2012-11-09
407.37 KB
129 чел.
Общие сведения
Excel - программируемый табличный калькулятор. Все расчеты в Excel выполняют формулы. Формулой Excel считает все, что начинается со знака "=". Если в ячейке написать просто "1+1", Excel не будет вычислять это выражение. Однако, если написать "=1+1" и нажать Enter, в ячейке появится результат вычисления выражения - число 2. После нажатия Enter формула не пропадает, ее можно увидеть снова, если сделать двойной щелчок по ячейке, или если выделить ее и нажать F2 или просто нажать Ctrl+Апостроф. Также ее можно увидеть в панели инструментов «Строка формул», если опять же выделить ячейку. После двойного щелчка, нажатия F2 или после щелчка в строке формул, можно изменить формулу, и для завершения нажать клавишу Enter.
В формуле можно использовать различные типы операторов (арифметические и т. п.), текст, ссылки на ячейку или диапазон ячеек, круглые скобки, именованные диапазоны. Естественно, в формулах соблюдается приоритет выполнения операций (умножение выполняется раньше сложения и т. п.). Для изменения порядка выполнения операций используются круглые скобки.
Использование текста в формулах
Если в формуле используется текст, то он обязательно должен быть заключен в двойные кавычки. Если написать формулу «=мама», Excel выдаст ошибку, а если написать «="мама"» все ок, корректная формула.
Использование ссылок в формулах
Для того, чтобы вставить в формулу адрес ячейки (ссылку на ячейку), не обязательно писать его вручную. Проще поставить знак «=», затем левой кнопкой щелкнуть на нужной ячейке или выделить нужный диапазон ячеек. При этом Excel подставит в формулу ссылку автоматически.
Если в формуле используется несколько ссылок, то каждой из них Excel дает свой цвет. Это очень удобно. Пример: напишите в какой либо ячейке формулу «=A1+D1», нажмите Enter, затем два раза щелкнете по ячейке. В ячейке вы увидите формулу с разноцветными ссылками, а вокруг ячеек A1 и D1 будут прямоугольники соответствующих цветов. Гораздо проще найти, куда указывет ссылка, по цвету прямоугольника, чем просматривать буквы столбцов и номера строк. Наведите курсор мыши на один из разноцветных прямоугольников и перетащите левой кнопкой за границу в другое место. Вы увидите, что при этом меняются и адреса ячеек в формуле часто это самый быстрый способ подправить адреса в формуле, особенно после копирования маркером автозаполнения.
Операторы
Операторы в Excel бывают бинарные и унарные. Бинарные операторы работают 2 значениями. Например, оператор «*» умножает число слева от себя на число справа от себя. Если число слева или справа опустить, то Excel выдаст ошибку.
Унарные операторы оперируют одним значением. Пример унарных операторов: унарный «+» (ничего не делает), унарный «-» (меняет знак числа справа на противоположный) или знак «%» (делит число слева на 100).
Арифметические операторы
Результатом вычисления любого арифметического выражения будет число
Логические операторы
Оператор объединения 2-х строк текста в одну
Оператор «&» (амперсанд) служит для «склеивания» между собой двух текстовых строк. Например, в ячейке A1 текст «мама», в ячейке A2 текст «мыла раму». В A3 пишем формулу «=A1 & A2». В результате в ячейке A3 появится текст «мамамыла раму». Как видим, пробел между двумя строками автоматически не ставится. Чтобы вставить этот пробел, нужно изменить формулу вот так: «=A1 & " " & A2». Точно так же работает оператор "СЦЕПИТЬ", выглядеть формула с его участием будет так: «=Сцепить(A1;" ";A2)».
Операторы ссылок
Выражения
Выражения в Excel бывают арифметические и логические.
Арифметическое выражение (например, «=2*(2+5)», результат 14) в результате дает числовое значение (положительное, отрицательное, дробное число). Логическое выражение (например, «=3>5», результат логическое значение «ЛОЖЬ»)в результате может дать лишь 2 значения: «ЛОЖЬ» или «ИСТИНА» (одно число либо больше другого, либо не больше, других вариантов нет).
В формулах Microsoft Excel можно использовать функции. Сам термин «функция» здесь используется в том же значении, что и «функция» в программировании. Функция представляет собой готовый блок (кода), предназначенный для решения каких-то задач.
Все функции в Excel характеризуются:
В качестве примера разберем функцию «СТЕПЕНЬ»
Пример использования: «=СТЕПЕНЬ(2;10)». Если написать эту формулу в ячкейке и нажать Enter, в ячейке будет число 1024. Здесь 2 и 10 аргументы (параметры), а 1024 возвращаемое функцией значение.
Пример формулы для вычисления длины окружности, содержащую функцию ПИ():
Синтаксис записи функции
Как вы видите, чтобы Excel не выдал ошибку, функция должна соответствовать определенному набору правил. Этот набор правил называется синтаксис записи функции.
Общий синтаксис записи любой функции в Excel:
имя_функции([аргумент_1; аргумент_2; … ; аргумент_N])
Список аргументов заключен в квадратные скобки, что говорит о том, что это необязательная часть.
В общем случае, аргументами функции могут быть константы (числа, введенные вручную), ссылки на ячейки, ссылки на диапазон ячеек, именованные ссылки и другие функции (вложенные функции).
Ввод функций вручную
Для набора простейших формул, содержащий функции, можно не пользоваться специальными средствами, а просто писать их вручную (см. рис. выше). Однако, этот способ плохо подходит для набора длинных формул, таких, как на рис. ниже.
К недостаткам набора формул вручную можно отнести:
Слишком большая вероятность допустить ошибку, набирая вручную сложные и длинные формулы, и на это уходит много времени.
Ввод функции с помощью кнопки "сигма"
Одно из средств облегчить и ускорить работы с функциями кнопка на панели инструментов «Стандартная». В ней разработчики Microsoft «спрятали» пять часто используемых функций:
Полезный совет: Чтобы быстро узнать сумму значений в диапазоне, выделяем его и смотрим на строку состояния там должна отображаться сумма;
Принцип работы:
В процессе выделения можно нажимать F4 для изменения типа ссылки и F3 для вставки именованных ссылок.
Общие сведения. Способы запуска
При написании сложных формул, особенно использующих вложенные функции, использование мастера функций наилучшее решение. Он очень облегчает и ускоряет ввод формул, и делает многие вещи за нас: автоматически вставляет знак "равно", имя функции, круглые скобки, расставляет точки с запятой. Позволяет просматривать значение ссылок и результаты промежуточных вычислений.
Существует 3 способа запуска мастера функций:
Первый шаг
После выполнения одного из этих действий откроется окно мастера функций:
На первом шаге мы выбираем нужную нам функцию, пользуясь поиском или фильтром категорий. После выбора нужной функции нажимаем "ОК" и попадаем на второй шаг.
Второй шаг
Открывается следующее окно:
Вставка вложенной функции
В одну функцию можно вставить другую функцию. Допускается до 7-ми уровней вложения функций (в Office 2007 до 64). Конечно, функцию можно записать вручную (писать название вложенной функции, открывать скобки, ставить точки с запятой). Однако это противоречит самой идеологии мастера функций, который должен облегчать написание формул, защищать пользователя от ошибок и свести к минимуму ручную работу. Существует более удобный способ вложить функцию специальная кнопка на панели "Строка формул":
После выбора нужной функции из выпадающего списка Excel вставит называние фукнции и круглые скобки в указанное место в формуле (в активное текстовое поле аргумента). После этого окно мастера функций для предыдущей функции (в этом примере "СУММ") сменится на окно для вставляемой функции ("СТЕПЕНЬ"), и ее название в формуле сделается жирным:
Переключение на другую функцию в формуле
Чтобы опять вернуться к окну для функции "СУММ", достаточно просто щелкнуть в строке формул на ее названии, и окно для степени сменится на окно для "СУММ". После этого функция "СУММ" в названии станет жирной, показывая, что в данный момент окно открыто именно для нее.
Типичные ошибки при работе с мастером функций
Полезные советы по работе с мастером функций
Простейшие математические и статистические функции
МИН, МАКС
Синтаксис:
МИН(число1; число2; ... ; число30) МАКС(число1; число2; ... ; число30)
Функции МИН и МАКС принимают от 1 до 30 аргументов (в Office 2007 до 255) и возвращает минимальный / максимальный из них. Если в качестве аргумента передать диапазон ячеек, из диапазона будет выбрано минимальное / максимальное значение. Эти функции также могут быть вставлены с помощью кнопки "сигма".
СРЗНАЧ
СРЗНАЧ(число1; число2; ... ; число30)
Функция СРЗНАЧ (среднее значение) принимает от 1 до 30 аргументов (в Office 2007 до 255) и возвращает их среднее арифметическое (сумма чисел, делённая на количество чисел). Эту функцию также можно вставить с помощью кнопки "сигма"
СТЕПЕНЬ
СТЕПЕНЬ(число; степень)
Функция СТЕПЕНЬ возвращает результат возведения первого аргумента ("число"), в степень, указанную во втором аргументе ("степень").
СУММ
=СУММ(арг1; арг2; ... ; арг30)
Функция СУММ принимает от 1 до 30 аргументов (в Office 2007 до 255) и возвращает их сумму. В качестве аргументов можно передавать адреса диапазонов (что чаще всего и делается), в этом случае просуммируются все числа в диапазоне.
СЧЁТ
СЧЁТ(арг1; арг2; ... ; арг30)
Функция СЧЁТ принимает от 1 до 30 аргументов (в Office 2007 до 255) и возвращает количество аргументов, являющиеся числами. Чаще всего функции просто передают адрес диапазона, а она подсчитывает количество ячеек с числами.
ПИ
ПИ()
Возвращает значение тригонометрической константы pi = 3,1415...
ПРОИЗВЕД
ПРОИЗВЕД(арг1; арг2; ... ; арг30)
Функция ПРОИЗВЕД принимает от 1 до 30 аргументов (в Office 2007 до 255) и возвращает их произведение. В качестве аргументов можно передавать адреса диапазонов, в этом случае перемножатся все числа в диапазоне.
Функции СУММЕСЛИ и СЧЁТЕСЛИ
СУММЕСЛИ
Часто необходимо не просто просуммировать все значения в диапазоне, а включить в сумму только те, которые удовлетворяют определённому условию. Функция СУММ суммирует все, что ей дают, функция СУММЕСЛИ позволяет просуммировать только значения, соответствующие определённому условию
Синтаксис:
СУММЕСЛИ(диапазон; критерий; [диапазон_суммирования] )
СЧЁТЕСЛИ
Работает очень похоже на функцию СУММЕСЛИ. В отличие от СУММЕСЛИ, которая суммирует значения из ячеек, СЧЁТЕСЛИ подсчитывает количество ячеек, удовлетворяющих определённому условию. Если написать формулу СУММЕСЛИ(">10", A1:A10), будет подсчитана сумма значений из ячеек, значение в которых больше 10. Если же написать СЧЁТЕСЛИ(">10", A1:A10), будет подсчитано количество ячеек, значение в которых больше 10.
Синтаксис:
СЧЁТЕСЛИ(диапазон; критерий)
В примере выше фактически подсчитывается количество ячеек, содержащих текст "Яблоки".
Логические функции ЕСЛИ, И, ИЛИ
ЕСЛИ
Синтаксис:
ЕСЛИ(логическое_выражение; значение_если_истина; значение_если_ложь).
Функция ЕСЛИ позволяет организовать в формуле ветвление. Вспомните сказки: налево пойдешь коня потеряешь, прямо пойдешь в болото попадешь, направо пойдешь засосёт в чёрную дыру. Использование функций ЕСЛИ, И, ИЛИ граничит с программированием. Неудивительно, что для многих людей разобраться, как они работают, очень сложно. В голове должен быть чёткий алгоритм решения задачи и требуется хорошее понимание понятия "тип данных"
Алгоритм перехода через дорогу на светофор
И
Синтаксис:
Логич_знач И( логич_знач1; логич_знач2; ... ; логич_знач30 )
Примечание: Функция И почти никогда не используется сама по себе, обычно её используют в качестве аргумента других функций, например, ЕСЛИ.
ИЛИ
Синтаксис:
Логич_знач ИЛИ( логич_знач1; логич_знач2; ... ; логич_знач30 )
Примечание: Функция ИЛИ почти никогда не используется сама по себе, обычно её используют в качестве аргумента других функций, например, ЕСЛИ.
Функция ВПР (Вертикальное Первое Равенство)
Для функции необходимо внести следующие данные: какое значение искать (указывается ячейка содержащее значение), массив в котором происходит поиск и номер столбца в котором находится значение, номер повторного значения в массиве, значение которого и будет использовано , которое надо вернуть. Очень удобная Функция значительно расширяет свои возможности при использовании её в паре с логической функцией ЕСЛИ().
Объясняю как пользоваться функцией: = ВПР (что ищем, таблица где ищем, из какого столбца взять значение, булевская переменная актуальная для очень больших отсортированных таблиц, для малых всегда ставим 0- полный перебор таблицы). К примеру = ВПР (A1, B:D, 2, 0) Эта функция нужна к примеру когда вам нужно копировать данные из одной таблицы в другую. Допустим, в одной таблице у вас производство по моделям, а в другой-продажи по моделям. В этих таблицах модели не по порядку. Вы можете добавить колонку "продажи" в таблицу "производство" и с помощью функции ВПР найти соответствующие значения продаж из другой таблицы.
Использование функции автоматически находит в указанной таблице нужное значение и избавляет от ручных копирований.
Примечание: ВПР выбирает только одно значение. Если у вас в таблицах модели повторяются, необходимо использовать функцию СУММЕСЛИ<!необходим пример, спорное утверждение !>.
Примечание: что ищем всегда ищется в первом столбце таблицы и результат вызова функции - значение ячейки таблицы со столбцом указанном параметром "из какого столбца взять значение", и строкой в которой первый раз будет найденно искомое значение
Функция ГПР (Горизонтальное Первое Равенство)
аналогично ВПР, значение ищется в первой строке таблицы результирующее значение берется из ячейки таблицы с номером столбца в котором встретилось найденное значение и заданном в параметре номере строки
А также другие работы, которые могут Вас заинтересовать | |||
35653. | Кулинария. Творческий проект | 1.21 MB | |
Цели и задачи проекта: расширение кругозора в области кулинарии; развитие понятия о здоровом и рациональном питании характеристика пищевой ценности отдельных продуктов рекомендации по организации рационального питания; восприятие различных национальных блюд освоение приготовления; формирование умения сервировки стола правильно вести себя за столом. Искусство приготовления пищи. Правильно смешать все ингредиенты подобрать правильное количество того или иного продукта красиво подать блюдо Так почему бы не устроить из приготовления... | |||
35654. | Вязание крючком | 134.39 KB | |
Х б нитки коричневого цвета м 16 руб. Х б нитки жёлтого цвет м 16 руб. Самоотценка Экологическая оценка Мои нитки сделаны из натурального материала с добавлением искусственных материалов. | |||
35655. | Группа киноведов. Сравнение фильмов Сергея Бондарчука с работой Французского режиссера Дорнхельмом | 57.5 KB | |
Фахрутдинов Владислав В своей творческой работе я хочу сравнить два фильма снятых по роману Война и Мир с оригиналом произведения. За то время пока я собирал материал для этой работы я прочитал множество рецензий на оба фильма и сделал для себя вывод. Перейду сразу к самому интересному за что ругают оба фильма это кино ошибки. Или в начале фильма обозначена сюжетная линия Николая Ростова и после 1807 года она почти полностью опущена. | |||
35656. | Лавочка. Творческий проект | 40.1 KB | |
Лавочки стол шкаф вот весь интерьер комнаты.ЦельПроявить свои способности в проектной деятельностиИзучить конструирования и технологию изготовления лавочки Научиться правильно пользоваться инструментами Изготовить Лавочку 4. Выбор изделияЛавочки весной и летом весьма востребованные изделия на даче когда люди много трудятся сажают растения и... | |||
35657. | Летняя сумочка. Вязание крючком | 8.05 MB | |
Связать изделие 4. Булавки вкалывают в изделие в три прокола таким образом чтобы острие осталось по возможности между слоев ткани. Незавершенное изделие лучше хранить завернув в ткань или положив в пакет. работа оформлена в законченное изделие в целом изделие производит благоприятное впечатление. | |||
35658. | Cервировка стола к завтраку | 1.37 MB | |
В переводе с английского слово завтрак (breakfast) означает «прервать пост». В зависимости от того, когда состоялся последний прием пищи, организм может голодать («постится») от 8 до 12 часов. Этот период значительно удлиняется, если завтрак пропускается. | |||
35659. | Изготовление блинчиков. Творческий проект | 253.46 KB | |
Опарное дрожжевое творог фрукты тесто начинка грибы Пресное повидло икра Выбор идеального варианта. Я выбрала из предложенных вариантов блинчики с ягодами и дрожжевое тесто. Некоторые национальные рецепты требуют добавления в тесто дрожжей или пищевой соды. Тесто не должно быть густым. | |||
35660. | Украшение дня рождения в японском стиле | 919.84 KB | |
Оценка стоимости изделия 10. Оценка изделия 11. Оценка проектирования Определение потребности Приближался мой день рождения. Планирование работы Распределить ткань на стены; Нарисовать акриловой черной краской на ткане иероглифы; В некоторых местах подвязать ткань красивой лентой; Оборудовать стол Надуть шарики Оценка стоимости изделия Для украшения стен нам понадобилось купить: ленты на ленты мы потратили 120 рублей; ткань стоимость ткани не обсчитывается т. | |||
35661. | Лоскутное шитье. Творческий проект | 749.98 KB | |
Процесс выращивания этих культур обработки сырья был очень длительным и трудоемким поэтому ткани ткали шириной 40 см соответственно крою рубахи или полотенца чтобы при шитье практически не оставалось отходов. Ширина ситцевого полотна составляла 7580 см то есть была шире кроя одежды и смекалистые хозяйки оставшимися лоскутками стали удлинять детскую одежду украшать подолы рубах рукава и оплечья. Расчет себестоимости наименование использованных материалов цена руб. расход материалов на изделие затраты на материалыруб. | |||