Уроки MS Excel

Как к дате прибавить день, месяц, год

Дата публикации: 27.01.2014

Для выполнения этой задачи нам понадобится функция «ДАТА()». Если вы используете для ввода мастер функций (кнопочка «fx» рядом со строкой формул), ищите ее в категории функций «Дата и время».
ДАТА
Функция имеет три аргумента:

  • год;
  • месяц;
  • день.

В качестве аргументов могут служить число, содержимое ячейки в формате «Дата» или математическое выражение.
В ячейке B2 у нас хранится дата «20.01.2014», а в ячейке C2 мы хотим получить дату, отстоящую от этой на два года вперед. Вводим в эту ячейку функцию ДАТА() со следующими аргументами:
ГОД+2
Теперь давайте попробуем вывести в ячейке C3 дату, отстоящую от сегодняшнего дня назад на 1 год, 3 месяца и 16 дней.
ГОД(СЕГОДНЯ())
Если вместо даты у вас в ячейке появилось какое-то число – проверьте формат ячейки, он должен быть «Дата».
Покоряйте Excel и до новых встреч!

Использование формул Если и Сумм

Дата публикации: 27.01.2014

Функция «СУММ()» предназначена для суммирования значений заданного диапазона, а «ЕСЛИ()» — логическая функция, умеющая проверять некое условие, и возвращать разные значения, в зависимости от того, условие «ИСТИНА» или «ЛОЖЬ».
Поскольку в вопросе фигурируют обе функции, речь видимо идет о подсчете суммы по некоторому условию. Давайте посмотрим, как это можно сделать.

Как посчитать сумму по условию в Excel 2007

Сделаем маленькую табличку, состоящую из списка заказов и их стоимости. Давайте подсчитаем, какова сумма, полученная от заказов, стоимость которых выше средней, и какова сумма, полученная от выполнения дешевых заказов.
СУММ(ЕСЛИ())
Как видите из введенной функции, каждое значение из диапазона «C3:C12» будет сравниваться со средним значением этого диапазона, и если это значение больше или равно среднему, то в суммирование будет подставляться оно, а если меньше, то ноль. Теперь один важный момент! Закончив вводить эту функцию, нажмите не «Enter», как обычно, а «Ctrl+Shift+Enter». Таким образом функция вводится как функция массива и в строке формул она будет заключена в фигурные скобки. В обычном режиме функция «СУММ()» с этой задачей не справится.
ввод функции массива
Ну и по дешевым товарам
пример

Как это сделать в Excel 2003

Функции «СУММ» и «ЕСЛИ» работают так же и в этой версии.
Покоряйте Excel и до новых встреч!

Excel: Защита ячейки от изменения

Дата публикации: 27.01.2014

Для того чтобы сделать ячейку защищенной от изменений, нужно выполнить два условия:

  • нужно чтобы в свойствах самой ячейки она была помечена как защищаемая;
  • включить защиту листа Excel.

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

Включаем защиту в Excel 2007

Выделите весь лист целиком («Ctrl+A») и в контекстном меню, открывающемся по правому щелчку мыши, выберите «Формат ячеек». Снимите флажок.
защищаемая ячейка
А теперь выделите ячейки, которым требуется защита, и снова его поставьте.
Теперь переходим на вкладку «Рецензирование» и нажимаем кнопку «Защитить лист».
защитить лист
После этого вас дважды попросят ввести пароль (не забудьте его). И теперь при попытке внести в эти ячейки какие-либо изменения вы увидите такое сообщение —
сообщение

Как это сделать в Excel 2003

Действуем так же, а для включения защиты листа обращаемся в меню «Сервис».
защитить лист 2003
Покоряйте Excel и до новых встреч!

Добавление новых строк в Excel

Дата публикации: 27.01.2014

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

Решение для Word 2010


ВКЛЮЧИТЕ СУБТИТРЫ!

Добавляем строки в Excel 2007


