Работа в excel для работы в офисе

1 СУММ: Сложение

Сумма — самая популярная функция Excel, которой пользуются абсолютно все – и школьники, и серьезные бухгалтеры, и аналитики. Как следует из названия, функция складывает значения в указанном диапазоне. Все просто – поставьте в ячейку знак равно, выберете функцию СУММ, выделите область, в которой находятся числа для сложения, и нажмите «ОК». В ячейке появится сумма выбранных значений.

Функция может сложить как два, так и миллион чисел — настоящий супер-калькулятор.

2 ЕСЛИ: Условие

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

Аргументами функции являются:

• Лог_выражение — здесь надо задать параметр, по которому будут отбираться значения.
• Значение_если_истина — то, что будет на экране, если логическое условие верно.
• Значение_если_ложь — имя значения, которое не соответствует критерию, указанному в логическом выражении.

Вот небольшой пример: необходимо рассортировать данные таблицы с товаром. Зададим логическое выражение: «если стоимость больше 50». Если выражение верно, будем считать это значение как «Много», если нет — как «Мало». Нажмем «ОК».

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

3 МАКС: Максимум

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

По тому же принципу работает функция для поиска минимума – МИН.

4 СРЗНАЧ: Среднее значение

Данная формула служит для поиска среднего значения среди множества ячеек таблицы. Называется она довольно очевидно – СРЗНАЧ. Аргументом данной функции является диапазон, в котором вы хотите найти среднее арифметическое.

5. СЧЕТ: Подсчет количества значений

Функция СЧЕТ подсчитывает количество ячеек в указанном диапазоне, которые содержат числа. Например, у вас есть 9 позиций товаров, а надо узнать, сколько из них было куплено. Выделяем необходимую область со значениями и нажимаем «ОК» — функция выдаст результат. В нашем случае все можно было посчитать и вручную, а что делать, если таких значений сотни? Верно — использовать операцию СЧЕТ.

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

6 СЧЕТЕСЛИ: Подсчет определенных значений

Также в Excel есть функция СЧЕТЕСЛИ — она позволяет подсчитывать ячейки по заданному критерию. Аргументами в таком случае является не только диапазон, но и условие подсчета.

Для более сложных операций существует функция СЧЕТЕСЛИМН — в ее аргументах можно задать несколько критериев счета.

7 СУММЕСЛИ: Сложение при условии

Функция СУММЕСЛИ служит для того, чтобы складывать избранные числа из выделенного диапазона. По сути, это сочетание СУММ и ЕСЛИ. Аргументами операции являются диапазон условия, критерий и область суммирования.

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

8. СУММЕСЛИМН: Сложение по многим критериям

Если вам необходимо сложить значения, которые нельзя описать одним условиям, используйте функцию СУММЕСЛИМН. Она позволяет задать до 127 разных критериев. Данная операция — одна из самых востребованных в экономике, так как открывает действительно широкие возможности для работы с таблицами в Excel.

8 СЦЕПИТЬ: Объединение текстовых значений

Очень часто в текстовых таблицах хранится много отдельных значений, которые удобно читать вместе. Например, в таблице с ФИО. Для объединения текста служит операция СЦЕПИТЬ. Все просто: выделите необходимые строки, которые хотите соединить, и нажмите «ОК». Но помните, что пробелы придется добавить самому — сразу в функции или в строках с текстом.

10. ВПР: Поиск значений

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

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

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

ВПР считается сложной функцией. Она может обработать большое количество данных и в считанные секунды найдет нужное вам значение.

Читайте также:
Как закрепить строку в Excel при прокрутке
Как выполнять расчеты времени в Excel

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

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

Инструкция по работе в Excel

Книга Excel состоит из листов. Лист – рабочая область в окне. Его элементы:

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

Значения могут быть числовыми, текстовыми, денежными, процентными и т.д. Чтобы установить/сменить формат, щелкаем по ячейке правой кнопкой мыши, выбираем «Формат ячеек». Или жмем комбинацию горячих клавиш CTRL+1.

