Работа в икс эль для начинающих. Видео уроки Microsoft Excel для Начинающих от Андрея Сухового

В третьей части материалов «Excel 2010 для начинающих» мы поговорим об алгоритмах копирования формул и системе отслеживания ошибок при их составлении. Помимо этого, вы узнаете, что такое функции, а так же как представлять данные или результаты вычислений в графическом виде с помощью диаграмм и спарклайнов.

Вступление

Во второй части цикла «Excel 2010 для начинающих» мы изучили некоторые возможности форматирования таблицы, например, добавление строк и столбцов, а так же объединение ячеек. Помимо этого вы узнали, как выполнять в Excel арифметические операции и научились составлять простые формулы.

В начале этого материала мы еще немного поговорим о формулах - расскажем, как их редактировать, поговорим о системе оповещения об ошибках и инструментах отслеживания ошибок, а так же узнаем, с помощью каких алгоритмов в Excel осуществляется копирование и перемещение формул. Затем мы познакомимся с еще одним важнейшим понятием в электронных таблицах - функциями. Напоследок, вы узнаете, как в MS Excel 2010 можно представлять данные и полученные результаты в наглядном (графическом) виде, используя диаграммы и спарклайны.

Редактирование формул и система отслеживания ошибок

Все формулы, которые находятся в ячейках таблицы можно отредактировать в любой момент. Для этого достаточно выделить ячейку с формулой и затем щелкнуть по строке формул над таблицей, где вы сможете сразу же внести необходимые изменения. Если вам удобнее редактировать содержимое непосредственно в самой ячейке, то кликните по ней два раза.

После окончания редактирования нажмите клавиши Enter или Tab, после чего Excel выполнить перерасчет с учетом изменений и отобразит результат.

Довольно часто случается так, что вы ввели формулу неверно или после удаления (изменения) содержимого одной из ячеек, на которую ссылается формула, происходит ошибка в вычислениях. В таком случае Excel непременно известит вас об этом. Рядом с клеткой, где содержится ошибочное выражение, появится восклицательный знак, в желтом ромбе.

Во многих случаях, приложение не только известит вас о наличие ошибки, но и укажет на то, что именно сделано не так.

Расшифровка ошибок в Excel:

• ##### - результатом выполнения формулы, использующей значения даты и времени стало отрицательное число или результат обработки не умещается в ячейке;
• #ЗНАЧ! - используется недопустимый тип оператора или аргумента формулы. Одна из самых распространенных ошибок;
• #ДЕЛ/0! - в формуле осуществляется попытка деления на ноль;
• #ИМЯ? - используемое в формуле имя некорректно и Excel не может его распознать;
• #Н/Д - неопределенные данные. Чаще всего эта ошибка возникает при неправильном определении аргумента функции;
• #ССЫЛКА! - формула содержит недопустимую ссылку на ячейку, например, на ячейку, которая была удалена.
• #ЧИСЛО! - результатом вычисления является число, которое слишком мало или слишком велико, что бы его можно было использовать в MS Excel. Диапазон отображаемых чисел лежит в промежутке от -10 307 до 10 307 .
• #ПУСТО! - в формуле задано пересечение областей, которые на самом деле не имеют общих ячеек.

Еще раз напомним, что ошибки могут появляться не только из-за неправильных данных в формуле, но и вследствие содержания некорректной информации в ячейке, на которую она ссылается.

Иногда, когда данных в таблице много, а формулы содержат большое количество ссылок на различные ячейки, то при проверке выражения на правильность или поиска источника ошибки могут возникнуть серьезные трудности. Что бы облегчить работу пользователя в таких ситуациях, в Excel встроен инструмент, позволяющий выделить на экране влияющие и зависимые ячейки.

Влияющие ячейки - это ячейки, на которые ссылаются формулы, а зависимые ячейки , наоборот, содержат формулы, ссылающиеся на адреса клеток электронной таблицы.

Что бы графически отобразить связи между ячейками и формулами с помощью, так называемых стрелок зависимостей , можно воспользоваться командами на ленте Влияющие ячейки и Зависимые ячейки в группе Зависимости формул во вкладке Формулы .

Например, давайте посмотрим как в нашей тестовой таблице, составленной в предыдущих двух частях, формируется итоговый результат накоплений:

Не смотря на то, что формула в данной ячейке имеет вид «=H5 - H12», программа Excel, cпомощью стрелок зависимостей, может показать все значения, которые учувствуют в вычислении конечного результата. Ведь в клетках H5 и H12 так же содержаться формулы, имеющие ссылки на другие адреса, которые в свою очередь, могут содержать как формулы, так и числовые константы.

Чтобы удалить все стрелки с рабочего листа, в группе Зависимости формул на вкладке Формулы , нажмите кнопку Убрать стрелки .

Относительные и абсолютные адреса ячеек

Возможность копирования формул в Excel из одной ячейки в другую с автоматическим изменением адресов, содержащихся в них, существует благодаря концепции относительной адресации . Так что же это такое?

Дело в том, что Excel понимает адреса ячеек введенных в формулу не как ссылку на их реальное месторасположение, а как ссылку на их месторасположение относительно ячейки, в которой находится формула. Поясним на примере.

Например, ячейка A3, содержит формулу: «=A1+A2». Для Excelэто выражение не означает, что нужно взять значение из ячейки A1 и прибавить к нему число из ячейки A2. Вместо этого он интерпретирует данную формулу, как «взять число из ячейки расположенной в том же столбце, но на две строки выше и сложить его со значением ячейки этого же столбца расположенной выше на одну строку». При копировании данной формулы в другую ячейку, например D3, принцип определения адресов ячеек входящих в выражение остается тем же: «взять число из ячейки расположенной в том же столбце, но на две строки выше и сложить его с…». Таким образом, после копирования в D3, исходная формула автоматически примет вид «=D1+D2».