ВКЛЮЧИТЕ СУБТИТРЫ!
Предположим, нам понадобилось вставить три строки между второй и третьей. Выделим три строки ниже второй, правый щелчок, и в контекстном меню выбираем «Вставить…».
вставить
В окне «Добавление ячеек» по умолчанию будет стоять выбор «ячейки со сдвигом вниз». В этом случае добавятся только ячейки в эту таблицу. Но можно выбрать опцию «строку», и тогда добавятся строки во всем листе Excel.
со сдвигом вниз
По тому же принципу добавляются и колонки.

Как это сделать в Excel 2003


ВКЛЮЧИТЕ СУБТИТРЫ!
Действуем точно так же. Единственное отличие – пункт в контекстном меню называется «Добавить ячейки».
добавить ячейки
Покоряйте Excel и до новых встреч!

Вставляем календарь на лист

Дата публикации: 27.01.2014

К сожалению, в стандартной поставке Excel отсутствует надстройка «Календарь». И если вам приходится часто вводить разнообразные даты, и вы хотите делать это, выбирая их из календаря – у вас два выхода. Срочно заняться изучением VBA (Visual Basic for Applications), или воспользоваться готовой надстройкой, созданной другими пользователями. Благо в интернете их множество.
Могу посоветовать в качестве варианта Windows Date Picker от Ron de Bruin. Достаточно удобный, автоматически подхватывает национальные настройки отображения дат, встраивается в контекстное меню. Скачать его можно, зайдя на сайт автора.

Как установить надстройку Windows Date Picker в Excel 2007

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

Как это сделать в Excel 2003

Добраться до надстроек можно через меню «Сервис»
надстройки
А дальше все то же.
Покоряйте Excel и до новых встреч!

Вставка картинок и работа с ними

Дата публикации: 27.01.2014

Пользуемся стандартным меню.

Вставляем картинку в Excel 2007

  • Закладка «Вставка».
  • Кнопка «Рисунок».
  • Выбираем файл с рисунком.

Картинка вставится в активную ячейку левым верхним углом. Беремся мышкой за маркеры и подгоняем до нужного размера.
вставляем картинку
Маленькая подсказка – если вы хотите выровнять картинку по линиям ячеек, при перетаскивании маркеров держите нажатой клавишу «Alt».

  • Правый щелчок.
  • «Размер и свойства».

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

Как это сделать в Excel 2003

Вставляем рисунок из меню «Вставка».
вставка
А для настройки привязки к фону выберите пункт «Формат рисунка» в контекстном меню и перейдите на закладку «Свойства».
Покоряйте Excel и до новых встреч!

Вычисляем процент от числа

Дата публикации: 27.01.2014

В Excel это считается точно так же, как и везде. Просто помним, что процент – это сотая часть числа.
И чтобы вычислить, сколько будет 8% от 358, нам нужно 358 разделить на 100 и результат умножить на 8.
процент от числа
Можно эту задачку решить и немного по-другому, используя знак % на клавиатуре.
процент
А чтобы узнать, сколько процентов составляет число 432 от 358, нам нужно это число разделить на величину одного процента от 358, то есть на 358/100.
сколько процентов
Впрочем, в последнем случае Excel может немного облегчить нам задачу. Мы можем просто разделить 432 на 358, а ячейке, где хранится результат задать формат данных «Процентный».
процентный формат
Тогда после нажатия кнопки «ОК», результат будет выглядеть так.
результат
Покоряйте Excel и до новых встреч!

Возведение в квадрат и извлечение корня

Дата публикации: 27.01.2014

Возведение в степень и извлечение корней в Excel выполняется с помощью одной и той же функции «СТЕПЕНЬ(число;степень)». Просто помним, что извлечь корень n-ой степени, это то же самое, что возвести в степень 1/n.
возведение в квадрат
На рисунке показано, как возводится в квадрат значение в ячейке R2C2.
А так мы извлекаем корень кубический.
корень кубический
Впрочем для извлечения квадратного корня существует и специальная функция, которая так и называется «КОРЕНЬ(число)».
квадратный корень
А еще удобнее работать со степенями, используя значок степени – «^». Он располагается там же, где и цифра 6 в верхнем регистре английской раскладки клавиатуры. С этим значком при вычислении 161,25, нужно будет ввести в ячейку такое выражение – «=16^1.25». Или подставить вместо 16 адрес ячейки, в которой это значение хранится.
возведение в степень
Покоряйте Excel и до новых встреч!