Для числовых форматов можно назначить количество десятичных знаков.

Примечание. Чтобы быстро установить числовой формат для ячейки — нажмите комбинацию горячих клавиш CTRL+SHIFT+1.

Для форматов «Дата» и «Время» Excel предлагает несколько вариантов изображения значений.

Отредактируем значение ячеек:

1. Щелкнем по ячейке со словом левой кнопкой мыши и введем число, например. Нажимаем ВВОД. Слово удаляется, а число остается.
2. Чтобы прежнее значение осталось, просто изменилось, нужно щелкнуть по ячейке два раза. Замигает курсор. Меняем значение: удаляем часть текста, добавляем.



3. Отредактировать значения можно и через строку формул. Выделяем ячейку, ставим курсор в строку формул, редактируем текст (число) – нажимаем Enter.

Для удаления значения ячейки используется кнопка Delete.

Чтобы переместить ячейку со значением, выделяем ее, нажимаем кнопку с ножницами («вырезать»). Или жмем комбинацию CTRL+X. Вокруг ячейки появляется пунктирная линия. Выделенный фрагмент остается в буфере обмена.

Ставим курсор в другом месте рабочего поля и нажимаем «Вставить» или комбинацию CTRL+V.

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

Чтобы переместить несколько ячеек, их нужно выделить:

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

Чтобы выделить столбец, нажимаем на его имя (латинскую букву). Для выделения строки – на цифру.

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

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

Чтобы сохранить ширину столбца, но увеличить высоту строки, нажимаем на ленте кнопок «Перенос текста».

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

Объединим несколько ячеек: выделим их и нажмем кнопку «Объединить и поместить в центре».

В Excel доступна функция автозаполнения. Вводим в ячейку А2 слово «январь». Программа распознает формат даты – остальные месяцы заполнит автоматически.

Цепляем правый нижний угол ячейки со значением «январь» и тянем по строке.

Апробируем функцию автозаполнения на числовых значениях. Ставим в ячейку А3 «1», в А4 – «2». Выделяем две ячейки, «цепляем» мышью маркер автозаполнения и тянем вниз.

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

Чтобы скопировать столбец на соседний, выделяем этот столбец, «цепляем» маркер автозаполнения и тянем в сторону.

Таким же способом можно копировать строки.

Удалим столбец: выделим его – правой кнопкой мыши – «Удалить». Или нажав комбинацию горячих клавиш: CTRL+»-«(минус).

Чтобы вставить столбец, выделяем соседний справа (столбец всегда вставляется слева), нажимаем правую кнопку мыши – «Вставить» — «Столбец». Комбинация: CTRL+SHIFT+»=»

Чтобы вставить строку, выделяем соседнюю снизу. Комбинация клавиш: SHIFT+ПРОБЕЛ чтобы выделить строку и нажимаем правую кнопку мыши – «Вставить» — «Строку» (CTRL+SHIFT+»=»)(строка всегда вставляется сверху).



Как работать в Excel: формулы и функции для чайников

Чтобы программа воспринимала вводимую в ячейку информацию как формулу, ставим знак «=». Например, = (2+3)*5. После нажатия «ВВОД» Excel считает результат.

Последовательность вычисления такая же, как в математике.

Формула может содержать не только числовые значения, но и ссылки на ячейки со значениями. К примеру, =(A1+B1)*5, где А1 и В1 – ссылки на ячейки.

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

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

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

В каждой ячейке столбца С второе слагаемое в скобках – 3 (ссылка на ячейку В1 постоянна, неизменна).

Значительно расширяют функционал программы встроенные функции. Чтобы вставить функцию, нужно нажать кнопку fx (или комбинацию клавиш SHIFT+F3). Откроется окно вида:

Чтобы не листать большой список функций, нужно сначала выбрать категорию.

Когда функция выбрана, нажимаем ОК. Откроется окно «Аргументы функции».

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

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

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

Аргумент функции КОРЕНЬ – ссылка на ячейку A1:

ВВОД – программа нашла квадратный корень из числа 40.

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

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

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