С одной стороны, такой тип ссылок дает пользователям прекрасную возможность просто копировать одинаковые формулы из ячейки в ячейку, избавляя от необходимости вводить их снова и снова. А с другой стороны, в некоторых формулах необходимо постоянно использовать значение одной определенной ячейки, а это значит, что ссылка на нее не должна изменяться и зависеть от расположения формулы на листе.

Например, представим, что в нашей таблице значения бюджетных расходов в рублях будут рассчитываться исходя из долларовых цен, умноженных на текущий курс, который записан всегда в ячейке A1. Это значит, что при копировании формулы ссылка на эту ячейку не должна изменяться. Тогда в этом случае следует применять не относительную, а абсолютную ссылку , которая всегда будет оставаться неизменной при копировании выражения из одной ячейки в другую.

С помощью абсолютных ссылок можно дать команду Excel при копировании формулы:

• сохранять ссылку на столбец постоянно, но при этом изменять ссылки на столбцы
• изменять ссылки на строки, но сохранять ссылку на столбец
• сохранять постоянными ссылки, как на столбец, так и на строку.

• $A$1 - ссылка всегда ссылается на ячейку A1 (абсолютная ссылка);
• A$1 - ссылка всегда ссылается на строку 1, а путь к столбцу может изменяться (смешанная ссылка);
• $A1 - ссылка всегда ссылается на столбец A, а путь к строке может изменяться (смешанная ссылка).

Для ввода абсолютных и смешанных ссылок используется клавиша «F4». Выделите ячейку для формулы, введите знак равенства (=) и кликните по клетке, на которую надо установить абсолютную ссылку. Затем нажмите клавишу F4, после чего перед буквой столбца и номером строки программа установит знаки доллара ($). Повторные нажатия на F4 позволяют переходить от одного типа ссылок к другим. Например, ссылка на E3, будет циклично изменяться на $E$3, E$3, $E3, E3 и так далее. При желании знаки $ можно вводить вручную.

Функции

Функциями в Excel называют заранее определенные формулы, с помощью которых выполняются вычисления в указанном порядке по заданным величинам. При этом вычисления могут быть как простыми, так и сложными.

Например, определение среднего значения пяти ячеек можно описать формулой: =(A1 + A2 + A3 + A4 + A5)/5, а можно специальной функцией СРЗНАЧ, которая сократит выражение до следующего вида: СРЗНАЧ(А1:А5). Как видите, что вместо ввода в формулу всех адресов ячеек можно использовать определенную функцию, указав ей в качестве аргумента их диапазон.

Для работы с функциями в Excel на ленте существует отдельная закладка Формулы , на которой располагаются все основные инструменты для работы с ними.

Надо отметить, что программа содержит более двухсот функций, способных облегчить выполнение вычислений различной сложности. Поэтому все функции в Excel 2010 разделены на несколько категорий, группирующих их по типу решаемых задач. Какие именно эти задачи, становится ясно из названий категорий: Финансовые, Логические, Текстовые, Математические, Статистические, Аналитические и так далее.

Выбрать необходимую категорию можно на ленте в группе Библиотека функций во вкладке Формулы . После щелчка по стрелочке, располагающейся рядом с каждой из категорий, раскрывается список функций, а при наведении курсора на любую из них, появляется окно с ее описанием.

Ввод функций, как и формул, начинается со знака равенства. После идет имя функции , в виде аббревиатуры из больших букв, указывающей на ее значение. Затем в скобках указываются аргументы функции - данные, использующиеся для получения результата.

В качестве аргумента может выступать конкретное число, самостоятельная ссылка на ячейку, целая серия ссылок на значения или ячейки, а так же диапазон ячеек. При этом у одних функций аргументы - это текст или числа, у других - время и даты.

Многие функции могут иметь сразу несколько аргументов. В таком случае, каждый из них отделяется от следующего точкой с запятой. Например, функция =ПРОИЗВЕД(7; A1; 6; B2) считает произведение четырёх разных чисел, указанных в скобках, и соответственно содержит четыре аргумента. При этом в нашем случае одни аргументы указаны явно, а другие, являются значениями определенных ячеек.

Так же в качестве аргумента можно использовать другую функцию, которая в этом случае называется вложенной . Например, функция =СУММ(A1:А5; СРЗНАЧ(В5:В10)) суммирует значения ячеек находящихся в диапазоне от А1 до А5, а так же среднее значение чисел, размещенных в клетках В5, В6, В7, В8, В9 и В10.

У некоторых простых функций аргументов может не быть вовсе. Так, с помощью функции =ТДАТА() можно получить текущие время и дату, не используя никаких аргументов.

Далеко не все функции в Ecxel имеют простое определение, как функция СУММ, осуществляющая суммирование выбранных значений. Некоторые из них имеют сложное синтаксическое написание, а так же требуют много аргументов, которые к тому же должны быть правильных типов. Чем сложнее функция, тем сложнее ее правильное составление. И разработчики это учли, включив в свои электронные таблицы помощника по составлению функций для пользователей - Мастер функций .

Для того что бы начать вводить функцию с помощью Мастера функций , щелкните на значок Вставить функцию (fx) , расположенный слева от Строки формул .

Так же кнопку Вставить функцию вы найдете на ленте сверху в группе Библиотека функций во вкладке Формулы . Еще одним способом вызова мастера функций является сочетание клавиш Shift+ F3 .

После открытия окна помощника, первое, что вам придется сделать - это выбрать категорию функции. Для этого можно воспользоваться полем поиска или ниспадающим списком.

В середине окна отражается перечень функций выбранной категории, а ниже - краткое описание выделенной курсором функции и справка по ее аргументам. Кстати назначение функции часто можно определить по ее названию.

Сделав необходимый выбор, щелкните по кнопке ОК, после чего появится окно Аргументы функции.

В левом верхнем углу окна указывается имя выбранной функции, под которым находятся поля, служащие для ввода необходимых аргументов. Справа от них, после знака равенства указываются текущие значения каждого аргумента. В нижней части окна размещается справочная информация, указывающая назначение функции и каждого аргумента, а так же текущий результат вычисления.

