Как ввести формулу в ячейку Excel
У почти всех начинающих юзеров Excel часто возникает вопросец: что такое формула Excel и как ее ввести в ячейку. Почти все даже задумываются, для чего она нужна. По их воззрению, Excel – это электрическая таблица. Но по сути, это большенный функциональный калькулятор и, даже, в некий степени, среда программирования.
Понятие формулы и функции
И вся работа в Excel строится на формулах, коих большущее количество. В базе хоть какой формулы лежит функция. Это базисный вычислительный инструмент, который возвращает значение в зависимости от передаваемых данных опосля их подготовительной обработки.
Формула же – это совокупа логических операторов, арифметических операций и функций. Она не постоянно содержит все эти элементы. Вычисление может включать в себя, к примеру, лишь математические операции.
В ежедневной речи юзеры Excel нередко путают эти понятия. По сути, грань меж ними довольно условная, и часто употребляются оба термина. Тем не наименее, для наиболее правильного осознания работы с Excel знать правильные значения нужно.
Определения, касающиеся формул
По сути, терминологический аппарат существенно обширнее и включает огромное количество остальных понятий, которые необходимо разглядеть наиболее тщательно.
- Константа. Это значение, которое остается схожим, и его нереально поменять. Таковым быть может, к примеру, число Пи.
- Операторы. Это модуль, нужный для выполнения определенных операций. Excel предугадывает три вида операторов:
- Арифметический. Нужен для того, чтоб сложить, вычитать, разделять и множить несколько чисел.
- Оператор сопоставления. Нужен для того, чтоб проверить, соответствуют ли данные определенному условию. Может возвращать одно значение: либо правду, либо ересь.
- Текстовый оператор. Он лишь один, и нужен, чтоб соединять воединыжды данные – &.
Как вводить данные в ячейку
Один из самых обычных методов вставки формулы, содержащей функцию – это пользоваться Мастером функций. Чтоб его вызвать, нужно кликнуть по значку fx незначительно левее строчки формул (она находится над таблицей, и в ней дублируется содержимое ячейки, если формулы в ней нет либо показывается формула, если она есть. Покажется такое диалоговое окно.
Там можно избрать категорию функции и конкретно ту из перечня, которую необходимо применять в определенной ячейке. Там можно поглядеть не только лишь список, да и что любая из функций делает.
2-ой метод ввода формул – пользоваться соответственной вкладкой на ленте Excel.
2
Тут интерфейс иной, но механика та же. Все функции распределены по категориям, и юзер может сам избрать ту, которая ему больше всего подступает. Чтоб поглядеть, что любая из функций делает, нужно навести на нее курсором мыши и подождать 2 секунды.
Также можно конкретно вводить функцию в ячейку. Для этого нужно в ней начать писать знак ввода формулы (символ равно =) и вводить заглавие функции вручную. Этот метод подойдет для наиболее опытнейших юзеров, которые знают ее назубок. Дозволяет существенно сберечь время.
3
Опосля ввода первых букв будет показан список, в котором также можно избрать нужную функцию и вставить ее. Если нет способности применять мышь, то можно передвигаться по этому списку при помощи клавиши TAB. Если же она есть, то довольно просто двойного клика по соответственной формуле. Опосля того, как функция будет выбрана, покажется подсказка, позволяющая ввести данные в правильной последовательности. Эти данные именуются аргументами функции.
4
Если же вы до сего времени используете Excel 2003 версии, то в нем не предвидено выпадающего перечня, потому нужно держать в голове четкое заглавие функции и вводить данные на память. То же касается и всех аргументов функции. К счастью, для опытнейшего юзера это не является неувязкой.
Принципиально постоянно начинать формулу со знака равно, по другому Excel будет мыслить, что в ячейке содержится текст.
При всем этом формулой будут считаться и те данные, которые начинаются со знака плюс либо минус. Если опосля этого будет в ячейке текст, то Excel выдаст ошибку #ИМЯ?. Если же приводятся числа либо числа, то Excel попробует выполнить надлежащие математические операции (сложение, вычитание, умножение, деление). В любом случае, рекомендуется начинать ввод формулы со знака =, так как так принято.
Аналогично, можно начинать запись функции со знака @, который будет автоматом изменен. Таковой метод ввода считается устаревшим и нужен для того, чтоб старенькые версии документов не утратили часть функциональности.
Понятие аргументов функции
Практически все функции содержат аргументы, в качестве которых может выступать ссылка на ячейку, текст, число и даже иная функция. Так, если применять функцию ЕНЕЧЕТ, нужно будет указать числа, которые будут проверяться. Возвратится логическое значение. Если оно число нечетное, будет возвращено значение «ИСТИНА». Соответственно, если четное, то «ЛОЖЬ». Аргументы, как видно из снимков экрана выше, вводятся в скобках, а делятся через точку с запятой. При всем этом если употребляется английская версия программки, то разделителем служит рядовая запятая.
Введенный аргумент именуется параметром. Некие функции не содержат их совершенно. К примеру, чтоб получить в ячейке текущее время и дату, нужно написать формулу = ТДАТА() . Как лицезреем, если функция не просит ввода аргументов, скобки все равно необходимо указать.
Некие индивидуальности формул и функций
Если данные в ячейке, на которую ссылается формула, редактируется, она автоматом делает пересчет данных, соответственно изменениям. Представим, у нас есть ячейка A1, в которую записывается обычная формула, содержащая обыденную ссылку на ячейку =D1 . Если в ней поменять информацию, то аналогичное значение будет отображено и в ячейке A1. Аналогично и для наиболее сложных формул, которые берут данные из определенных ячеек.
Принципиально осознавать, что обычными способами Excel нереально создать так, чтоб ячейка возвращала свое значение в другую ячейку. При всем этом данной для нас задачки можно достигнуть, использовав макросы – подпрограммы, которые делают определенные деяния в документе Excel. Но это совершенно иная тема, которая очевидно не для новичков, так как она просит способностей программирования.
Понятие формулы массива
Это один из вариантов формулы, который вводится незначительно остальным методом. Но почти все не знают, что же все-таки это такое. Потому давайте в первую очередь разберемся со значением этого термина. Существенно проще разобраться в этом на примере.
Представим, у нас есть формула СУММ, которая возвращает сумму значений определенного спектра.
Давайте сделаем таковой простой спектр, записав в ячейки A1:A5 числа от 1-го до 5. Потом укажем функцию =СУММ(A1:A5) в ячейке B1. В итоге, там покажется число 15.
Это уже и есть формула массива? Нет, хотя она и работает с набором данных и могла бы называться такой. Давайте внесем некие конфигурации. Представим, нам необходимо при всем этом еще к любому аргументу добавлять единицу. Для этого функцию необходимо создать таковой:
=СУММ(A1:A5+1). Выходит, что мы желаем к спектру значений добавить единицу перед тем, как подсчитать их сумму. Да и в таком виде Excel не захотит этого созодать. Ему необходимо показать это, использовав формулу Ctrl + Shift + Enter. Формула массива различается наружным видом и смотрится последующим образом:
Опосля этого в нашем случае будет введен итог 20.
Нет смысла вводить фигурные скобки вручную. Это ничего не даст. Напротив, Excel даже не помыслит, что это функция и просто текст заместо формулы.
Снутри данной для нас функции, тем временем, осуществлялись последующие деяния. Поначалу программка раскладывает этот спектр на составляющие. В нашем случае – это 1,2,3,4,5. Дальше Excel автоматом наращивает каждую из их на единицу. Позже приобретенные числа складываются.
Еще есть один вариант, когда формула массива в состоянии сделать то, что обычная не может. К примеру, у нас есть набор данных, перечисленных в спектре A1:A10. В обычном случае будет получен ноль. Но представим, у нас таковая ситуация, что ноль в учет брать недозволено.
Давайте введем такую формулу, которая инспектирует спектр на предмет того, чтоб он не был равен этому значению.
Тут возникает неверное чувство, что хотимого результата достигнуть получится. Но это не так, так как тут необходимо внедрение формулы массива. В приведенной выше формуле будет проверяться лишь 1-ый элемент, что, естественно, нам не подступает.
А вот если перевоплотить ее в формулу массива, расклад может стремительно поменяться. Сейчас меньшее значение будет 1.
Преимущество формулы массива к тому же в том, что ею быть может возвращено несколько значений. К примеру, можно транспонировать таблицу.
Таковым образом, различных видов формул бывает весьма много. Одни из их требуют наиболее обычного ввода, остальные – сложного. В особенности томными для восприятия новенькими могут быть формулы массива, но они весьма полезны.
Знак в формуле в эксель
Модуль 2
«Формулы»Ссылки. Типы ссылок(относительные, абсолютные, смешанные). Виды представления ссылок. Именованные ссылки. Формулы в Microsoft Excel. Внедрение текста в формулах. Внедрение ссылок в формулах. Операторы. Арифметические операторы. Логические операторы. Оператор объединения 2-х строк текста в одну. Операторы ссылок. Выражения.
Ссылки
Ссылка в Microsoft Excel — адресок ячейки либо связного спектра ячеек. В любом листе Excel быть может 256 (2 в 8 степени) столбцов и 65536 (2 в 16 степени) строк. Адресок ячейки определяется пересечением столбца и строчки, к примеру: A1, C16. Адресок спектра ячеек задается адресом верхней левой ячейки и нижней правой, к примеру: A1:C5.
Типы ссылок (типы адресации):
Ссылки в Excel бывают 3-х типов:
- Относительные ссылки (пример: A1);
- Абсолютные ссылки (пример: $A$1);
- Смешанные ссылки (пример: $A1 либо A$1, они наполовину относительные, наполовину абсолютные).
Знак $ (бакс) тут никакого дела к валютным единицам не имеет, это только метод указать Excel тип ссылки. Различия меж различными типами ссылок можно узреть, если потянуть за маркер автозаполнения активной ячейки либо спектра ячеек, содержащих формулу со ссылками.
Относительные ссылки
Если поставить в какой то ячейке символ «=», потом щелкнуть левой клавишей мыши на какой то ячейке, Excel подставляет опосля «=» относительную ссылку на эту ячейку. Всякий раз, когда мы тянем за маркер автозаполнения либо копируем Формулу, содержащую относительные ссылки, Excel пересчитывает адреса всех относительных ссылок в ней в согласовании с новеньким положением ячейки.
Абсолютные ссылки
Чтоб создать относительную ссылку абсолютной, довольно поставить символ «$» перед буковкой столбца и адресом строчки, к примеру $A$1. Наиболее резвый метод — выделить относительную ссылку и надавить один раз кнопку «F4», при всем этом Excel сам проставит символ «$». Если 2-ой раз надавить «F4», ссылка станет смешанной такового типа A$1, если 3-ий раз — такового $A1, если в 4-ый раз — ссылка снова станет относительной. И так по кругу.
Смешанные ссылки
Смешанные ссылки являются наполовину абсолютными и наполовину относительными. Знак бакса в их стоит либо перед буковкой столбца либо перед номером строчки.
К примеру, в ячейке записана формула «=A$1». Ссылка A$1 относительная по столбцу A и абсолютная по строке 1. Если мы потянем за маркер автозаполнения эту формулу вниз либо ввысь, то ссылки во всех скопированных форумулах будут указывать на ячейку A1, другими словами будет вести себя как абсолютные. Но, если потянем на право либо на лево — ссылки будут вести себя как относительные, другими словами Excel будет пересчитывать их адресок. Таковым образом, формулы, сделанные автозаполнением, будут применять один и этот же номер строчки ($1), но поменяется номер столбца (A, B, C. ).Виды представления ссылок
Есть два вида представления ссылок в 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] — на лево). В общем случае направления вниз и на право числятся положительными, ввысь и на лево — отрицательными.
Если включен традиционный вид и в ячейке с адресом A1 находится формула «=$B$3» ($B$3 — абсолютная ссылка), то опосля переключения в вид R1C1 она воспримет вид «=R3C2». Квадратных скобок нет, другими словами обозначено не относительно смещение по отношению к положению формула, а абсолютное смещение по отношению к всему листу.
Именованные ссылки
Для упрощения работы с адресом ячейки либо спектра ячеек можно отдать ему имя, и потом обращаться к ячейке либо спектру не по его адресу, а по символьному имени. Именованные ссылки можно применять всюду, где можно применять то значение, на которое показывает ссылка.
Для сотворения именованной ссылки необходимо выделить подходящую ячейку либо спектр, потом щелкнуть в текстовое поле (см. рис. ниже) ввести свое имя и надавить Enter. Можно применять российские имена. Имя не может содержать пробелов и начинаться с числа. Именованная ссылка может ссылаться на бессвязный спектр ячеек (выделенный с «Ctrl»).
Для вставки именованной ссылки можно пользоваться клавишей со стрелкой вниз:
либо надавить кнопку «F3», раскроется последующее окно:
Пример использования: «=СУММ(tablica_1);»
Формулы в Microsoft Excel
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).
Арифметические операторы
- «+» — сложение (Пример: «=1+1»);
- «-» — вычитание (Пример: «=1-1»);
- «*» — умножение (Пример: «=2*3»);
- «/» — Деление (Пример: «=1/3»);
- «^» — Возведение в степень (Пример: «=2^10»);
- «%» — Процент (Пример: «=3 %» — преобразуется в 0,03; «=37*8 %» — отыскали 8 % от 37). Другими словами если мы дописываем опосля числа символ «%», то число делится на 100.
Логические операторы
- «>» — больше;
- «<» — меньше;
- «>=» — больше, или равно;
- «< <>» — неравно (проверка на неравенство).
Оператор объединения 2-х строк текста в одну
Оператор «&» (амперсанд) служит для «склеивания» меж собой 2-ух текстовых строк. К примеру, в ячейке A1 текст «мать», в ячейке A2 текст «мыла раму». В A3 пишем формулу «=A1 & A2». В итоге в ячейке A3 покажется текст «мамамыла раму». Как лицезреем, пробел меж 2-мя строчками автоматом не ставится. Чтоб вставить этот пробел, необходимо поменять формулу вот так: «=A1 & » » & A2».
Операторы ссылок
- : (двоеточие). Ставится меж ссылками на первую и последнюю ячейку спектра. Такое сочетание является ссылкой на спектр (A1:A15);
- ; (точка с запятой). Соединяет воединыжды несколько ссылок в одну ссылку (СУММ(A1:A15;B1:B15));
- (пробел). Оператор пересечения множеств. Служит для ссылки на общие ячейки 2-ух диапазонов (B7:D7 C6:C8).
Выражения
Выражения в Excel бывают арифметические и логические. Арифметическое выражение (к примеру, «=2*(2+5)», итог — 14) в итоге дает числовое значение (положительное, отрицательное, дробное число). Логическое выражение (к примеру, «=3>5», итог — логическое значение «ЛОЖЬ»)в итоге может отдать только 2 значения: «ЛОЖЬ» либо «ИСТИНА» (одно число или больше другого, или не больше, остальных вариантов нет).