Я расскажу вам, как делить данные в ячейках по разным столбцам и как создать выпадающий список. Вам будет нелишним узнать о полезной функции анализа «Что Если». Вы наконец-то научитесь применять популярные функции ЕСЛИ и ВПР в своей работе. Научитесь создавать сводные таблицы и создавать макросы для автоматизации ваших повторяющихся действий. Кстати, более углубленные знания в этом направлении, вы можете получить в платном курсе «Excel и Google-таблицы: от новичка до эксперта».

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

Урок 1. Меню и вкладки в Excel

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

Урок 2. Понимание книг и рабочих листов, строка состояния. Добавление и удаление строк и столбцов

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

Урок 3. Изучение методов ввода и редактирования данных. Шаблоны Excel и заметки

Ввод данных — одно из самых основных действий, которые мы делаем в Excel, но есть некоторые правила, некоторые концепции, с которыми вам следует ознакомиться. Этим мы и займемся в нашем третьем уроке.

Урок 4. Создание и копирование простых формул в Excel

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

Урок 5. Комбинированный подсчет, вычисление процентов, закрепить ячейку в формуле excel ($)

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

Урок 6. Функции СУММ, СРЕДНЕЕ, РАНГ, НАИБОЛЬШИЙ

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

Урок 7. Регулирование размера строк и столбцов, объединение ячеек, перенос записи, выравнивание и работа с границами

Сегодня мы разберем способы форматирования рабочей области Excel. Я предполагаю, что вы уже знакомы с некоторыми продуктами Microsoft Office, как, например, Word, поэтому я не буду рассматривать те функции, с которыми вы, скорее всего, уже знаете.

Урок 8. Числовой и финансовый формат. Даты и условное форматирование

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

Урок 9. Создание и форматирование таблицы. Фильтрация и срезы в Excel

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

Урок 10. Работа с фигурами, добавление рисунков и схемы SmartArt в Excel

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

Урок 11. Копирование и перенос строк таблицы, скрытие строк и столбцов. Найти и заменить в Excel

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

Урок 12. Как сделать диаграмму в excel

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

Урок 13. Форматирование диаграмм, Спарклайны, Гистограмма и диаграмма Парето в Excel

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

Урок 14. Как закрепить строку, разделить таблицу, и функция «группировать»  

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

Урок 15. Как перемещаться между книгами Excel и упорядочить несколько окон

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

Урок 16. Создание, копирование и группировка рабочих листов Excel

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

Урок 17. Как посчитать данные из разных листов и файлов в Excel

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

Урок 18. Сортировка и фильтрация данных в Excel

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

Урок 19. Как разделить текст в ячейке excel по столбцам. Удаление дубликатов

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

Урок 20. Проверка значений и как сделать выпадающий список в excel

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

Урок 21. Применение функции анализа «Что Если»

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

Урок 22. Функция ЕСЛИ в Excel. Подробное объяснение с примерами

Одна из самых мощных функций Excel называется функцией ЕСЛИ. По своей природе она почти похожа на программирование и открывает двери для другого образа мышления. Функция ЕСЛИ – это условная функция. Она позволяет нам давать несколько ответов в зависимости от условия. 

Урок 23. Функция ВПР в Excel. Подробное объяснение с примерами

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

Урок 24. Функции СЧЁТЕСЛИ и СУММЕСЛИ в Excel

Сегодня будет короткий урок, где мы закончим разбирать популярные функции Excel и рассмотрим две сразу: СЧЁТЕСЛИ и СУММЕСЛИ.

Урок 25. Как сделать сводную таблицу в Excel и манипулировать ее данными

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

Урок 26. Группировка и фильтры в сводной таблице excel. Сводные диаграммы.

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

Урок 27. Создание простых макросов в Excel

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

Урок 28. Как защитить рабочий лист или книгу и поделиться файлом Excel

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

Урок 29. Как печатать страницы и заголовки на каждой странице. Как вставить колонтитул в excel

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