Ссылки на ячейки (или их диапазон) в поля для ввода аргументов можно вводить как вручную, так и используя мышь, что гораздо удобнее. Для этого просто щелкайте левой кнопкой по нужным клеткам на открытом листе или обведите их необходимый диапазон. Все значения будут автоматически подставлены в текущее поле ввода.

Если диалоговое окно Аргументы функции мешает вводу необходимых данных, перекрывая собой рабочий лист, его можно на время уменьшить, нажав на кнопку в правой части поля ввода аргументов.

Повторное нажатие на нее же приведет к восстановлению обычного размера.

После ввода всех необходимых значений, остается кликнуть по кнопке ОК и в выбранной ячейке появится результат вычисления.

Диаграммы

Довольно часто числа в таблице, даже отсортированные должным образом, не позволяют составить полную картину по итогам вычислений. Что бы получить наглядное представление результатов, в MS Excel существует возможность построения диаграмм различных типов. Это может быть как обычная гистограмма или график, так и лепестковая, круговая или экзотическая пузырьковая диаграмма. Более того, в программе существует возможность создавать комбинированные диаграммы из различных типов, сохраняя их в качестве шаблона для дальнейшего использования.

Диаграмму в Excel можно разместить либо на том же листе, где уже находится таблица, и в таком случае она называется «внедренной», либо на отдельном листе, который станет называться «лист диаграммы».

В качестве примера, попробуем представить в наглядном виде данные ежемесячных расходов, указанных в таблице, созданной нами в предыдущих двух частях материалов «Excel 2010 для начинающих».

Для создания диаграммы на основе табличных данных сначала выделите те ячейки, информация из которых должна быть представлена в графическом виде. При этом внешний вид диаграммы зависит от типа выбранных данных, которые должны находиться в столбцах или строках. Заголовки столбцов должны находиться над значениями, а заголовки строк - слева от них. В нашем случае, выделяем клетки содержащие названия месяцев, статей расходов их значения.

Затем, на ленте во вкладке Вставка в группе Диаграммы выберите нужный тип и вид диаграммы. Что бы увидеть краткое описание того или иного типа и вида диаграмм, необходимо задержать на нем указатель мыши.

В правом нижнем углу блока Диаграммы располагается небольшая кнопка Создать диаграмму , с помощью которой можно открыть окно Вставка диаграммы , отображающее все виды, типы и шаблоны диаграмм.

В нашем примере давайте выберем объемную цилиндрическую гистограмму с накоплением и нажмем кнопку ОК, после чего на рабочем листе появится диаграмма.

Так же обратите внимание, на появление дополнительной закладки на ленте Работа с диаграммами , содержащая еще три вкладки: Конструктор , Макет и Формат .

На вкладке Конструктор можно изменить тип диаграммы, поменять местами строки и столбцы, добавить или удалить данные, выбрать ее макет и стиль, а так же переместить диаграмму на другой лист или другую вкладку книги.

На вкладке Макет располагаются команды, позволяющие добавлять или удалять различные элементы диаграммы, которые можно легко форматировать с помощью закладки Формат .

Вкладка Работа с диаграммами появляется автоматически всякий раз, когда вы выделяете диаграмму и исчезает, когда происходит работа с другими элементами документа.

Форматирование и изменение диаграмм

При первичном создании диаграммы заранее очень трудно определить, какой ее тип представит наиболее наглядно выбранные табличные данные. Тем более, вполне вероятно, что расположение новой диаграммы на листе окажется совсем не там, где вам хотелось бы, да и ее размеры вас могут не устраивать. Но это не беда - первоначальный тип и вид диаграммы можно легко изменить, так же ее можно переместить в любую точку рабочей области листа или скорректировать горизонтальные и вертикальные размеры.

Что быстро изменить тип диаграммы на вкладке Конструктор в группе Тип , размещающейся слева, щелкните кнопку Изменить тип диаграммы . В открывшемся окне слева выберите сначала подходящий тип диаграммы, затем ее подтип и нажмите кнопку ОК. Диаграмма будет автоматически перестроена. Старайтесь подбирать такой тип диаграммы, который наиболее точно и наглядно будет демонстрировать цель ваших вычислений.

Если данные на диаграмме отображаются не должным образом, попробуйте поменять местами отображения строк и столбцов, нажав на кнопку, Строка/столбец в группе Данные на вкладке Конструктор .

Подобрав нужный тип диаграммы, можно поработать на ее видом, применив к ней встроенные в программу макеты и стили. Excel, за счет встроенных решений, предоставляет пользователям широкие возможности выбора взаимного расположения элементов диаграмм, их отображения, а так же цветового оформления. Выбор нужного макета и стиля осуществляется на вкладке Конструктор в группах с говорящими названиями Макеты диаграмм и Стили диаграмм . При этом в каждой из них есть кнопка Дополнительные параметры , раскрывающая полный список предлагаемых решений.

И все же не всегда созданная или отформатированная диаграмма с помощью встроенных макетов и стилей удовлетворяет пользователей целиком и полностью. Слишком большой размер шрифтов, очень много места занимает легенда, не в том месте находятся подписи данных или сама диаграмма слишком маленькая. Словом, нет предела совершенству, и в Excel, все, что вам не нравится, можно исправить самостоятельно на свой «вкус» и «цвет». Дело в том, что диаграмма состоит из нескольких основных блоков, которые вы можете форматировать.

Область диаграммы - основное окно, где размещаются все остальные компоненты диаграммы. Наведя курсор мыши на эту область (появляется черное перекрестье), и зажав левую кнопку мыши, можно перетащить диаграмму в любую часть листа. Если же вы хотите изменить размер диаграммы, то наведите курсор мыши на любой из углов или середину стороны ее рамки, и когда указатель примет форму двухсторонней стрелочки, потяните его в нужном направлении.

Область построения диаграммы - включает в себя вертикальную и горизонтальную оси, ряд данных, а так же основные и дополнительные линии сетки (стены).

