Здравствуйте!
Многие кто не пользуются Excel - даже не представляют, какие возможности дает эта программа! Подумать только: складывать в автоматическом режиме значения из одних формул в другие, искать нужные строки в тексте, складывать по условию и т.д. - в общем-то, по сути мини-язык программирования для решения "узких" задач (признаться честно, я сам долгое время Excel не рассматривал за программу, и почти его не использовал)...
В этой статье хочу показать несколько примеров, как можно быстро решать повседневные офисные задачи: что-то сложить, вычесть, посчитать сумму (в том числе и с условием), подставить значения из одной таблицы в другую и т.д. То есть эта статья будет что-то мини гайда по обучению самого нужного для работы (точнее, чтобы начать пользоваться Excel и почувствовать всю мощь этого продукта!).
Возможно, что прочти подобную статью лет 15-17 назад, я бы сам намного быстрее начал пользоваться Excel (и сэкономил бы кучу своего времени для решения "простых" (прим.: как я сейчас понимаю) задач)...
Примечание : все скриншоты ниже представлены из программы Excel 2016 (как самой новой на сегодняшний день).
Многие начинающие пользователи, после запуска Excel - задают один странный вопрос: "ну и где тут таблица?". Между тем, все клеточки, что вы видите после запуска программы - это и есть одна большая таблица!
Теперь к главному: в любой клетке может быть текст, какое-нибудь число, или формула. Например, ниже на скриншоте показан один показательный пример:
Просто число (слева) и посчитанная формула (справа)
Суть в том, что Excel может считать как калькулятор, если выбрать какую нибудь ячейку, а потом написать формулу, например "=3+5+8" (без кавычек). Результат вам писать не нужно - Excel посчитает его сам и отобразит в ячейке (как в ячейке C1 в примере выше)!
Но писать в формулы и складывать можно не просто числа, но и числа, уже посчитанные в других ячейках. На скриншоте ниже в ячейке A1 и B1 числа 5 и 6 соответственно. В ячейке D1 я хочу получить их сумму - можно написать формулу двумя способами:
Сложение ячеек, в которых уже есть числа
Распространение формулы на другие ячейки
В примере выше мы сложили два числа в столбце A и B в первой строке. Но строк то у нас 6, и чаще всего в реальных задачах сложить числа нужно в каждой строке! Чтобы это сделать, можно:
Кстати, обратите внимание на то, что Excel сам подставил формулы в каждую строку. То есть, если сейчас вы выберите ячейку, скажем, D2 - то увидите формулу "=A2+B2" (т.е. Excel автоматически подставляет формулы и сразу же выдает результат) .
Как задать константу (ячейку, которая не будет меняться при копировании формулы)
Довольно часто требуется в формулах (когда вы их копируете), чтобы какой-нибудь значение не менялось. Скажем простая задача: перевести цены в долларах в рубли. Стоимость рубля задается в одной ячейке, в моем примере ниже - это G2.
Далее в ячейке E2 пишется формула "=D2*G2" и получаем результат. Только вот если растянуть формулу, как мы это делали до этого, в других строках результата мы не увидим, т.к. Excel в строку 3 поставит формулу "D3*G3", в 4-ю строку: "D4*G4" и т.д. Надо же, чтобы G2 везде оставалась G2...
Чтобы это сделать - просто измените ячейку E2 - формула будет иметь вид "=D2*$G$2". Т.е. значок доллара $ - позволяет задавать ячейку, которая не будет меняться, когда вы будете копировать формулу (т.е. получаем константу, пример ниже)...
Можно, конечно, составлять формулы в ручном режиме, печатая "=A1+B1+C1" и т.п. Но в Excel есть более быстрые и удобные инструменты.
Один из самых простых способов сложить все выделенные ячейки - это использовать опцию автосуммы (Excel сам напишет формулу и вставить ее в ячейку).
Довольно часто при работе требуется не просто сумма всего столбца, а сумма определенных строк (т.е. выборочно). Предположим простую задачу: нужно получить сумму прибыли от какого-нибудь рабочего (утрировано, конечно, но пример более чем реальный).
Я в своей таблицы буду использовать всего 7 строк (для наглядности), реальная же таблица может быть намного больше. Предположим, нам нужно посчитать всю прибыль, которую сделал "Саша". Как будет выглядеть формула:
Примечание : условий может быть несколько и проверять их можно по разным столбцам.
Довольно типичная задача: посчитать не сумму в ячейках, а количество строк, удовлетворяющих какомe-либо условию. Ну, например, сколько раз имя "Саша" встречается в таблице ниже (см. скриншот). Очевидно, что 2 раза (но это потому, что таблица слишком маленькая и взята в качестве наглядного примера). А как это посчитать формулой? Формула:
"=СЧЁТЕСЛИ(A2:A7 ;A2 ) " - где:
Результат показан в правой части на скрине ниже.
Теперь представьте более расширенную задачу: нужно посчитать строки где встречается имя "Саша", и где в столбце И - будет стоять цифра "6". Забегая вперед, скажу, что такая строка всего лишь одна (скрин с примером ниже).
Формула будет иметь вид:
=СЧЁТЕСЛИМН(A2:A7 ;A2 ;B2:B7 ;"6") (прим.: обратите внимание на кавычки - они должны быть как на скрине ниже, а не как у меня) , где:
A2:A7 ;A2 - первый диапазон и условие для поиска (аналогично примеру выше);
B2:B7 ;"6" - второй диапазон и условие для поиска (обратите внимание, что условие можно задавать по разному: либо указывать ячейку, либо просто написано в кавычках текст/число).
Тоже довольно распространенный вопрос, с которым часто сталкиваюсь. Вообще, насколько я себе представляю, возникает он чаще всего - из-за того, что люди путаются и не знают, что от чего ищут процент (да и вообще, плохо понимают тему процентов (хотя я и сам не большой математик, и все таки... )).
Самый простой способ, в котором просто невозможно запутаться - это использовать правило "квадрата", или пропорции. Вся суть приведена на скрине ниже: если у вас есть общая сумма, допустим в моем примере это число 3060 - ячейка F8 (т.е. это 100% прибыль, и какую то ее часть сделал "Саша", нужно найти какую...).
По пропорции формула будет выглядеть так: =F10*G8/F8 (т.е. крест на крест: сначала перемножаем два известных числа по диагонали, а затем делим на оставшееся третье число). В принципе, используя это правило, запутаться в процентах практически невозможно .
Собственно, на этом я завершаю данную статью. Не побоюсь сказать, что освоив все, что написано выше (а приведено здесь всего лишь "пяток" формул) - Вы дальше сможете самостоятельно обучаться Excel, листать справку, смотреть, экспериментировать, и анализировать. Скажу даже больше, все что я описал выше, покроет многие задачи, и позволит решать самое распространенное, над которым часто ломаешь голову (если не знаешь возможности Excel), и не знаешь как быстрее это сделать...
Формулы в Excel - это его основная суть, то, ради чего и была создана эта программа компанией Microsoft. Формулы позволяют произвести расчеты значений ячеек на основе данных других ячеек, причем если исходные данные поменяются, то результат вычислений в ячейке, где стоит формула пересчитается автоматически!
Рассмотрим работу формул на самом простом примере - сумме двух чисел. Пусть в одной ячейке Excel введено число 2, а в другой 3. Нужно, чтобы в третье ячейке появилась сумма этих чисел.
Суммой 2 и 3 является, конечно же, 5, но вносить пятерку вручную в следующую ячейку не надо, иначе теряется смысл расчетов в Excel. В ячейку с итогом необходимо ввести формулу суммы и тогда результат будет вычислен программой автоматически.
В примере вычисление выглядит простым, но когда числа большие или дробные без формулы просто не обойтись.
Фомулы в Excel могут содержать арифметические операции (сложение +, вычитание -, умножение *, деление /), координаты ячеек исходных данных (как по отдельности, так и диапазон) и функции вычисления.
Рассмотрим формулу для суммы чисел в примере выше:
СУММ(A2;B2)
Каждая формула начинается со знака «равно». Если Вы хотите добавить в ячейку формулу, написав ее вручную, то именно этот знак следует написать первым.
Далее в примере идет функция СУММ, которая означает что необходимо произвести суммирование некоторых данных, а уже в скобках у функции, разделенные точкой с запятой, указываются некоторые аргументы, в данном случае координаты ячеек (A2 и B2), значения которых необходимо сложить и поместить результат в ту ячейку, где написана формула. Если бы Вам требовалось сложить три ячейки, то можно было бы написать три аргумента у функции СУММ, разделяя их точкой с запятой, например:
СУММ(А4;B4;C4)
Когда требуется сложить большое количество ячеек, то указывать каждую из них в формуле займет очень много времени, поэтому вместо простого перечисления можно использовать указание диапазона ячеек:
СУММ(B2:B7)
Диапазон ячеек в Экселе указывается с помощью координат первой и последней ячеек, разделенных знаком «двоеточие». В данном примере производится сложение значений ячеек, начиная с ячейки B2 до ячейки B7.
Функции в формулах можно соединять и комбинировать как Вам необходимо для получение требуемого результата. Например, стоит задача сложить три числа и в зависимости от того, меньше ли результат числа 100 или больше, домножить сумму на коэффициент 1.2 или 1.3. Решить задачу поможет следующая формула:
ЕСЛИ(СУММ(А2:С2)
Разберем решение задачи подробнее. Использовалось две функции ЕСЛИ и СУММ. Функция ЕСЛИ всегда имеет три аргумента: первый - условие, второй - действие в случае, если условие верно, третий - действие в случае, если условие неверно. Напоминаем, что аргументы разделяются знаком «точка с запятой».
ЕСЛИ(условие; верно; неверно)
В качестве условия указано, что сумма диапазона ячеек A2:C2 меньше 100. Если при расчете, условие выполнится и сумма ячеек диапазона будет равна, например, 98, то Эксель выполнить действие указанное во втором аргументе функции ЕСЛИ, т.е. СУММ(А2:С2)*1,2. В случае же, если сумма превысит число 100, то выполнится уже действие в третьем аргументе функции ЕСЛИ, т.е. СУММ(А2:С2)*1,3.
Функций в Excel огромное количество и знать все просто невозможно. Некоторые часто используемые запомнить можно, а некоторые Вам понадобятся лишь изредка и помнить их название и тем более форму записи очень сложно.
Но в Экселе имеется стандартный способ вставки функций с их полным списком. Если Вы хотите добавить какую-то функцию в ячейку, то кликните по ячейке и выберите в главном меню вставку функции. Программа отобразит список функций и Вы сможете выбрать ту, которая необходима для решения задачи.
Чтобы вставить функцию в Excel 2007 выберите в главном меню пункт «Формулы» и кликните на значок «Вставить функцию», либо нажмите на клавиатуре комбинацию клавиш Shift+F3.
В Excel 2003 функция вставляется через меню «Вставка»->«Функция». Так же работает и комбинация клавиш Shift+F3.
В ячейке на которой стоял курсор появится знак равенства, а поверх листа отобразится окно «Мастер функций».
Функция в Excel разделены по категориям. Если Вы знаете к какой категории может относится предполагаемая Вами функция, то выбирайте отбор по ней. В противном случае выберите «Полный алфавитный перечень». Программа отобразит все имеющиеся функции в списке функций.
Пролистывайте список и выделяйте мышью наименование, заинтересовавшей Вас функции. Чуть ниже списка появится ее форма записи, требуемые аргументы и краткое описание, которое разъяснит Вам предназначение функции. Когда найдете то, что нужно, кликните по кнопке «OK» для перехода к указанию аргументов.
В окне аргументов имеются поля с названиями «Число 1», «Число 2» и т.д. Их необходимо заполнить координатами ячеек (либо диапазонами) в которых требуется взять данные. Заполнять можно вручную, но гораздо удобнее нажать в конце поля на значок таблицы для того, чтобы указать исходную ячейку или диапазон.
Окно аргументов примет упрощенный вид. Теперь необходимо кликнуть мышью на первую исходную ячейку с данными, а затем снова на значок таблица в окне аргументов.
Поле «Число 1» заполнится координатами выбранной ячейки. Ту же самую процедуру следует проделать для поля «Число 2» и для следующих полей, если число аргументов функции у вас более двух.
Заполнив все аргументы, Вы уже можете предварительно посмотреть результат расчета полученной формулы. Чтобы он появился в ячейке на листе, нажмите кнопку «OK». В рассмотренном примере в ячейку D2 помещено произведение чисел в ячейках B2 и C2.
Рассмотренный способ вставки функции является универсальным и позволяет добавлять любую функцию из общего списка стандартных функций Excel.
Нравится |
Формулы в Excel – одно из самых главных достоинств этого редактора. Благодаря им ваши возможности при работе с таблицами увеличиваются в несколько раз и ограничиваются только имеющимися знаниями. Вы сможете сделать всё что угодно. При этом Эксель будет помогать на каждом шагу – практически в любом окне существуют специальные подсказки.
Для создания простой формулы достаточно следовать следующей инструкции:
При этом затронутые ячейки всегда подсвечиваются. Это делается для того, чтобы вы не ошиблись с выбором. Визуально увидеть ошибку проще, чем в текстовом виде.
В качестве примера приведём следующее выражение.
Оно состоит из:
Операторы в редакторе Excel указывают какие именно операции нужно выполнить над указанными элементами формулы. При вычислении всегда соблюдается один и тот же порядок:
К ним относятся:
Если перед числом поставить «минус», то оно примет отрицательное значение, но по модулю останется точно таким же.
Данные операторы применяются для сравнения значений. В результате операции возвращается ИСТИНА или ЛОЖЬ. К ним относятся:
Для этой цели используется специальный символ «&» (амперсанд). При помощи его можно соединить различные фрагменты в одно целое – тот же принцип, что и с функцией «СЦЕПИТЬ». Приведем несколько примеров:
Любой текст, кроме ссылок, необходимо указывать в кавычках. Иначе формула выдаст ошибку.
Обратите внимание, что кавычки используют именно такие, как на скриншоте.
Для определения ссылок можно использовать следующие операторы:
Поскольку только она попадает под определение «пересечения множеств». Именно такое название носит данный оператор (пробел).
Во время работы в редакторе Excel можно использовать ссылки различных видов. При этом большинство начинающих пользователей умеют пользоваться только самыми простыми из них. Мы вас научим, как правильно вводить ссылки всех форматов.
Как правило, данный вид используют чаще всего, поскольку их составлять намного удобнее, чем остальные.
Приведем несколько примеров:
Иногда в формулах используется информация с других листов. Работает это следующим образом.
=СУММ(Лист2!A5:C5)На втором листе указаны следующие данные.
Если в названии листа есть пробел, то в формуле его нужно указывать в одинарных кавычках (апострофы).
=СУММ("Лист номер 2"!A5:C5)Редактор Эксель работает с тремя видами ссылок:
Рассмотрим их более внимательно.
Все указанные ранее примеры принадлежат к относительному адресу ячеек. Данный тип самый популярный. Главное практическое преимущество в том, что редактор во время переноса изменит ссылки на другое значение. В соответствии с тем, куда именно вы скопировали эту формулу. Для подсчета будет учитываться количество клеток между старым и новым положением.
Представьте, что вам нужно растянуть эту формулу на всю колонку или строку. Вы же не будете вручную изменять буквы и цифры в адресах ячеек. Работает это следующим образом.
Если таблица очень большая, лучше кликнуть на правый нижний угол и, не отпуская пальца, протянуть указатель до конца. Если данных мало, то копировать при помощи горячих клавиш намного быстрее.
Если вы хотите, чтобы при переносе формул все ссылки сохранялись (то есть чтобы они не менялись в автоматическом режиме), нужно использовать абсолютные адреса. Они указываются в виде «$B$2».
=СУММ($B$4:$B$9)В итоге мы видим, что изменений никаких не произошло. Во всех столбцах у нас отображается одно и то же число.
Данный тип адресов используется тогда, когда необходимо зафиксировать только столбец или строку, а не всё одновременно. Использовать можно следующие конструкции:
Работают с такими формулами только тогда, когда это необходимо. Например, если вам нужно работать с одной постоянной строкой данных, но при этом изменять только столбцы. И самое главное – если вы собираетесь рассчитать результат в разных ячейках, которые не расположены вдоль одной линии.
Дело в том, что когда вы скопируете формулу на другую строку, то в ссылках цифры автоматически изменятся на количество клеток от исходного значения. Если использовать смешанные адреса, то всё останется на месте. Делается это следующим образом.
Под понятие «трёхмерные» попадают те адреса, в которых указывается диапазон листов. Пример формулы выглядит следующим образом.
=СУММ(Лист1:Лист4!A5)В данном случае результат будет соответствовать сумме всех ячеек «A5» на всех листах, начиная с 1 по 4. При составлении таких выражений необходимо придерживаться следующих условий:
Если нарушить эти правила, то вы увидите какую-нибудь ошибку.
Данный тип ссылок от «A1» отличается тем, что номер задается не только строкам, но и столбцам. Разработчики решили заменить обычный вид на этот вариант для удобства в макросах, но их можно использовать где угодно. Приведем несколько примеров таких адресов:
Программа Excel для обозначения диапазонов ячеек, одиночных ячеек, таблиц (обычные и сводные), констант и выражений позволяет создавать свои уникальные имена. При этом для редактора никакой разницы при работе с формулами нет – он понимает всё.
Имена вы можете использовать для умножения, деления, сложения, вычитания, расчета процентов, коэффициентов, отклонения, округления, НДС, ипотеки, кредита, сметы, табелей, различных бланков, скидки, зарплаты, стажа, аннуитетного платежа, работы с формулами «ВПР», «ВСД», «ПРОМЕЖУТОЧНЫЕ.ИТОГИ» и так далее. То есть можете делать, что угодно.
Главным условием можно назвать только одно – вы должны заранее определить это имя. Иначе Эксель о нём ничего знать не будет. Делается это следующим образом.
Точно так же можно присвоить имя какой-нибудь ячейке, тексту или числу.
Использовать информацию в таблице можно как при помощи имён, так и при помощи обычных ссылок. Так выглядит стандартный вариант.
А если попробовать вместо адреса «D4:D9» вставить наше имя, то вы увидите подсказку. Достаточно написать несколько знаков, и вы увидите, что подходит (из базы имён) больше всего.
В нашем случае всё просто – «столбец_3». А представьте, что у вас таких имён будет большое множество. Все наизусть вы запомнить не сможете.
В редакторе Excel вставить функцию можно несколькими способами:
Рассмотрим каждый метод более внимательно.
В этом случае всё просто – вы при помощи рук, собственных знаний и умений вводите формулы в специальной строке или прямо в ячейке.
Если же у вас нет рабочего опыта в этой области, то лучше поначалу использовать более облегченные методы.
В этом случае необходимо:
Применить его можно следующим образом:
Достаточно забить какое-нибудь слово, которым можно описать то, что вы хотите сделать, а редактор попробует вывести все подходящие варианты.
В качестве примера будем использовать формулы с логическими условиями. Для этого нам нужно будет добавить какую-нибудь таблицу.
Затем придерживайтесь следующей инструкции:
Как видите, копирование произошло весьма успешно, поскольку мы использовали относительные ссылки, о которых мы говорили ранее. Если же вам нужно закрепить адреса в аргументах функций, тогда используйте абсолютные значения.
Сделать это можно несколькими способами: использовать строку формул или специальный мастер. В первом случае всё просто – кликаете в специальное поле и вручную вводите нужные изменения. Но писать там не совсем удобно.
Единственное, что вы можете сделать, это увеличить поле для ввода. Для этого достаточно кликнуть на указанную иконку или нажать на сочетание клавиш Ctrl +Shift +U .
Стоит отметить, что это единственный способ, если вы не используете в формуле функции.
В случае использования функций всё становится намного проще. Для редактирования необходимо следовать следующей инструкции:
Для того чтобы удалить какое-нибудь выражение, достаточно сделать следующее:
Добиться точно такого же результата можно и при помощи инструмента «Очистить всё».
Ниже перечислены самые популярные ошибки, которые допускаются пользователями:
При работе с формулой вы можете увидеть следующие варианты ошибок:
При редактировании формулы (ячейки подсвечиваются) вы увидите, что они на самом деле не пересекаются.
Иногда можно увидеть много символов #, которые полностью заполняют ячейку по ширине. На самом деле тут ошибки нет. Это означает, что вы работаете с числами, которые не помещаются в данную клетку.
Для того чтобы увидеть содержащееся там значение, достаточно изменить размер столбца.
Кроме этого, можно использовать форматирование ячеек. Для этого необходимо выполнить несколько простых шагов:
Благодаря этому редактор Эксель сможет перевести это число в другой формат, который умещается в данном столбце.
Редактор Microsoft Excel позволяет обрабатывать информацию любым удобным для вас способом. Для этого есть все необходимые условия и возможности. Рассмотрим несколько примеров формул по категориям. Так вам будет проще разобраться.
Для того чтобы оценить математические возможности Экселя, нужно выполнить следующие действия.
Обратите внимание, что символ процента ставится в конце, а не в начале. Кроме этого, при работе с процентами не нужно дополнительно умножать на 100. Это происходит автоматически.
При помощи Экселя можно рассчитывать различные функции и строить по ним графики, а затем проводить графический анализ. Как правило, подобные приёмы используются в презентациях.
В качестве примера попробуем построить графики для экспоненты и какого-нибудь уравнения. Инструкция будет следующей:
Как мы и говорили ранее, прирост экспоненты происходит намного быстрее, чем у обычного кубического уравнения.
Подобным образом можно представить графически любую функцию или математическое выражение.
Всё описанное выше подходит для современных программ 2007, 2010, 2013 и 2016 года. Старый редактор Эксель значительно уступает в плане возможностей, количества функций и инструментов. Если откроете официальную справку от Microsoft, то увидите, что они дополнительно указывают, в какой именно версии программы появилась данная функция.
Во всём остальном всё выглядит практически точно так же. В качестве примера, посчитаем сумму нескольких ячеек. Для этого необходимо:
В данном самоучителе мы рассказали обо всем, что связано с формулами в редакторе Excel, – от самого простого до очень сложного. Каждый раздел сопровождался подробными примерами и пояснениями. Это сделано для того, чтобы информация была доступной даже для полных чайников.
Если у вас что-то не получается, значит, вы допускаете где-то ошибку. Возможно, у вас есть опечатки в выражениях или же указаны неправильные ссылки на ячейки. Главное понять, что всё нужно вбивать очень аккуратно и внимательно. Тем более все функции не на английском, а на русском языке.
Кроме этого, важно помнить, что формулы должны начинаться с символа «=» (равно). Многие начинающие пользователи забывают про это.
Для того чтобы вам было легче разобраться с описанными ранее формулами, мы подготовили специальный демо-файл, в котором составлялись все указанные примеры. Вы можете его с нашего сайта совершенно бесплатно. Если во время обучения вы будете использовать готовую таблицу с формулами на основании заполненных данных, то добьетесь результата намного быстрее.
Если наше описание вам не помогло, попробуйте посмотреть приложенное ниже видео, в котором рассказываются основные моменты более детально. Возможно, вы делаете всё правильно, но что-то упускаете из виду. С помощью этого ролика вы должны разобраться со всеми проблемами. Надеемся, что подобные уроки вам помогли. Заглядывайте к нам чаще.
Строкой формул называется специальная строка, расположенная над заголовками столбцов и предназначенная для ввода и редактирования формул и иной информации. Фрагмент строки формул представлен на рисунке 3.4.
Рисунок 3.4 - Строка формул
Строка формул состоит из двух основных частей: адресной строки, которая расположена слева, и строки ввода и отображения информации. На рисунке 3.4 в адресной строке отображается имя последней использованной функции (в данном случае функции вычисления суммы), а в строке ввода и отображения информации - формула «=А1+5».
Адресная строка предназначена для отображения адреса выделенной ячейки либо диапазона ячеек, а также для ввода с клавиатуры требуемых адресов. Однако при выделении группы ячеек в адресной строке будет показан только адрес первой ячейки диапазона, расположенной в его левом верхнем углу.
В табличном редакторе Excel 2007 можно полностью автоматизировать выполнение расчетов, используя для этого тип данных «Формула». Формула - это специальный инструмент Excel 2007, предназначенный для расчетов, вычислений и анализа данных.
Формула начинается со знака «=», после чего следуют операнды и операторы. Список арифметических операторов приведен в таблице 3.1. Старшинство операций при вычислении формул Excel следующее:
− операторы связи (выполняется в первую очередь);
− оператор процент;
− унарный минус;
− оператор возведение в степень;
− операторы умножение и деление;
− операторы сложение и вычитание (в последнюю очередь). Таблица 3.1 – Символы для обозначения операторов в Excel
взять процент | ||
возведение в степень | ||
Операторы связи | ||
задание диапазона | СУММ(А1:В10) |
|
объединение | СУММ(А1;А3) |
Скобки в формулах Excel выполняют привычную, с точки зрения алгебры, роль указания приоритета вычисления той или иной части выражения. Например:
= 10*4+4^2 дает результат 56
= 10*(4+4^2) дает результат 200
Особенно внимательно надо расставлять скобки при задании унарного минуса. Например: = -10^2 дает результат 100, а =-(10^2) даст результат -100; - 1^2+1^2 дает результат 2, а 1^2-1^2 даст результат 0.
Если формула не может быть правильно вычислена, вместо ожидаемого результата Excel выводит в ячейку код ошибки (таблица 3.2).
Таблица 3.2 – Сообщения об ошибках при вычислении формул
Код ошибки | Возможные причины |
В формуле делается попытка деления на нуль (пустые ячейки |
|
считаются нулями) |
|
Нет доступного значения |
|
Не распознается имя, использованное в формуле |
|
но пересечение двух областей, которые не имеют общих ячеек) |
|
В функции с числовым аргументом используется неприемле- |
|
мый аргумент |
|
Формула неправильно ссылается на ячейку |
|
Используется недопустимый тип аргумента |
Многие виды расчетов можно также выполнять с помощью специальных встроенных в Excel 2007 функций. Функция - это изначально созданная и заложенная в программу Excel процедура, которая выполняет в определенном порядке вычисления по заданным аргументам.
В состав каждой функции в обязательном порядке входят следующие элементы: имя или название (примеры имен - СУММ, СРЗНАЧ, СЧЕТ, МАКС и т. д.), а также аргумент (либо несколько аргументов), который задается в круглых скобках сразу после имени функции. Аргументами функций могут быть числа, ссылки, формулы, текст, логические величины и др.. Если аргумен-
тов у функции несколько, то они задаются через запятую. Если аргументов у функции нет, например, у функции ПИ (), то внутри скобок ничего не задается. Скобки позволяют определить, где начинается и где заканчивается список аргументов. Между названием функции и скобками ничего вставлять нельзя. Поэтому символ возведения функции в степень задается после записи аргумента. Например, SIN(A1)^3. Если правила записи функции нарушены, то Excel выдает сообщение о том, что в формуле имеется ошибка.
Вводить функции можно как в ручном, так и в автоматическом режиме. В последнем случае используют мастер функций, открываемый кнопкой Вставить функцию , которая расположена на ленте Excel 2007 на вкладкеФормулы .
Все имеющиеся в программе функции для удобства работы сгруппированы в категории. Выбор категории осуществляется из раскрывающегося списка Категория , при этом в нижней части окна отображается перечень функций, входящих в эту категорию. Если выделить требуемую функцию и нажать кнопку ОК, то откроется о кно (его содержимое зависит от конкретной функции), в котором указываются аргументы функции.
В инженерных расчетах часто используются тригонометрические функции. Следует иметь в виду, что аргумент тригонометрической функции должен быть задан в радианах. Поэтому, если аргумент задан в градусах, его необходимо перевести в радианы. Это можно реализовать либо через формулу пересчета «=А1*ПИ()/180» (предполагается, что аргумент записан в ячейку с адресом А1), либо с помощью функции РАДИАНЫ(А1).
Пример . Записать формулу Excel= − для2 + вычисления3 1+e функцииtg 3 (5 2 ) b .
Предполагая, что значение x задано в градусах и записано в ячейку А1, а значение b в ячейку B1, формула в ячейке Excel будет выглядеть следующим образом:
=(- (B1^2) + (1+exp(B1))^(1/3)) /TAN(5*РАДИАНЫ(А1)^2) ^3
Для записи в формулы Excel констант следует использовать абсолютную адресацию ячеек. В этом случае при копировании формулы в другую ячейку адрес ячейки с константой не изменится. Чтобы изменить в формуле относительный адрес ячейки В2 на абсолютный $B$2, следует последовательно нажить клавишу F4, либо вручную добавить символы доллара. Существуют также смешанные адреса ячеек (B$2 и $B2). При копировании формулы содержащей
смешанные адреса меняется только не зафиксированная (знаком $ слева) часть адреса.
При копировании формулы в соседнюю ячейку по строке в относительном адресе ссылки меняется буквенная составляющая. Например, ссылка А3 заменится ссылкой В3, а смешанный адрес $А1 при копировании вдоль строки не изменится. Соответственно, при копировании формулы в соседнюю ячейку по столбцу в относительном адресе ссылки меняется цифровая составляющая. Например, ссылка А1 заменится ссылкой А2, а смешанный адрес А$1 при копировании вдоль столбца не изменится.
В Excel под термином диаграмма понимается любое графическое представление числовых данных. Построение диаграмм производится на основе ряда данных – группы ячеек с данными в пределах одной строки или столбца. На одной диаграмме можно отображать несколько рядов данных.
Наиболее простой способ построения диаграмм следующий: выделить один или несколько рядов данных, в группе Диаграммы вкладкиВставка ленты Excel выбрать нужный тип диаграммы. Диаграмма будет помещена на текущий лист рабочей книги. При необходимости ее можно перенести на другой лист с помощью командыПереместить диаграмму вкладкиКонструктор Работы с диаграммами. С помощью вкладкиМакет Работы с диаграммами
можно изменить внешний вид диаграммы: добавить название диаграммы, осей, изменить шрифты и т.п.. При необходимости можно изменить подписи на горизонтальной оси. Для этого в контекстном меню диаграммы следует выбрать ко-
манду Выбрать данныеи в диалоговом окне Выбор источника данных(рису-
нок 3.5) изменить подписи горизонтальной оси.
Рисунок 3.5 – Окно для изменения данных на оси Х
Всем привет. Мы с вами уже знаем, как выглядит , из каких она состоит. Самое время попробовать программу в деле, вы узнаете, как работать в Excel. Мы создадим новый документ, внесем в него данные, выполним простые расчеты и построим временную диаграмму. После освоения этого урока вы уже сможете работать в Экселе, выполнять лёгкие задачи. Это будет первый уверенный шаг к профессиональному использованию Эксель.
Исходные данные: ежемесячные объёмы продаж товара в 2015 году, стоимость единицы товара, выручка за 2014 год помесячно. Приступаем к расчетам.
Если Excel у вас еще не запущен – нажмите Пуск — Все программы — Microsoft Office , и в открывшейся папке – Excel . Программа будет запущена. В открывшемся окне выберите Пустая книга .
Создание нового документа Excel
Если программа уже запущена – нажмите комбинацию клавиш Ctrl+N для создания новой рабочей книги.
В результате, перед вами новый пустой документ с активным листом «Лист 1» . Давайте заполним его исходными данными. Начнём с шапки, т.е. определимся со структурой таблицы. Пусть в первом столбце будут месяцы года.
Шапка готова.
Теперь напишем названия месяцев в столбце А. Установите курсор в ячейку А2, напишите «Январь» и нажмите Enter . Табличный курсор перенесётся на одну строчку вниз. Чтобы заполнить последующие месяцы – воспользуемся функцией Эксель – автозаполнение. Верните курсор в ячейку А2 и наведите мышью на квадратик в нижнем правом углу активной клетки. При этом, курсор превратится в чёрный крестик. Зажмите левую кнопку мыши и тяните курсор вниз, как бы растягивая надпись «Январь» вниз. Эксель автоматически будет записывать новые месяца в каждой следующей строчке.
Пока вы не отпустили мышь – программа показывает подсказку о том, какой месяц будет в соответствующей ячейке. Посмотрите, на рисунке я дотянул список месяцев до мая. Вы же протяните курсор на 11 строчек вниз, на подсказке отобразится «декабрь». Отпускайте мышь и «вуа-ля», одним взмахом руки мы прописали целых 12 месяцев!
Теперь заполним количества и цены. Поставим курсор в клетку С2, запишем количество и нажмём Ввод на клавиатуре. Выделение переместится в С3, там тоже запишем количество. Так, заполняем все продажи до декабря (С13). Аналогично поступим с ценами в столбце D. В нашем примере цены содержат копейки. В русифицированной версии Excel они отделаются от целой части запятой. В оригинальной версии – точкой. Еще заполним выручку 2014 года. Вот так будет выглядеть заполненная таблица данных:
Важно! Все формулы в Excel начинаются со знака «=» . Если первый символ в ячейке – этот знак, программа понимает, что дальше будет следовать вычисляемая формула.
Активизируем ячейку B2 и вычислим выручку в январе 2015 года. Напишем «=» и кликнем левой кнопкой мыши на ячейку С2, задав ссылку на продажи января. Дальше ставим знак умножения «*» и кликаем на D2 – ссылка на стоимость товара. Жмём Enter и видим, что в выделенной ячейке отобразился результат расчётов, а в строке формул для этой ячейки – введенная нами формула. Excel извлечёт значение из С2 и умножит его на значение в D2. Если изменить любое из них – формула сразу же будет пересчитана. Больше информации о правилах расчета я написал в статьях и . Там всё это расписано более детально.
Давайте протянем эту формулу на все строки таблицы, как мы делали с заполнением месяцев. Тянем за квадратик в правом нижнем углу ячейки с формулой. Результат – программа просчитала выручку для всех месяцев. Обратите внимание, при протягивании изменяются адреса ссылок в формуле. Например, в ячейке В12 у нас формула: =C12*D12 , а не =C2*D2 . Это потому, что у нас по умолчанию применились .
Теперь вычислим разницу между выручкой 2014 и 2015 годов. Назовём столбец F – «Разница». Методика написания формул не меняется, только вычислим =В2-Е2 , протянем формулу для всех месяцев. Видим, например, что в январе 2015 года наша выручка на 7,5 ед. меньше, чем в 2014.
Осталась последняя задача – построить временную диаграмму и визуально оценить динамику продаж (более детально вопрос диаграмм я рассмотрел в ).
Чтобы построить график, нужно выделить диапазон (почитайте ) данных, на основании которых будет выстроена кривая. Наведите курсор мышки на ячейку А2. Зажмите левую кнопку и тяните курсор к ячейке В13. После этого отпустите кнопку мыши. Если вы всё сделали правильно, диапазон А2:В13 будет выделен тёмным цветом.
Строим график на основании выделенных данных. Для этого, не ленте перейдите на вкладку Вставка , а там – Диаграммы — Вставить график — График с маркерами . На экране появится диаграмма изменения выручки по месяцам 2015 года. Первый выделенный столбец программа использовала для подписей по горизонтальной оси, а второй – для значений по вертикальной.
Чтобы сохранить наш документ – нажмите комбинацию клавиш Ctrl+S , в открывшемся окне нажмите Обзор . Открывается стандартное окно сохранения, где нужно указать расположение файла и задать его имя. Нажатием кнопки Сохранить, подтверждаем создание нового файла.
Вот мы и выполнили минимальную задачу, а заодно познакомились с основными возможностями Эксель. Дальше мы будем расширять и совершенствовать полученные знания. В ближайших постах мы рассмотрим , некоторые специальные возможности для ввода. Если у вас остались вопросы – задавайте в комментариях, обязательно отвечу.
Всегда ваш, Александр Томм.