Таблицы Excel — очень мощный инструмент. В них больше 470 скрытых функций. Поначалу это пугает: кажется, на то, чтобы разобраться со всем, уйдут годы. На самом деле это не так. Всего десятка функций и горячих клавиш уже хватит для того, чтобы сильно упростить себе жизнь. Расскажем о некоторых из них (скоро стартует второй поток курса «Магия Excel»).

Интерфейс

Настраиваем панель быстрого доступа

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

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

Другой вариант — просто щелкнуть по инструменту на ленте правой кнопкой мыши и нажать «Добавить…»:

Перемещаемся по ленте без мышки

Нажмите на Alt. На ленте инструментов появились цифры и буквы — у каждого инструмента на панели быстрого доступа и у каждой вкладки на ленте соответственно:

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

Ввод данных

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

Автозамена

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

Прогрессия

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

Протягивание

Представьте, что вам нужно извлечь какие-то данные из целого столбца или переписать их в другом виде (например, фамилию с инициалами вместо полных ФИО). Задайте Excel одну ячейку с образцом — что хотите получить:

Выделите все ячейки, которые хотите заполнить по образцу, — и нажмите Ctrl+E. И магия случится (ну, в большинстве случаев).

Проверка ошибок

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

Какие бывают типовые ошибки в Excel?

• Текст вместо чисел
• Отрицательные числа там, где их быть не может
• Числа с дробной частью там, где должны быть целые
• Текст вместо даты
• Разные варианты написания одного и того же значения. Например, сокращения («ЭБ» вместо «Электронная библиотека»), лишние пробелы в конце текстового значения или между словами — всего этого достаточно, чтобы превратить текстовые значения в разные и, соответственно, чтобы они обрабатывались Excel некорректно.

Инструмент проверки данных

Чтобы использовать инструмент проверки данных, нужно выделить ячейки, к которым хотите его применить, выбрать на ленте «Данные» → «Проверка данных» и настроить параметры проверки в диалоговом окне:

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

Если же вы выбрали «Предупреждение» или «Сообщение», то при попытке ввести неверные данные будет появляться предупреждение, но его можно будет проигнорировать и все равно ввести что угодно.

Еще неверные данные можно обвести, чтобы точно увидеть, где есть ошибки:

Удаление пробелов

Для удаления лишних пробелов (в начале, в конце и всех кроме одного между слов) используйте функцию СЖПРОБЕЛЫ / TRIM. Ее единственный аргумент — текст (ссылка на ячейку с текстом, как правило).

Если после очистки данных функцией СЖПРОБЕЛЫ или другой обработки вам не нужен исходный столбец, вставьте данные, полученные в отдельном столбце с помощью функций, как значения на место исходных данных, а столбец с формулой удалите:

Дата и время

За любой датой в Excel скрывается целое число. Датой его делает формат.

Аналогично со временем: одна единица — это день, а часть единицы (число от 0 до 1) — время, то есть часть дня.

Это не значит, что так имеет смысл вводить даты и время в ячейки, вводите их в любом из стандартных форматов — Excel сразу отформатирует их как даты:

ДД.ММ.ГГГГ

ДД/ММ/ГГГГ

ГГГГ-ММ-ДД

С датами можно производить операции вычитания и сложения.

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

Прибавить к дате число — и получить дату, которая наступит через соответствующее количество дней.

Поиск и подстановка значений

Функция ВПР / VLOOKUP

Функция ВПР / VLOOKUP (вертикальный просмотр) нужна, чтобы связать несколько таблиц — «подтянуть» данные из одной в другую по какому-то ключу (например, названию товара или бренда, фамилии сотрудника или клиента, номеру транзакции).

=ВПР (что ищем; таблица с данными, где «что ищем» должно быть в первом столбце; номер столбца таблицы, из которого нужны данные; [интервальный просмотр])

У нее есть два режима работы: интервальный просмотр и точный поиск.

Интервальный просмотр — это поиск интервала, в который попадает число. Если у вас прогрессивная шкала налога или скидок, нужно конвертировать оценку из одной системы в другую и так далее — используется именно этот режим. Для интервального просмотра нужно пропустить последний аргумент ВПР или задать его равным единице (или ИСТИНА).