Ряд данных - данные, представленные в графическом виде (диаграмма, гистограмма, график и т.д.). Могут иметь подписи данных, отображающие точные цифровые показатели строк или рядов диаграммы.

Ось значений и ось категорий - числовые параметры, расположенные вдоль вертикальной и горизонтальной линий, ориентируясь на которые можно оценить данные диаграммы. Могут иметь собственные подписи делений и заголовки.

Линии сетки - наглядно представляют значения осей и размещаются на боковых панелях, называемых стенами .

Легенда - расшифровка значений рядов или строк.

Любому пользователю Excel предоставляется возможность самостоятельно изменять стили и художественное представление каждого из вышеперечисленных компонентов диаграммы. К вашим услугам выбор цвета заливки, стиля границ, толщины линий, наложение объема, теней, свечения и сглаживания на выбранные объекты. В любой момент, можно изменить общий размер диаграммы, увеличить/уменьшить любую ее область, например, увеличить саму диаграмму и уменьшить легенду, или вообще отменить отображение ненужных элементов. Можно изменить угол наклона диаграммы, повернуть ее, сделать объемной или плоской. Одним словом, MS Excel 2010 содержит инструменты, позволяющие придать диаграмме собственноручно наиболее удобный для восприятия образ.

Для изменения компонентов диаграммы воспользуйтесь вкладкой Макет , расположенной на ленте в области Работа с диаграммами .

Здесь вы найдете команды с названиями всех частей диаграммы, а щелкнув по соответствующим кнопкам, можно перейти к их форматированию. Есть и другие, более простые способы изменения компонентов диаграмм. Например, достаточно просто навести курсор мыши на нужный объект, и щелкнуть по нему два раза, после чего сразу откроется окно форматирования выбранного элемента. Так же вы можете воспользоваться командами контекстного меню, которое вызывается кликом правой кнопки мыши по нужному компоненту.

Самое время преобразовать внешний вид нашей тестовой диаграммы, воспользовавшись разными способами. Сначала несколько увеличим размер диаграммы. Для этого установите курсор мыши в любой угол области диаграммы и после изменения его вида на двухстороннюю стрелочку потащите указатель в нужном направлении (направлениях).

Теперь отредактируем внешний вид рядов данных. Щелкните два раза левой кнопкой мыши по любой цветной цилиндрической области диаграммы (каждый ряд отмечен своим уникальным цветом), после чего откроется одноименное окно с настройками.

Здесь, слева в столбце, отображается список параметров, которые можно собственноручно изменять для данного компонента диаграммы, а справа - область редактирования с текущими значениями.

Здесь вы можете выбрать различные параметры отображения ряда, включая тип фигуры, зазоры между ними, заливку области, цвет границ и так далее. Попробуйте самостоятельно в каждом из разделов менять параметры и увидите, как это будет влиять на внешний вид диаграммы.

В итоге, в окне Формат ряда данных мы убрали фронтальный зазор, а боковой сделали равным 20%, добавили тень снаружи и немного объема сверху.

Теперь щелкнем правой кнопкой мыши на любой цветной цилиндрической области и в открывшемся контекстном меню выберем пункт Добавить подписи данных . После этого на диаграмме появятся ежемесячные значения по выбранной статье расходов. Тоже самое проделайте со всеми оставшимися рядами. Кстати, сами подписи данных впоследствии тоже можно форматировать: изменять размер шрифта, цвет, его начертание, менять месторасположение значений и так далее. Для этого так же используйте контекстное меню, кликнув правой кнопкой мыши непосредственно по самому значению, и выберите команду Формат подписи данных .

Для форматирования осей давайте воспользуемся вкладкой Макет на ленте сверху. В группе Оси щелкните по одноименной кнопке, выберите нужную ось, а затем пункт Дополнительные параметры основной горизонтальной/вертикальной оси .

Принцип расположения управляющих элементов в открывшемся окне Формат оси мало чем отличается от предыдущих - тот же столбец с параметрами слева и зоной изменяемых значений справа. Здесь мы не стали особо ничего менять, лишь добавив светло-серые тени к подписям значений, как вертикальной, так и горизонтальной осей.

Наконец, давайте добавим заголовок диаграммы, щелкнув на вкладке Макет в группе Подписи по кнопке Название диаграммы . Далее уменьшим область легенды, увеличим область построения и посмотрим, что у нас получилось:

Как видите, встроенные в Excel инструменты форматирования диаграмм действительно дают широкие возможности пользователям и визуальное представление табличных данных на этом рисунке разительно отличается от первоначального варианта.

Спарклайны или инфокривые

Завершая тему диаграмм в электронных таблицах, давайте рассмотрим новый инструмент наглядного представления данных, который стал доступен в Ecxel 2010. Спарклайны или инфокривые - это небольшие диаграммы, размещающиеся в одной ячейке, которые позволяют визуально отобразить изменение значений непосредственно рядом с данными.

Таким образом, занимая совсем немного места, спарклайны призваны продемонстрировать тенденцию изменения данных в компактном графическом виде. Инфокривые рекомендуется размещать в соседней ячейке с используемыми ее данными.

Для примерных построений спарклайнов давайте воспользуемся нашей уже готовой таблицей ежемесячных доходов и расходов. Нашей задачей будет построить инфокривые, показывающие ежемесячные тенденции изменения доходных и расходных статей бюджета.

Как уже говорилось выше, наши маленькие диаграммы должны находиться рядом с ячейками данных, которые учувствуют в их формировании. Это значит, что нам необходимо вставить в таблицу дополнительный столбец для их расположения сразу после данных по последнему месяцу.

Теперь выберем нужную пустую ячейку во вновь созданном столбце, например H5. Далее на ленте во вкладке Вставка в группе Спарклайны выберете походящий тип кривой: График , Гистограмма или Выигрыш/Проигрыш .