Округление чисел в большую и меньшую сторону

Дата публикации: 27.01.2014

Никакому бухгалтеру не нужны цены, в которых фигурируют сотые доли копеек или среднесписочный состав, в котором значатся десятые доли сотрудников. А с другой стороны, вы знаете, что в Excel, цифры, которые вы видите на экране, не всегда точно соответствуют реальному содержимому ячейки.
значение в ячейке
На рисунке мы видим в ячейке R2C3, которой задан числовой формат с отображением двух знаков после запятой, число «30,12», но реально там хранится значение «30,1245832». И именно такое значение будет подставляться в расчетах и формулах, в которых используется эта ячейка. После длинной цепочки вычислений это разночтение может привести к достаточно неожиданным результатам.
Вот почему вычисляемое значение нужно вовремя округлить. В Excel используются разнообразные способы округления, но наиболее часто бывают востребованными, конечно, обычное математическое округление и округление в большую или меньшую сторону.
Для округления по привычным нам математическим правилам используется функция «ОКРУГЛ(число;число_разрядов)».
ОКРУГЛ()
Теперь в ячейке R2C4 мы получим значение числа, размещенного в ячейке R2C3, округленное с точностью до двух знаков после запятой.
При округлении больших чисел разряды указываются со знаком «минус». К примеру, функция «ОКРУГЛ(1256421;-3)» выдаст нам значение, округленное до тысяч — «1256000».
Функция «ОКРУГЛВВЕРХ(число;число_разрядов)» округляет число с заданной точностью до ближайшего большего по модулю, а «ОКРУГЛВНИЗ(число;число_разрядов)» — до ближайшего меньшего.
На рисунках ниже показан ввод функции для округления в большую сторону и результат ее работы.
ОКРУГЛВВЕРХ()
результат
Эти функции одинаково работают как в Excel 2007, так и в более старшей версии 2003 года.
Покоряйте Excel и до новых встреч!

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

Дата публикации: 23.01.2014

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

Инструкция для Excel 2010


ВКЛЮЧИТЕ СУБТИТРЫ!

Как это сделать в Excel 2007


ВКЛЮЧИТЕ СУБТИТРЫ!
Выделим ячейки с ценами заказов и, нажав на стрелочку рядом с кнопкой «Условное форматирование», выберем «Создать правило».
создаем правило
Выберем четвертый пункт, позволяющий сравнивать текущие значения со средним. Нас интересуют значения выше среднего. Нажав кнопку «Формат», зададим цвет ячеек.
выше среднего
Подтверждаем наш выбор, и ячейки с ценой выше средней окрасились в голубой цвет, привлекая наше внимание к дорогим заказам.
дорогие заказы
Выделим ячейки со статусами заказов и создадим новое правило. На этот раз используем второй вариант, позволяющий проверять содержимое ячейки. Выберем «Текст», «содержит» и введем слово «Выполнен». Зададим зеленый цвет, подтверждаем, и выполненные работы у нас позеленели.
содержит текст
Ну и сделаем еще одно правило, окрашивающее просроченные заказы в красный цвет. Выделяем даты выполнения заказов. При создании правила снова выбираем второй пункт, но на этот раз задаем «Значение ячейки», «меньше», а в следующем поле вводим функцию, возвращающую сегодняшнюю дату.
сравнение дат
«ОК», и мы получили весело разукрашенную таблицу, позволяющую наглядно отслеживать ход выполнения заказов.
таблица
Обратили внимание, что статусы задаются выбором из выпадающего списка значений? Как делать такие списки, мы рассказывали в инструкции «Как в Excel сделать выпадающий список».

Как это сделать в Excel 2003


ВКЛЮЧИТЕ СУБТИТРЫ!
«Условное форматирование» в меню «Формат». Тут понадобится немного больше ручной работы. Вот так будут выглядеть настройки для нашей первой задачи – закрасить ячейки со значениями больше средних.
условное форматирование 2003
Придется вручную ввести функцию «=СРЗНАЧ()», поставить курсор между скобками, нажать на кнопочку рядом и мышкой указать нужный диапазон.
Но принцип действий тот же самый.
Покоряйте Excel и до новых встреч!