Excel 2010 для начинающих: Формулы, автозаполнение и редактирование таблиц

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

Оглавление

Введение

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

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

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

Выполнение базовых арифметических операций

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

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

Например, давайте представим, что нам необходимо сложить два числа – «12» и «7». Установите курсор мыши в любую ячейку и напечатайте следующее выражение: «=12+7». По окончании ввода нажмите клавишу «Enter» и в ячейке отобразится результат вычисления – «19».

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

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

После проведения всех операций, обратите внимание на результат деления чисел 12 на 7, который получился не целым (1,714286) и содержит довольно много цифр после запятой. В большинстве случаев такая точность не требуется, да и столь длинные числа будут только загромождать таблицу.

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

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

Составление формул

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

Чтобы посчитать суммарный расход за январь в ячейке B7 можно написать следующее выражение: «=18250+5100+6250+2500+3300» и нажать Enter, после чего вы увидите результат вычисления. Это является примером применения простейшей формулы, составление которой ничем не отличается от вычислений на калькуляторе. Разве что знак равно ставится вначале выражения, а не в конце.

С учетом этого давайте изменим нашу формулу вычисления суммарных ежемесячных расходов.

Установите курсор на пустой клетке сбоку нужной строки (в нашем случае это H2). Затем нажмите кнопку Сумма на закладке Главная в группе Редактирование. Теперь, вернемся к таблице и посмотрим, что же произошло.

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

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

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

Автозаполнение

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

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

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

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

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

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

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

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

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

Добавление строк, столбцов и объединение ячеек

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

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

Щелкните в любой ячейке второй строки правой кнопкой мыши и в открывшемся меню выберите команду Вставить…, а затем в окне — Добавить строку.

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

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

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

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

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

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

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

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

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

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

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

После выполнения команды, все выделенные ячейки в строке объединятся в одну большую ячейку.

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

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

Заключение

Для заполнения оставшихся ячеек с D14 по G14 применим возможность их автоматического заполнения, так же как мы это делали в случае с суммами.

Кстати, для проверки значения итоговых накоплений на июнь, находящегося в клетке G14, в ячейке H14 можно вывести разницу между общей суммой ежемесячных доходов (H5) и ежемесячных расходов (H12). Как вы понимаете, они должны быть равны.

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

А вот и наша итоговая таблица с выполненными расчётами:

Теперь, при желании, вы уже самостоятельно сможете продолжать ее наполнение, вставляя как дополнительные статьи расходов или доходов (строки), так и добавляя новые месяцы (столбцы).

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

[expert_bq id=»1570″]Это говорит о том, что, когда ИНДЕКС работает с векторами, то он второй параметр воспринимает не как номер строки, а как номер элемента одномерного массива. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] И я могу вам это доказать! Если формула возвращает нам ссылку на ячейку, а не её значение, то с результатом работы формулы ИНДЕКС должны работать все ТРИ оператора Excel по работе с ссылками: оператор задания диапазона — двоеточие , оператор перечисления диапазонов — точка с запятой и наконец оператор нахождения пересечения диапазонов — пробел .

Как найти значение выражения в excel

• Выделите столбец с формулами (формулы м.б. разными)
• Вызовите Текст-по-столбцам
• После вызова этого инструмента во втором окне укажите в качестве разделителя апостроф ‘
• Нажмите Готово

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

Как использовать формулы в таблицах Excel

Excel — это одна из программ пакета Microsoft office, созданного для производства различных документов. Эксель позволяет создавать табличные документы, в нем удобно делать и самые простые вычисления, используя как калькулятор, и сложные расчеты, такие как, например, строительные сметы.

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

Как вставить формулу в ячейку

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

Затем нажимает «Ввод», в поле автоматически появляется результат сложения двух значений.

В Экселе используются стандартные математические символы:

Как создать таблицу с формулами

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

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

Чтобы рассчитать общую стоимость каждого наименования товаров, необходимо стоимость единицы каждого из них умножить на количество единиц этого товара. Вычислим общую стоимость Товара 1. Для этого выделяем поле D2, ставим знак равенства, выделяем В2, прописываем алгебраический символ умножения «*» и выделяем С2.

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

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

После отпускания клавиши мыши в захваченный полях появятся искомые значения.

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

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

Как обозначить постоянную ячейку

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

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

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

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

Примеры формул в Экселе

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

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

Сформируем цельные даты в столбце D. Для этого выделим D2, а в меню формул выбираем «ДАТА».

В появившемся окне впишем наименования ячеек, в которых содержится информация о годе, месяце и дне, это А2, В2 и С2 соответственно.

После нажатия кнопки «ОК» в выделенном поле появится дата.

Формулу распространяем на ячейки ниже в столбце, растянув D2, после чего появятся даты.

Текст

Функция текста дает возможность перевести ячейку в текстовый формат. То есть любое ее содержимое отныне является текстом и не сможет принимать участие в расчете. Если координату текстового поля включат в формулу, вычисления не произойдет, и система выдаст ошибку #ЗНАЧ!

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