После этого откроется окно Создание спарклайнов , в котором вам будет необходимо ввести диапазон ячеек с данными, на основе которых будет создан спарклайн. Сделать это можно либо напечатав диапазон ячеек вручную, либо выделив его с помощью мыши прямо в таблице. В случае необходимости можно так же указать место для размещения спарклайнов. После нажатия кнопки ОК в выделенной ячейки появится инфокривая.

В этом примере, вы можете визуально наблюдать динамику изменения общих доходов за полугодие, которую мы отобразили в виде графика. Кстати, что бы построить спарклайны в оставшихся ячейках строк «Зарплата» и «Бонусы», нет никакой необходимости проделывать все вышеописанные действия заново. Достаточно вспомнить и воспользоваться уже известной нам функцией автозаполнения.

Подведите курсор мыши к правому нижнему углу ячейки с уже построенным спарклайном и после появления черного перекрестья, перетащите его к верхнему краю клетки H3. Отпустив левую кнопку мыши, наслаждайтесь полученным результатом.

Теперь попробуйте самостоятельно достроить спарклайны по расходным статьям, только в виде гистограмм, а для общего баланса, как нельзя кстати подойдет тип выигрыш/проигрыш.

Теперь, после добавления спарклайнов, наша сводная таблица приняла вот такой интересный вид:

Таким образом, бросив беглый взгляд на таблицу и не вчитываясь в числа, можно увидеть динамику получения доходов, пиковые расходы по месяцам, месяцы, где баланс был отрицательным, а где положительным и так далее. Согласитесь, во многих случаях это может быть полезно.

Так же как и диаграммы, спарклайны можно редактировать и настраивать их внешний вид. При щелчке мышью по ячейке с инфокривой, на ленте появляется новая вкладка Работа со спарклайнами .

С помощью команд расположенных здесь можно изменить данные спарклайна и его тип, управлять показом отображения точек данных, изменять стиль и цвет, управлять масштабом и видимостью осей, группировать и задавать собственные параметры форматирования.

Напоследок, стоит отметить и еще один интересный момент - в ячейку, содержащую спарклайн, вы можете вводить обычный текст. В этом случае инфокривая будет использоваться в качестве фона.

Заключение

Итак, теперь вы знаете, что с помощью Excel 2010 вы можете не только строить таблицы любой сложности и производить различные вычисления, но и представлять результаты в графическом виде. Все это делает электронные таблицы от Microsoft мощнейшим инструментом, способным удовлетворить потребности, как профессионалов, использующих его для составления деловой документации, так и обычных пользователей.

Большинство пользователей компьютерных систем на основе Windows с установленным пакетом Microsoft Office непременно сталкивались с приложением MS Excel. У начинающих юзеров программа вызывает некоторые сложности в освоении, тем не менее работа в «Экселе» с формулами и таблицами не так уж и сложна, как может показаться на первый взгляд, если знать основные принципы, заложенные в приложении.

Что такое Excel?

По сути своей Excel представляет собой полноценную математическую машину для выполнения множества арифметических, алгебраических, тригонометрических и других более сложных операций, оперируя несколькими основными типами данных, не всегда относящимися именно к математике.

Работа с таблицами «Эксель» подразумевает использование более широких возможностей с объединением и вычислений, и обычного текста, и мультимедиа. Но в своем изначальном виде программа создавалась именно как мощнейший математический редактор. Некоторые, правда, поначалу принимают приложение за какой-то калькулятор с расширенными возможностями. Глубочайшее заблуждение!

Работа в «Экселе» с таблицами для начинающих: первое знакомство с интерфейсом

Первым делом после открытия программы пользователь видит главное окно, в котором присутствуют основные элементы управления и инструменты для работы. В более поздних версиях при старте приложения появляется окно, в котором предлагается создать новый файл, по умолчанию называемый «Книга 1» или выбрать шаблон для дальнейших действий.

Работа с таблицами «Эксель» для начинающих на первом этапе знакомства с программой должна сводиться именно к созданию пустой таблицы. Пока рассмотрим основные элементы.

Основное поле занимает сама таблица, которая разбита на ячейки. Каждая имеет нумерацию, благодаря двумерным координатам - номер строки и буквенное обозначение столбца (для примера берем Excel 2016). Такая нумерация нужна для того, чтобы в формуле зависимостей можно было четко определить именно ту ячейку с данными, операция над которой будет производиться.

Сверху, как и в остальных офисных приложениях, расположена панель главного меню, а чуть ниже - инструментарий. Под ним есть специальная строка, в которой вводятся формулы, а чуть левее можно увидеть окошко с координатами активной на данный момент ячейки (на которой расположен прямоугольник). Снизу представлена панель листов и ползунок горизонтального перемещения, а под ним находятся кнопки переключения вида и масштабирования. Справа располагается вертикальная полоса для перемещения по листу вверх/вниз.

Основные типа ввода данных и простейшие операции

На первых порах предполагается, что работа в «Экселе» с таблицами начинающим пользователем будет осваиваться по знакомым ему операциям, например в том же текстовом редакторе Word.

Как обычно, в таблице можно производить операции копирования, вырезания или вставки данных, вводить текст или числовые данные.

Но ввод несколько отличается от того, который производится в текстовых редакторах. Дело в том, что программа изначально настроена на автоматическое распознавание того, что пользователь прописывает в активной ячейке. Например, если ввести строку 1/2/2016, данные будут распознаны как дата, и в ячейке вместо введенных цифр появится дата в упрощенном виде - 01.02.2016. Изменить формат отображения можно достаточно просто (на этом остановимся чуть позже).

Точно так же дело обстоит с числами. Можно вводить любые числовые данные даже с произвольным количеством знаков после запятой, и они будут отображены в том виде, в котором все их привыкли видеть. Но, если вводится целое число, оно будет представлено без мантиссы (знаков после запятой в виде нулей). Поменять это тоже можно.