В большинстве случаев мы связываем таблицы по текстовым ключам — в таком случае нужно обязательно явным образом указывать последний аргумент «интервальный_просмотр» равным нулю (или ЛОЖЬ). Только тогда функция будет корректно работать с текстовыми значениями.

Функции ПОИСКПОЗ / MATCH и ИНДЕКС / INDEX

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

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

Функция ПОИСКПОЗ / MATCH определяет порядковый номер значения в диапазоне. Ее синтаксис:

=ПОИСКПОЗ (что ищем; где ищем ; 0)

На выходе — число (номер строки или столбца в рамках диапазона, в котором находится искомое значение).

ИНДЕКС / INDEX выполняет другую задачу — возвращает элемент по его номеру.

=ИНДЕКС(диапазон, из которого нужны данные; порядковый номер элемента)

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

Получается следующая конструкция:

=ИНДЕКС(диапазон, из которого нужны данные; ПОИСКПОЗ (что ищем; где ищем ; 0))

Оформление

Нужно оформить ячейки в книге Excel в едином стиле? Для этого есть одноименный инструмент — «Стили».

На ленте инструментов нажмите на «Стили ячеек» и выберите подходящий. Он будет применен к выделенным ячейкам:

А самое главное — если вы применили стиль ко многим ячейкам (например, ко всем заголовкам на 20 листах книги Excel) и захотели что-то переделать, щелкните правой кнопкой мыши и нажмите «Изменить». Изменения будут применены ко всем нужным ячейкам в документе.

На курсе «Магия Excel» будет два модуля — для новичков и продвинутых. Записывайтесь→

Фото на обложке отсюда

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

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



10 самых важных формул Excel




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

Автозаполнение данных в Excel

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

Например, введите в ячейку какое-либо наименование вместе с его номером, после чего захватите курсором мыши край правого нижнего угла (курсор должен изменить вид) и потяните его вниз (или в сторону) на столько ячеек, на сколько необходимо использовать автоматическое заполнение. Скажем, если вы ввели значение «Запись №1», то последующие ячейки будут заполнены аналогичным значением с изменением нумерации на единичку.

Если же необходимо, чтобы значение порядкового номера менялось с другим шагом, то заполните две ячейки (например, «Запись №1» и «Запись №3»), затем выделите их обе и вновь захватите курсором мыши край нижнего правого угла выделенной области и потяните вниз. В таком случае следующая клетка заполнится значением «Запись №5», затем «Запись №7» и так далее.

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

Автозаполнение формул Excel

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

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

Управление данными при помощи Сводной таблицы

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

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

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



Как найти бесплатные видео по Microsoft Office 2016




Как найти бесплатные видео по Microsoft Office 2016
Компания Microsoft регулярно выпускает пособия для объяснения новых возможностей своего пакета Office, в том числе и на русском языке.
Подробнее

Использование колонтитулов в Word

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

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

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

Если же вам нужны разные колонтитулы в разных частях документа, то просто воспользуйтесь опцией разделения документа на разделы (через ленточное меню «Макет» — «Разрывы») и для каждого из них настройте свой колонтитул.

Управление макетом Word

Наконец, еще один необходимый инструмент Microsoft Office, с которым должен быть знаком каждый пользователь, это настройки макета документа. Прежде всего сюда относится нумерация страниц. Самое необходимое находится во вкладке «Вставка» — в уже знакомой нам области «Колонтитулы». По нажатию на кнопку «Номер страницы» открывается дополнительный список с вариантами вставки. При этом для каждого из вариантов – вверху, внизу, на полях – предлагаются наглядные шаблоны.

Нумерация страниц в Word




Нумерация страниц в Word
Подробный разбор вариантов вставки и настройки нумерации страниц в документах Microsoft Word.
Подробнее

Другие особенности внешнего вида страницы попали на вкладку меню «Макет». Здесь можно изменить поля страницы, ориентацию – альбомную или книжную, размер страницы, вставить колонки, разрывы в документе и многое другое.

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