Выделим верхнюю ячейку этого столбца — Е2 и выберем в меню формул «ТЕКСТ».

В появившемся окне заполним строки: значение — это координаты, откуда взято число, — D2, формат — текстовое обозначение — #руб.

После нажатия кнопки «ОК» в ячейке появится итоговая цифра с наименованием валюты.

Автосумма

Функция автосуммы позволит быстро вычислить общую сумму значений выбранных ячеек.

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

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

Другие примеры

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

Эксель — это наиболее полный и удобный сервис по работе с таблицами, но некоторые функции предоставляет и Ворд.

[expert_bq id=»1570″]После проведения всех операций, обратите внимание на результат деления чисел 12 на 7, который получился не целым 1,714286 и содержит довольно много цифр после запятой. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Выделяем диапазон, который следует заполнить, начиная с ячейки с уже введенными данными. Затем на вкладке Главная в группе Редактирование нажимаем кнопку Заполнить и выбираем направление заполнения.

Как вставить формулу в таблицу excel или word

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

Как Присваивать Значения Формулам в Excel

Формула – это выражение, которое состоит из последовательности операндов (аргументов), разделенных операторами

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

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

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

В формуле 4 аргумента, представленных константами. Три математических оператора: сложение, умножение, вычитание

В формуле 4 аргумента: ссылка на ячейку и три константы. Операторы — два умножения и один — сложения.

Константы – текстовые или числовые значения, которые вводятся в ячейку и не могут изменяться во время вычислений

Ссылка на ячейку или группу ячеек – способ, которым указывается конкретная ячейка или несколько ячеек.

Ссылка на отдельную ячейку является ее координатой.

• Относительные – ячейки обозначаются относительным смещением от ячейки с формулой (например: F7).
• Абсолютные – ячейки обозначаются координатами ячеек в сочетании со знаком $ (например: $F$7).
• Комбинация предыдущих типов (например: F$7) – смешанные.

При копировании формул относительные ссылки изменяются на размер перемещения!

Операторами обозначаются операции, которые следует выполнить над операндами формулы

В Microsoft Excel включено четыре вида операторов:

Служат для выполнения арифметических операций, таких как сложение, вычитание, умножение

Арифметические операции выполняются над числами!

Используются арифметические операторы:

Используются для сравнения двух значений. Результатом сравнения является логическое значение: либо ИСТИНА, либо ЛОЖЬ.

Для описания ссылок на диапазоны ячеек

1. Формула в Microsoft Excel всегда начинается со знака равенства (=). Знак равенства свидетельствует о том, что последующие знаки составляют формулу
2. Элементы, следующие за знаком равенства, являются операндами, разделяемыми операторами вычислений
3. Формулы вычисляют значения в определенном порядке. Порядок определяется обычными математическими законами: формула вычисляется слева направо, в соответствии с определенным порядком (приоритетом) для каждого оператора в формуле
4. Если формула содержит операторы с одинаковым приоритетом — например операторы деления и умножения — они выполняются слева направо
5. Если в одной формуле используется несколько операторов, Microsoft Excel выполняет операции в порядке, показанном в таблице

Повышение приоритета операций нужно для обеспечения правильности вычислений.

Для повышения приоритета операции используют круглые скобки

Допустим, необходимо составить формулу для вычисления выражения, представленного в виде дроби:

Правильный результат достигается только в третьем случае. Числитель делится на знаменатель.

Если формула в ячейке не может быть правильно вычислена, Microsoft Excel выводит в ячейку сообщение об ошибке

Если формула содержит ссылку на ячейку, которая содержит значения ошибки, то вместо этой формулы также будет выводиться сообщение об ошибке

#### – ширина ячейки не позволяет отобразить число в заданном формате;

#ИМЯ? – Microsoft Excel не смог распознать имя, использованное в формуле;

#ДЕЛ/0! – в формуле делается попытка деления на нуль;

#ЧИСЛО! – нарушены правила задания операторов, принятые в математике;

#Н/Д – такое сообщение может появиться, если в качестве аргумента задана ссылка на пустую ячейку;

#ПУСТО! – неверно указано пересечение двух областей, которые не имеют общих ячеек;

#ССЫЛКА! – в формуле задана ссылка на несуществующую ячейку;

[expert_bq id=»1570″]Я буду описывать функции в виде функция аргумент1; аргумент2 и расскажу о том, какие аргументы являются обязательными, а какие нет. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Я вам заодно расскажу о некоторых математических функциях, которые могут понадобиться при вычислениях. Я буду описывать функции в виде функция (аргумент1; аргумент2…) и расскажу о том, какие аргументы являются обязательными, а какие — нет. Итак, математические функции.

Наиважнейшая формула Excel — Формулы рабочего листа — Excel — Каталог статей — Perfect Excel

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