Но по окончании ввода данных многие начинающие пользователи пытаются перейти на следующую ячейку, используя для этого клавиатурные стрелки (по типу того, как это можно сделать в таблицах Word). И это не срабатывает. Почему? Да только потому, что работа с таблицами «Эксель» отличается от текстового редактора Word достаточно ощутимо. Переход может осуществлен нажатием клавиши Enter или установкой активного прямоугольника на другую ячейку при помощи левого клика мыши. Если после написания чего-то в активной ячейке нажать клавишу Esc, ввод будет отменен.

Действия с листами

Работа с листами на первых порах трудностей вызывать не должна. На панели снизу имеется специальная кнопка добавления листов, после нажатия на которую появится новая таблица с автоматическим переходом на нее и заданием имени («Лист 1», «Лист 2» и т. д.).

Двойным кликом можно активировать переименование любого из них. Также можно использовать меню правого клика для вызова дополнительного меню, в котором имеется несколько базовых команд.

Форматы ячеек

Теперь самое главное - формат ячейки - одно из базовых понятий, которое и определяет тип данных, который будет использован для распознавания ее содержимого. Вызывать редактирование формата можно через меню правого клика, где выбирается соответствующая строка, или посредством нажатия клавиши F2.

В окне слева представлены все доступные форматы, а справа показаны варианты отображения данных. Если посмотреть на пример с датой, показанный выше, в качестве формата выбирается «Дата», а справа устанавливается желаемый вид (например, 1 февраля 2016 г.).

Для проведения математических операций можно использовать несколько форматов, но в самом простом случае выберем числовой. Справа имеется несколько типов ввода, указатель на количество знаков в мантиссе после запятой и поле установки разделителя групп разрядов. Используя другие числовые форматы (экспоненциальный, дробный, денежный и т. д.), тоже можно выставлять желаемые параметры.

По умолчанию для автоматического распознавания данных установлен общий формат. Но при вводе текста или нескольких литер программа может преобразовать его самопроизвольно во что-то другое. Поэтому для ввода текста для активной ячейки нужно установить соответствующий параметр.

Работа в «Эксель» с формулами (таблицами): пример

Наконец, несколько слов о формулах. И для начала рассмотрим пример суммы двух чисел, находящихся в ячейках A1 и A2. В приложении имеется кнопка автоматического суммирования с некоторыми дополнительными функциями (вычисление среднего арифметического, максимума, минимума и т. д.). Достаточно установить активной ячейку, находящуюся в том же столбце ниже, и при выборе суммы она будет посчитана автоматически. То же самое работает для горизонтально расположенных значений, но активную ячейку для суммы нужно выставлять правее.

Но можно ввести формулу и вручную (работа с таблицами «Эксель» предполагает и такую возможность, когда автоматическое действие не предусмотрено). Для той же суммы следует в строке формул поставить знак равенства и прописать операцию в виде A1+A2 или СУММ(A1;A2), а если требуется указать диапазон ячеек, используется такой вид после знака равенства: (A1:A20), после чего будет посчитана сумма всех чисел, находящихся в ячейках от первой до двадцатой включительно.

Построение графиков и диаграмм

Работа с таблицами «Эксель» интересна еще и тем, что предполагает использование специального автоматизированного инструмента построения графиков зависимостей и диаграмм на основе выделенных диапазонов.

Для этого предусмотрена специальная кнопка на панели, после нажатия на которую можно выбрать любые параметры или желаемый вид. После этого диаграмма или график отобразятся на листе в виде картинки.

Перекрестные связи, импорт и экспорт данных

В программе также можно устанавливать связи данных, находящихся на разных листах, использовать на файлы другого формата или объекты, находящиеся на серверах в интернете, и множество других надстроек.

Кроме того, файлы Excel можно экспортировать в другие форматы (например, PDF), копировать из них данные и т. д. Но и сама программа может открывать файлы, созданные в других приложениях (текстовые форматы, базы данных, веб-страницы, XML-документы и т. д.).

Как видим, возможности редактора практически неограничены. И, конечно, описать их все просто не хватит времени. Здесь приведены только начальные азы, но заинтересованному пользователю придется почитать справочную информацию, чтобы освоить программу на самом высоком уровне.

Часто встречаются любители статистики, которые не любят считать и вычислять. Им нравится изучать, анализировать готовые цифры и вести скрупулёзный учёт. Теперь, с появлением электронных процессоров для таких людей предоставляются неограниченные возможности. Нет ничего проще, чем вести статистический и другой учет в программе Excel из офисного пакета Microsoft.

Основные понятия и функции

Приступая к работе в Экселе с таблицами, для начинающих важно усвоить основные понятия и принципы работы данной программы. Как любая виндовская программа, Excel обладает традиционным для подобных программ интерфейсом.

В меню входят разделы, свойственные всем компонентам пакета Майкрософт Офис: главная, вставка, разметка страницы, вид, рецензирование. Есть и вкладки, свойственные только этой программе: формулы и данные.

Внешний вид рабочей области представлен в виде разбитой на ячейки страницы. Каждая ячейка имеет свой номер или координаты. Для этого самый левый столбик пронумерован и первая верхняя строчка имеет свою нумерацию в виде латинских букв. Координаты ячейки определяются по пересечению вертикального столбца с буквой и горизонтальной строки с цифрой.

Каждая ячейка является хранилищем данных. Это может быть что угодно: цифры, текст, формула для вычислений. К любой ячейке можно применять разные свойства и виды форматирования данных. Для этого используют щелчок на ячейке правой кнопкой мыши. В открывшемся меню выбирают раздел «Формат ячейки».

Все ячейки объединены в листы. В нижней части окна программы имеются ярлычки с названиями листов. По умолчанию это Лист1, Лист2 и Лист3, а также ярлычок для создания нового листа. Все листы можно переименовывать по своему усмотрению. Для этого надо навести курсор на ярлычок и нажать правую кнопку мыши. В появившемся меню выбрать соответствующую команду. Кроме этого, также можно удалить, скопировать, переместить, вставить, скрыть и защитить лист.

Из-за большого количества листов в одном экселевском файле, такие файлы также называют книгами. Книгам присваиваются имена, их легко хранить, рассортировав по папкам.

Создание таблиц

В основном нужно знать, как работать с эксель-таблицами, поэтому всем от чайника до продвинутого программиста важно усвоить, на чём основана работа с таблицами в Excel. В отличие от вордовских аналогов, экселевские обладают рядом существенных преимуществ:

1. В них можно производить подсчёты и вычисления.
2. В них можно сортировать данные по выбранным критериям. Чаще всего по возрастанию или по убыванию.
3. Их можно связывать с другими страницами и делать динамическими, то есть при изменении данных в связанных полях будут меняться данные в других связанных полях.
4. Информацию таких банков данных можно использовать для построения гистограмм, графиков и других интерактивных элементов, что очень удобно для наглядного представления данных.

Это далеко не все преимущества экселевских документов. Само создание вычисляемых полей в Excel предельно простое. Вот нужные шаги для получения такого объекта:

Попробовав сделать одну небольшую табличку, любой чайник сможет без труда создавать объекты разной конфигурации и вида. Упражняясь с ними, со временем многие овладевают другими полезными свойствами экселевских элементов и с удовольствием используют их для своих нужд.

Вычисления с помощью формул

Освоившие начальные азы того, как работать в программе Excel с таблицами, смогут в дальнейшем самосовершенствоваться. Ведь в отличие от вордовского собрата экселевское хранилище данных даёт безграничные возможности для различных вычислений. Достаточно усвоить небольшой навык, как вставлять формулы и производить вычисления в этих объектах.

Чтобы создать вычисляемое поле в Excel, достаточно выделить ячейку и нажать на клавиатуре знак «=». После этого становятся доступны вычисления значений, содержащихся в любых ячейках данной таблицы. Чтобы сложить или вычесть значения, выделяют первую ячейку, затем ставят нужный знак вычисления и выделяют вторую ячейку. После нажатия на Enter в выбранной изначально ячейке появится результат вычисления. Таким образом, можно производить различные вычисления любых ячеек, отвечающих требованиям проводимых вычислений.

Для того чтобы работать в Экселе с таблицами было ещё проще, применяются многочисленные формулы. Например, для нахождения суммы нескольких ячеек находящихся в одной строке или в одном столбце, есть формула «сумм». Чтобы её использовать, достаточно после выбора ячейки и нажатия на «=» в строке над верхней строчкой листа с левой стороны выбрать нужную формулу из списка, который открывается после нажатия на маленький треугольничек в окошке с формулами.

Научившись пользоваться этой нехитрой технологией, многие любители статистики освободили себя от массы мучительных вычислений и ненамеренных ошибок, которые свойственны человеку, но отсутствуют у вычислительных машин.

Excel необходим в случаях, когда вам нужно упорядочить, обработать и сохранить много информации. Он поможет автоматизировать вычисления, делает их проще и надежнее. Формулы в Excel позволяют проводить сколь угодно сложные вычисления и получать результаты моментально.

Как написать формулу в Excel

Прежде чем учиться этому, следует понять несколько базовых принципов.

1. Каждая начинается со знака «=».
2. Участвовать в вычислениях могут значения из ячеек и функции.
3. В качестве привычных нам математических знаков операций используются операторы.
4. При вставке записи в ячейке по умолчанию отражается результат вычислений.
5. Посмотреть конструкцию можно в строке над таблицей.

Каждая ячейка в Excel является неделимой единицей с собственным идентификатором (адрес), который обозначается буквой (номер столбца) и цифрой (номер строки). Отображается адрес в поле над таблицей.

Итак, как создать и вставить формулу в Excel? Действуйте по следующему алгоритму:

Обозначение Значение

Сложение
- Вычитание
/ Деление
* Умножение

Если вам необходимо указать число, а не адрес ячейки – вводите его с клавиатуры. Чтобы указать отрицательный знак в формуле Excel, нажмите «-».

Как вводить и скопировать формулы в Excel

Ввод их всегда осуществляется после нажатия на «=». Но что делать, если однотипных расчетов много? В таком случае можно указать одну, а затем ее просто скопировать. Для этого следует ввести формулу, а затем «растянуть» ее в нужном направлении, чтобы размножить.
Установите указатель на копируемую ячейку и наведите указатель мыши на правый нижний угол (на квадратик). Он должен принять вид простого крестика с равными сторонами.


Нажмите левую кнопку и тяните.


Отпустите тогда, когда надо прекратить копирование. В этот момент появятся результаты вычислений.


Также можно растянуть и вправо.
Переведите указатель на соседнюю ячейку. Вы увидите такую же запись, но с другими адресами.


При копировании таким образом номера строки увеличиваются, если сдвиг происходит вниз, или увеличиваются номера столбцов – если вправо. Это называется относительная адресация.
Давайте введем в таблицу значение НДС и посчитаем цену с налогом.


Цена с НДС высчитывается как цена*(1+НДС). Введем последовательность в первую ячейку.


Попробуем скопировать запись.


Результат получился странный.


Проверим содержимое во второй ячейке.


Как видим, при копировании сместилась не только цена, но и НДС. А нам необходимо, чтобы эта ячейка оставалась фиксированной. Закрепим ее с помощью абсолютной ссылки. Для этого переведите указатель на первую ячейку и щелкните в строке формул на адрес B2.


Нажмите F4. Адрес будет разбавлен знаком «$». Это и есть признак абсолютно ячейки.


Теперь после копирования адрес B2 останется неизменным.
Если вы случайно ввели данные не в ту ячейку, просто перенесите их. Для этого наведите указатель мыши на любую границу, дождитесь, когда мышь станет похожа на крестик со стрелочками, нажмите левую кнопку и тяните. В нужном месте просто отпустите манипулятор.

Использование функций для вычислений

Excel предлагает большое количество функций, которые разбиты по категориям. Посмотреть полный перечень можно, нажав на кнопку Fx около строки формул или открыв раздел «Формулы» на панели инструментов.


Расскажем о некоторых функциях.

Как задать формулы «Если» в Excel

Эта функция позволяет задавать условие и проводить расчет в зависимости от его истинности или ложности. Например, если количество проданного товара больше 4 пачек, следует закупить еще.
Чтобы вставить результат в зависимости от условия, добавим еще один столбец в таблицу.


В первой ячейке под заголовком этого столбца установим указатель и нажмем пункт «Логические» на панели инструментов. Выберем функцию «Если».


Как и при вставке любой функции, откроется окно для заполнения аргументов.


Укажем условие. Для этого необходимо щелкнуть в первую строку и выбрать первую ячейку «Продано». Далее поставим знак «>» и укажем число 4.


Во второй строке напишем «Закупить». Эта надпись будет появляться для тех товаров, которые были распроданы. Последнюю строку можно оставить пустой, так как у нас нет действий, если условие ложно.


Нажмите ОК и скопируйте запись для всего столбца.


Чтобы в ячейке не выводилось «ЛОЖЬ» снова откроем функцию и исправим ее. Поставьте указатель на первую ячейку и нажмите Fx около строки формул. Вставьте курсор на третью строку и поставьте пробел в кавычках.


Затем ОК и снова скопируйте.


Теперь мы видим, какой товар следует закупить.

Формула текст в Excel

Эта функция позволяет применить формат к содержимому ячейки. При этом любой тип данных преобразуется в текст, а значит не может быть использован для дальнейших вычислений. Добавим столбец чтобы отформатировать итоговую сумму.


В первую ячейку введем функцию (кнопка «Текстовые» в разделе «Формулы»).


В окне аргументов укажем ссылку на ячейку итоговой суммы и установим формат «#руб.».


Нажмем ОК и скопируем.


Если попробовать использовать эту сумму в вычислениях, то получим сообщение об ошибке.

«ЗНАЧ» обозначает, что вычисления не могут быть произведены.
Примеры форматов вы можете видеть на скриншоте.

Формула даты в Excel

Excel предоставляет много возможностей по работе с датами. Одна из них, ДАТА, позволяет построить дату из трех чисел. Это удобно, если вы имеете три разных столбца – день, месяц, год.

Поставьте указатель на первую ячейку четвертого столбца и выберите функцию из списка «Дата и время».

Расставьте адреса ячеек соответствующим образом и нажмите ОК.


Скопируйте запись.

Автосумма в Excel

На случай, если необходимо сложить большое число данных, в Excel предусмотрена функция СУММ . Для примера посчитаем сумму для проданных товаров.
Поставьте указатель в ячейку F12. В ней будет осуществляться подсчет итога.


Перейдите на панель «Формулы» и нажмите «Автосумма».


Excel автоматически выделит ближайший числовой диапазон.


Вы можете выделить другой диапазон. В данном примере Excel все сделал правильно. Нажмите ОК. Обратите внимание на содержимое ячейки. Функция СУММ подставилась автоматически.


При вставке диапазона указывается адрес первой ячейки, двоеточие и адрес последней ячейки. «:» означает «Взять все ячейки между первой и последней. Если вам надо перечислить несколько ячеек, разделите их адреса точкой с запятой:
СУММ (F5;F8;F11)

Работа в Excel с формулами: пример

Мы рассказали, как сделать формулу в Excel. Это те знания, которые могут пригодиться даже в быту. Вы можете вести свой личный бюджет и контролировать расходы.


На скриншоте показаны формулы, которые вводятся для подсчета сумм доходов и расходов, а также расчет баланса на конец месяца. Добавьте листы в книгу для каждого месяца, если не хотите, чтобы все таблицы располагались на одном. Для этого просто нажмите на «+» внизу окна.

Чтобы переименовать лист, два раза на нем щелкните и введите имя.

Таблицу можно сделать еще подробнее.
Excel – очень полезная программа, а вычисления в нем дают практически неограниченные возможности.

Отличного Вам дня!

Формулы в Excel – одно из самых главных достоинств этого редактора. Благодаря им ваши возможности при работе с таблицами увеличиваются в несколько раз и ограничиваются только имеющимися знаниями. Вы сможете сделать всё что угодно. При этом Эксель будет помогать на каждом шагу – практически в любом окне существуют специальные подсказки.

Для создания простой формулы достаточно следовать следующей инструкции:

1. Сделайте активной любую клетку. Кликните на строку ввода формул. Поставьте знак равенства.

1. Введите любое выражение. Использовать можно как цифры,

При этом затронутые ячейки всегда подсвечиваются. Это делается для того, чтобы вы не ошиблись с выбором. Визуально увидеть ошибку проще, чем в текстовом виде.

Из чего состоит формула

В качестве примера приведём следующее выражение.

Оно состоит из:

• символ «=» – с него начинается любая формула;
• функция «СУММ»;
• аргумента функции «A1:C1» (в данном случае это массив ячеек с «A1» по «C1»);
• оператора «+» (сложение);
• ссылки на ячейку «C1»;
• оператора «^» (возведение в степень);
• константы «2».

Использование операторов

Операторы в редакторе Excel указывают какие именно операции нужно выполнить над указанными элементами формулы. При вычислении всегда соблюдается один и тот же порядок:

• скобки;
• экспоненты;
• умножение и деление (в зависимости от последовательности);
• сложение и вычитание (также в зависимости от последовательности).

Арифметические

К ним относятся:

• сложение – «+» (плюс);

• отрицание или вычитание – «-» (минус);

Если перед числом поставить «минус», то оно примет отрицательное значение, но по модулю останется точно таким же.

• умножение – «*»;

• деление «/»;

• процент «%»;

• возведение в степень – «^».

Операторы сравнения

Данные операторы применяются для сравнения значений. В результате операции возвращается ИСТИНА или ЛОЖЬ. К ним относятся:

• знак «равенства» – «=»;

• знак «больше» – «>»;

• знак «меньше» – «<»;