Ms excel (эксель): основные приемы работы и полезные функции

10 профессиональных приемов в Microsoft Excel, которые сделают работу быстрее

Студенты не любят MS Excel. Впрочем, MS Excel тоже не очень любит студентов. А мы знаем, как приручить этого демона и упростить учебу и работу с этим нелегким офисным приложением. Ловите, дорогие студенты: самые полезные функции, а также основные методы и приемы работы в Excel!

Советы и основные приемы работы в Excel

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

Работа с диаграммой в Excel: быстрое добавление новых данных

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

Диаграммы Excel

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

Моментальное заполнение в Excel

Многие приемы в Экселе неизвестны студентам. Мы решили раскрыть вам тайну мгновенного заполнения.

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

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

Списки Excel

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

Точно так же вы можете склеивать из фрагментов ФИО, извлекать имена из email и так далее.

Копирование без нарушения форматов в Excel

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

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

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

Копирование в Excel

Если вы выберите действие Fill Without Formatting (копировать только значения), то программа не будет портить внешний вид оформления, копируя выбранную формулу без формата.

Кстати! Для наших читателей сейчас действует скидка 10% на любой вид работы

Данные из таблицы на геокарте в Excel

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

Геокарты в Excel

Чтобы это сделать, зайдите во вкладку «Вставка», найдите там Магазин приложений и установите там плагин Bing Maps. Добавив модуль, его можно будет потом выбрать во вкладке Вставка/Мои приложения и разместить на рабочий лист.

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

Быстрый переход по листам в Excel

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

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

Превращение строки в столбец и наоборот в Excel

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

Строку в столбец или столбец в строку Excel

Если вы используете старую версию Эксель и там нет такой функции, вопрос легко решается специальной вставкой: Ctrl + Alt + V и выбрать опцию Транспортировать.

«Умная» таблица в Excel

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

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

Умная таблица Excel

Спарклайны в Excel

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

Спарклайн Excel

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

Восстановление несохраненных файлов в Excel

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

Делайте так: жмите  Файл/Последние, в нижнем правом углу экрана ищите кнопку Восстановить несохраненные книги.

Если у вас версия новее 2013 года, действия несколько отличаются: Файл/Сведения/Управления версиями/Восстановить несохраненные книги.

Восстановление несохраненных файлов Excel

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

Сравнение 2-х участков документа на сходства и отличия в Excel

Если вам нужно быстро найти и сравнить элементы, быстрее всего это будет сделать так:

• выделяем 2 сравниваемых столбца с удержанием Ctrl,
• выбираем вкладку Главная/Условное форматирование/Правила выделения ячеек/Повторяющиеся значения,
• выбираем вариант Уникальные, который появится в новом списке.

Сравнение и поиск дублей Excel

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

Источник: https://Zaochnik.ru/blog/poleznye-sovety-10-professionalnyx-priemov-microsoft-excel-kotorye-sdelayut-rabotu-bystree/

12 простых, но эффективных приёмов для ускоренной работы в Excel

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

 О них расскажет Николай Павлов — автор проекта «Планета Excel», меняющего представление людей о том, что на самом деле можно сделать с помощью этой замечательной программы и всего пакета Office.

Николай является IT-тренером, разработчиком и экспертом по продуктам Microsoft Office, Microsoft Office Master, Microsoft Most Valuable Professional. Вот проверенные им лично приёмы для ускоренной работы в Excel. ↓ 

Быстрое добавление новых данных в диаграмму

Если для вашей уже построенной диаграммы на листе появились новые данные, которые нужно добавить, то можно просто выделить диапазон с новой информацией, скопировать его (Ctrl + C) и потом вставить прямо в диаграмму (Ctrl + V).

Мгновенное заполнение (Flash Fill)

Эта функция появилась только в последней версии Excel 2013, но она стоит того, чтобы обновиться до новой версии досрочно. Предположим, что у вас есть список полных ФИО (Иванов Иван Иванович), которые вам надо превратить в сокращённые (Иванов И. И.).

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

Подобным образом можно извлекать имена из email’ов, склеивать ФИО из фрагментов и т. д.

Копирование без нарушения форматов

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

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

Если выбрать опцию «Копировать только значения» (Fill Without Formatting), то Microsoft Excel скопирует вашу формулу без формата и не будет портить оформление.

Отображение данных из таблицы Excel на карте

В последней версии Excel 2013 появилась возможность быстро отобразить на интерактивной карте ваши геоданные, например продажи по городам и т. п. Для этого нужно перейти в «Магазин приложений» (Office Store) на вкладке «Вставка» (Insert) и установить оттуда плагин Bing Maps.

Это можно сделать и по прямой ссылке с сайта, нажав кнопку Add. После добавления модуля его можно выбрать в выпадающем списке «Мои приложения» (My Apps) на вкладке «Вставка» (Insert) и поместить на ваш рабочий лист.

Останется выделить ваши ячейки с данными и нажать на кнопку Show Locations в модуле карты, чтобы увидеть наши данные на ней.

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

Быстрый переход к нужному листу

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

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

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

Преобразование строк в столбцы и обратно

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

1. Выделите диапазон.
2. Скопируйте его (Ctrl + C) или, нажав на правую кнопку мыши, выберите «Копировать» (Copy).
3. Щёлкните правой кнопкой мыши по ячейке, куда хотите вставить данные, и выберите в контекстном меню один из вариантов специальной вставки — значок «Транспонировать» (Transpose).

В старых версиях Excel нет такого значка, но можно решить проблему с помощью специальной вставки (Ctrl + Alt + V) и выбора опции «Транспонировать» (Transpose).

Выпадающий список в ячейке

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

1. Выделите ячейку (или диапазон ячеек), в которых должно быть такое ограничение.
2. Нажмите кнопку «Проверка данных» на вкладке «Данные» (Data — Validation).
3. В выпадающем списке «Тип» (Allow) выберите вариант «Список» (List).
4. В поле «Источник» (Source) задайте диапазон, содержащий эталонные варианты элементов, которые и будут впоследствии выпадать при вводе.

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

«Умная» таблица

Если выделить диапазон с данными и на вкладке «Главная» нажать «Форматировать как таблицу» (Home — Format as Table), то наш список будет преобразован в «умную» таблицу, которая (кроме модной полосатой раскраски) умеет много полезного:

• Автоматически растягиваться при дописывании к ней новых строк или столбцов.
• Введённые формулы автоматом будут копироваться на весь столбец.
• Шапка такой таблицы автоматически закрепляется при прокрутке, и в ней включаются кнопки фильтра для отбора и сортировки.
• На появившейся вкладке «Конструктор» (Design) в такую таблицу можно добавить строку итогов с автоматическим вычислением.

Спарклайны

Спарклайны — это нарисованные прямо в ячейках миниатюрные диаграммы, наглядно отображающие динамику наших данных. Чтобы их создать, нажмите кнопку «График» (Line) или «Гистограмма» (Columns) в группе «Спарклайны» (Sparklines) на вкладке «Вставка» (Insert). В открывшемся окне укажите диапазон с исходными числовыми данными и ячейки, куда вы хотите вывести спарклайны.

После нажатия на кнопку «ОК» Microsoft Excel создаст их в указанных ячейках. На появившейся вкладке «Конструктор» (Design) можно дополнительно настроить их цвет, тип, включить отображение минимальных и максимальных значений и т. д.

Восстановление несохранённых файлов

Пятница. Вечер. Долгожданный конец ударной трудовой недели. Предвкушая отдых, вы закрываете отчёт, с которым возились последнюю половину дня, и в появившемся диалоговом окне «Сохранить изменения в файле?» вдруг зачем-то жмёте «Нет».

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

На самом деле, есть неслабый шанс исправить ситуацию. Если у вас Excel 2010, то нажмите на «Файл» — «Последние» (File — Recent) и найдите в правом нижнем углу экрана кнопку «Восстановить несохранённые книги» (Recover Unsaved Workbooks).

Откроется специальная папка из недр Microsoft Office, куда на такой случай сохраняются временные копии всех созданных или изменённых, но несохранённых книг.

Сравнение двух диапазонов на отличия и совпадения

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

1. Выделите оба сравниваемых столбца (удерживая клавишу Ctrl).
2. Выберите на вкладке «Главная» — «Условное форматирование» — «Правила выделения ячеек» — «Повторяющиеся значения» (Home — Conditional formatting — Highlight Cell Rules — Duplicate Values).
3. Выберите вариант «Уникальные» (Unique) в раскрывающемся списке.

Бонус: другие способы сравнить два диапазона и найти отличия/совпадения.

Подбор (подгонка) результатов расчёта под нужные значения

Вы когда-нибудь подбирали входные значения в вашем расчёте Excel, чтобы получить на выходе нужный результат? В такие моменты чувствуешь себя матёрым артиллеристом, правда? Всего-то пара десятков итераций «недолёт — перелёт», и вот оно, долгожданное «попадание»!

Microsoft Excel сможет сделать такую подгонку за вас, причём быстрее и точнее. Для этого нажмите на вкладке «Вставка» кнопку «Анализ „что если“» и выберите команду «Подбор параметра» (Insert — What If Analysis — Goal Seek).

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

После нажатия на «ОК» Excel выполнит до 100 «выстрелов», чтобы подобрать требуемый вами итог с точностью до 0,001.

Если этот подробный обзор охватил не все полезные фишки MS Excel, о которых вы знаете, делитесь ими в комментариях!

Источник: https://Lifehacker.ru/uskorennaja-rabota-v-excel/

Приемы работы в MS Excel

Как “перевернуть” таблицу?

Суть проблемы заключается обычно в следующем – “нарисовали” таблицу, а она не помещается на листе для распечатывания, слишком много столбцов, а количество строк небольшое. Вот было бы здорово поменять местами столбцы со строками.

Как быстро “протянуть” формулы?

Очень часто наблюдаю такую картину. Человек пытается “растянуть” формулу расположенную, например, в ячейке “E2” на диапазон, скажем, “E3:E5000“. И выглядит это очень грустно: палец на мышке затекает, глаза слезятся от проносящихся по экрану строк. А между тем эту задачу можно решить в один клик мышки.

Как быстро ввести название месяцев или дней недели?

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

Как быстро посчитать итоги в таблице?

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

Как быстро создать нумерованный список?

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

Как изменить текст во множестве ячеек?

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

) написаны как попало, где-то “кг.”, где-то “килограммы”, где-то “КГ”, а где-то и с ошибкой “килограмы”. В общем некая солянка. Для редактирования “руками” список слишком велик, например, 1 000 позиций.

Что же делать?

Как написать текст в ячейке в несколько строк?

Для отображения текста в ячейке в несколько строк в MS Excel есть специальная команда. Но при изменении ширины ячейки текст начинает “скакать” – автоматически меняется количество строк. Что делать если необходимо четко задать количество строк?

Как “красиво” написать дату?

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

Как быстро вставить текущую дату в ячейку?

Иногда просто даешься диву как в такой замечательной программе как MS Excel не предусмотрен стандартный инструмент для вставки дат или времени в ячейку? Если вы часто работаете с датами вы уже, наверное, пальцы стерли. Тем не менее кое-какой инструмент в MS Excel имеется.

Как быстро объединить данные из двух столбцов в один?

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

Как быстро объединить данные из двух таблиц?

Типичная проблема – имеем два списка. Первый список – содержит объем продаж по менеджерам с фамилиями и суммами, другой содержит фамилии менеджеров и отделы к которым они относятся. Как в первый список быстро добавить название отдела, к которому относится менеджер, чтобы посмотреть продажи по отделу?

Как быстро перейти к нужному листу книги?

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

Как быстро удалить дубликаты в списке?

Ну что тут писать в предисловии? Из названия все понятно – есть список, в нем есть дубликаты, нужно получить только уникальные значения. Как? Читайте далее.

Как создать выпадающий список в ячейке?

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

Как создать свой формат данных?

В MS Excel существует бесчисленное множество форматов, в которых можно представить свои числовые данные. Тут вам и даты, и валюты и даже номера телефонов. Но что делать если нам не подходит ни один из предустановленных форматов? Создать свой.

Мои “быстрые” клавиши в Excel

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

“Быстрый” фильтр в MS Excel

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

Чтобы найти ячейки, содержащие тот или иной текст необходимо проделать несколько действий – щелкнуть по фильтру, выбрать пункт “Текстовые фильтры”, выбрать пункт “содержит…”, ввести соответствующий текст и нажать “ОК”.

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

Быстро делаем ABC-Анализ в MS Excel

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

Как быстро вставить необходимую дату в ячейку?

Ну нет в MS Excel календаря вот и приходится пользователям либо вводить даты с клавиатуры, либо создавать свои собственные формы в виде календаря для быстрой вставки дат. В данной статье я предлагаю решение для “ленивых”, для тех, кому лень делать и то, и другое.

Как быстро заменить формулы на значения?

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

Как быстро заполнить пустые ячейки в таблице?

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

Как быстро переименовать листы?

Типичная задача. Есть много листов названия которых нас не устраивают. Мы хотим их переименовать. Делать это вручную очень долго.

Как быстро создать несколько копий листа?

Допустим мы “нарисовали” на листе некую таблицу и нам необходимо продублировать ее на несколько листов, допустим на 10. Простым копированием это делать достаточно лениво. Как можно сделать это быстро?

Как объединить две таблицы и более в одну?

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

Не работает фильтр по дате. Что делать?

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

Генератор примеров (массивы, таблицы, отчеты)

Когда я начинал читать тренинги по MS Excel то “стер” пальцы бесконечно создавая примеры для той или иной темы. Особенно это касалось темы “Сводные таблицы”.

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

Оказалось, что это не только отличный инструмент для тренера, но и такой же отличный инструмент для “студента”.

Как быстро построить сводную таблицу из отчета 1C или SAP?

Суть проблемы, я думаю, ясна всем, кто хоть раз строил Сводные таблицы на основе отчета, полученного из учетной системы. В одном столбце расположены разнотипные данные и Клиент, и Категория товара, и Наименование товара. Значения же, например, объем продаж разбит по нескольким столбцам, по месяцам: Январь в своем столбце, Февраль в своем и так далее.

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

Как быстро преобразовать таблицу в массив для сводной таблицы?

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

Как отфильтровать столбец сводной таблицы?

На тренингах я часто задаю вопрос участникам: Кто знает, как отфильтровать отдельный (не итоговый) столбец сводной таблицы по значениям? И что я слышу в ответ: Это невозможно! В сводных таблицах нет такой возможности! Вопрос настолько древний, что люди уже перепробовали всякие варианты и не найдя решения сделали вывод – невозможно. Хотя решение существует.

Как построить сводную таблицу по нескольким массивам (листам)?

Типичная задача при обработке информации полученной из разных источников. Типовое решение – взять и свести все таблицы в одну. Но что делать, когда таблиц много (например, 20), или свести их в одну нет возможности, на листе просто не хватает строк (все таблицы в сумме дают больше 1 100 000 строк)?

Однако решение существует! И оно не очень сложное.

Как построить сводную таблицу?

Сводная таблица MS Excel – это мощный инструмент анализа данных.

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

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

Как проводить вычисления в сводной таблице?

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

Что такое сводные таблицы?

Приходилось ли вам когда-нибудь попадать в такую ситуацию?:

Вы очень долго “рисовали” сложную таблицу с множеством строк и столбцов и большим содержанием данных, например, таблицу – отчет по продажам в разрезе Подразделений, Городов, Менеджеров, Типов клиентов, с группировкой по кварталам, по количеству и суммам, с вычислением процентов и долей… И когда уже все готово и “раскрашено”, вы показываете эту таблицу, например, руководителю, а он говорит: “Все круто, вот только бы добавить сюда еще разрез по Товарным категориям, вообще было бы замечательно”.

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

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

Как перенести диаграмму на отдельный лист?

Суть проблемы обычно в следующем: построили диаграмму, а для большей наглядности ее необходимо растянуть, но в растянутом виде она не помещается на экране. Что делать? Перенести на отдельный лист.

Как построить “Пузырьковую” диаграмму по сводной таблице?

При попытке построить “Пузырьковую” диаграмму на основе сводной таблицы – вылетает ошибка: “Невозможно изменить тип диаграммы… Выберите другой тип диаграммы”. MS Excel не умеет строить такие диаграммы на основе сводных таблиц. А что делать если очень нужно?

Как построить диаграмму?

Данная статья для начинающих пользователей.

Круговая диаграмма с выноской

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

Построение “Пузырьковой” диаграммы

Для тех, кто сталкивался с этой задачей главный вопрос звучит так: “Какие данные нужны и в каком виде их располагать?”. В данной статье есть ответ на этот вопрос.

Построение диаграммы с разрывом

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

Как вставить готовый макрос в рабочую книгу?

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

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

Как записать макрос не зная языка VBA?

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

Как подключать надстройки к MS Excel?

Надстройки – это файлы MS Excel с расширением .xlam (2007, 2010, 2013, 2016) или .xla (2003), которые содержат макросы и которые пишутся для расширения возможностей MS Excel. В этой статье я расскажу, как подключать такие надстройки и как настроить MS Excel, чтобы надстройки могли свободно функционировать.

Источник: https://e-xcel.ru/index.php/zaklinaniya

Топ 10 самых полезных функций excel

       Добрый день уважаемый пользователь!

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

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

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

1.    Функция СУММ. Самой первой функцией стоит изучить функцию СУММ, без нее просто не обходится ни одно математическое действие в таблицах, просуммировать ячейки, диапазон ячеек или даже множество разбросанных значений в документе, это всё во власти функции СУММ. Также для удобства использования Excel предоставляет вам возможность использования инструмента «автосумма», что еще более упрощает работу, вам стоит нажать одну кнопочку и весь диапазон чисел будет посчитан в одно мгновение от 2 до миллиона значений, увы, на калькуляторе это будет дольше и не гарантирует правильный результат. Функция таит в себе свои хитрости и секреты, которые вам очень пригодятся.
2.    Функция ЕСЛИ. Второй по важности изучения, стоит функция ЕСЛИ. Эта логическая функция позволит вам производит множество логических вычислений по многим условиям. Функция имеет возможность вложения, а это позволит вам работать с вариантами условий. Может быть, вы и испугаетесь некой сложности функции, но не стоит пугаться ее обманчивости – она очень проста и доступна. Максимально полезна она будет для экономистов и любых аналитиков.
3.   Функция СУММЕСЛИ. Третьей важной функцией в моем обзоре станет функция СУММЕСЛИ. Эта функция соединяет математические и логические разделы в одном лице и позволит вам собрать и просуммировать значения со всего диапазона по заданному критерию, а это очень поможет, когда строк и столбцов в таблице великое множество. Конечно, есть альтернативы по получению аналогичного результата, но всё же, все остальные варианты будут сложнее. Функция будет очень полезна и бухгалтерам и экономистам.
4.     Функция ВПР. Четвёртой по счёту рассмотрим функцию ВПР. Эта функция с раздела «Ссылки и массивы», является одной из самых полезных и мощных функций при работе с массивами. Поиск и работа с полученными данными из массива ваших данных будет эффективным при использовании функции ВПР, но у нее есть одно ограничение, она ищет только в вертикальных списках, хотя данные списки используются в 95%, это компенсирует ее недостаток. А если вам нужно горизонтальный поиск, вам поможет функция ГПР. Аналогом этой функции может стать соединение других функций, таких как ПОИСКПОЗ и ИНДЕКС, но о них отдельно. Очень полезная функция для анализа любых финансовых результатов и построений «дашбордов».
5.    Функция СУММЕСЛИМН. Пятой функцией нашего топ списка самых полезных функций Excel станет функция СУММЕСЛИМН. Эта функция может все, что умеет третья функция нашего списка, но только немножко больше, а именно суммировать не по одному критерию, а по многим, всё же 127 поддерживаемых критериев это очень сильно. Не стоит забывать, что для корректной работы со многими критериями и диапазонами необходимо пользоваться абсолютными ссылками. Станет полезной многим бухгалтерам и экономистам при работе с большими объемами данных.
6.      Функция ЕОШИБКА. Эта простая функция, которую я предоставил под номером шесть в моем списке ТОП 10, часто спасала меня и помогала получить результат. Достаточно часто мы можем предугадать, что возникнет та или иная ошибка, а если она возникает в средине вычислений, то ломается вся наша  вычислительная линейка. Эта функция позволит нам проигнорировать ошибку и подставить вместо нее нужный результат, что позволит сделать намного больше полезных и точных вычислений, особенно актуально применение совместно с логическими функциями. Очень-очень полезная функция, особенно для экономистов и аналитиков, так как при их работе частенько приходится работать с ошибками, которые возникают.
7.    Функция ПОИСКПОЗ. Седьмую ступеньку нашей пирамиды занимает функция ПОИСКПОЗ, которая, как и функция ВПР работает с массивами, ищет и возвращает значения согласно заданным критериям. По большому счёту эта функция часто является альтернативой функции ВПР, особенно когда ее совместить в гармоничный симбиоз с функцией ИНДЕКС. В этом случае вы сможете получить ряд преимуществ, как то поиск с левой стороны, поиск значения более чем 255 символов, а также добавлять и удалять столбики в таблицу поиска, а также многое другое.  Пригодится любым специалистам, которые работают с большими объемами информации.
8.    Функция СЧЁТЕСЛИ. На восьмом месте я разместил функцию СЧЁТЕСЛИ, которая совмещает математическое начало и логическое, своеобразное соединение функции СЧЁТ и функции ЕСЛИ. Эта функция самое-то в случае, когда вам нужно будет сосчитать что-либо и где-либо, это могут быть и текстовые значения, и даты, и числовые значения в массивах и многое другое. Несмотря на то, что функция СЧЁТЕСЛИ производит подсчёт только по одному критерию, всё же ее польза большая, да и этого зачастую с головой хватает. Функция в работе достаточно проста и неприхотлива, да и пригодится в работе специалисту любой финансовой специальности.
9.      Функция СУММПРОИЗВ. Предпоследней из списка функций моего топ списка станет функция СУММПРОИЗВ, не стоить думать, что она имеет также последние значение в работе, как раз наоборот, многие из специалистов считают ее одним из первых в работе хороших экономистов. Она отлично работает с массивами данных, несмотря на простоту ее синтаксиса, ее функциональность огромна, и осуществлять поиск и выборку данных с массивов она делает легко, быстро и чётко. Функция станет незаменима в работе для экономических специальностей.
10.      Функция ОКРУГЛ. Ну, вот добрались и до конца нашего списка самых полезных функций в Excel, который предоставлен, функцией ОКРУГЛ, с раздела статистических функций.  Почему именно ее я включил ее, потому что взял во внимание работу бухгалтера, который когда делает расчёт и у него пропадает копейка, это уже личная трагедия и головная боль. Так что, несмотря на ее простоту и непритязательность, ее польза в правильном предоставлении данных станет очень полезной и нужной. Является важной для бухгалтерских вычислений и получения точного результата.

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

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

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

      До встречи на страницах TopExcel.ru.

Статья помогла? Поделись ссылкой с друзьями, твитни или лайкни!

Источник: http://topexcel.ru/top-10-samyx-poleznyx-funkcij-excel/

11 полезных приемов для опытных пользователей Excel

ANTHONY DOMANICO. 11 tricks for Excel power users. PCWorld.

Знание этих функций – от сводных таблиц до Power View – поможет вам влиться в ряды специалистов по электронным таблицам.

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

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

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

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

Вставьте в формулу функцию «ВПР», указав в первом ее аргументе искомое значение, по которому осуществляется связь (1).

Во втором аргументе задайте диапазон ячеек, в которых следует производить выборку (2), в третьем – номер столбца, из которого будут подставляться данные, а в четвертом введите значение ЛОЖЬ, если хотите найти точное соответствие, или ИСТИНА, если нужен ближайший приблизительный вариант (4).

Создание диаграмм

Для создания диаграммы введите в Excel данные с указанием заголовков столбцов (1), выберите на вкладке «Вставка» пункт «Диаграммы» (2) и укажите требуемый тип диаграммы.

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

В версии Excel 2013 присутствует вкладка Рекомендуемые диаграммы, на которой отображаются типы диаграмм, соответствующие введенным вами данным.

Функции «ЕСЛИ» и «ЕСЛИОШИБКА»

«ЕСЛИ» и «ЕСЛИОШИБКА» относятся к числу наиболее популярных функций Excel.

Функция ЕСЛИ позволяет определить условную формулу, которая при выполнении условия вычисляет одно значение, а при его невыполнении другое.

Функция «ЕСЛИОШИБКА» представляет собой частный случай более общей функции «ЕСЛИ». Она возвращает какое-то конкретное значение (или пустое значение), если в процессе вычисления формулы произошла ошибка.

К примеру, при выполнении функции ВПР над другим листом или таблицей, функция «ЕСЛИОШИБКА» может возвращать пустое значение в тех случаях, когда «ВПР» не находит искомого параметра, задаваемого первым аргументом.

Функция «ЕСЛИ» вычисляет результат в зависимости от задаваемого вами условия.

Сводная таблица

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

В меню значений выберите пункт «Параметры полей значений» и в качестве операции укажите «Среднее» (3).

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

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

Сводная диаграмма

Сводные диаграммы обладают чертами как сводных таблиц, так и традиционных диаграмм Excel.

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

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

В Excel 2013 появились «Рекомендуемые сводные диаграммы». Откройте вкладку «Вставка», перейдите в раздел «Диаграммы» и выберите пункт «Рекомендуемые диаграммы». Переместив указатель мыши на выбранный вариант, вы увидите, как он будет выглядеть. Для создания сводной диаграммы вручную нажмите на вкладке «Вставка» кнопку «Сводная диаграмма».

Мгновенное заполнение

Лучшая, пожалуй, новая функция Excel 2013 – «Мгновенное заполнение» – позволяет эффективно решать повседневные задачи, связанные с быстрым переносом нужных блоков информации из смежных ячеек. В прошлом, при работе со столбцом, представленным в формате «Фамилия, Имя», пользователю приходилось извлекать из него имена вручную или искать какие-то очень сложные обходные пути.

Предположим теперь, что тот же самый столбец с фамилиями и именами присутствует в Excel 2013. Достаточно ввести имя первого человека в ближайшую справа ячейку (1) и на вкладке «Главная» выбрать «Заполнить» и «Мгновенное заполнение». Excel автоматически извлечет все прочие имена и заполнит ими ячейки справа от исходных.

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

Быстрый анализ

Новый инструмент быстрого анализа Excel 2013 помогает ускорить создание диаграмм из простых наборов данных. После выделения данных рядом с правым нижним углом выделенной области появляется характерный значок (1). Щелкнув по нему, вы переходите в меню «Быстрого анализа» (2).

В меню представлены инструменты «Форматирования», «Диаграмм», «Итогов», «Таблиц» и «Спарклайнов». Щелкая мышью по этим инструментам, вы сможете увидеть поддерживаемые ими возможности.

Быстрый анализ ускоряет работу с простыми наборами данных.

Power View

Интерактивный инструмент исследования и визуализации данных Power View предназначен для извлечения и анализа больших объемов данных из внешних источников. В Excel 2013 для вызова функции Power View перейдите на вкладку «Вставка» (1) и нажмите кнопку «Отчеты» (2).

Отчеты, созданные с помощью Power View, уже готовы к презентации и поддерживают режимы чтения и полноэкранного представления. Интерактивную их версию можно даже экспортировать в PowerPoint. Руководства по бизнес-анализу, представленные на сайте Microsoft, помогут вам в кратчайшие сроки стать специалистом в этой области.

Режим Power View позволяет создавать интерактивные отчеты готовые к презентации.

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

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

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

Транспонирование столбцов в строки и наоборот

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

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

Функция Специальной вставки позволяет транспонировать столбцы и строки.

Важнейшие комбинации клавиш

Источник: https://www.dgl.ru/articles/11-poleznyh-priemov-dlya-opytnyh-polzovateley-excel_5609.html

Основные приемы работы в программе Microsoft Excel

Приложение 6

Основные  приемы  работы
в  программе  microsoft  excel

В настоящем приложении кратко рассмотрены некоторые правила работы в программе Microsoft Excel. Изложены последовательности действий пользователя ПК.

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

Освоив представленную информацию, начинающий пользователь научится: 1) работать с файлами; 2) вводить расчетные формулы; 3) строить диаграммы; 4) обрабатывать экспериментальные данные.

Замечание. При написании приложения использовались русскоязычные версии операционной системы Microsoft Windows XP, Microsoft Office 2003 и Microsoft Excel 2003.

6.1.  Запуск и закрытие программы Microsoft Excel

1. Нажмите кнопку “Пуск”, чтобы открыть главное меню Windows.

2. В главном меню выберете пункт “Программы”, а затем в появившемся меню – пункт “Microsoft Office” и далее – “Microsoft Office Excel 2003”.

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

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

Для закрытия приложения Excel необходимо выполнить одно из следующих действий:

1.  Выполнить команду “Файл | Выход”.

2.  Нажать с помощью мыши кнопку T в правом верхнем углу окна программы.

3.  На клавиатуре нажать комбинацию клавиш +.

Рис.  6.1.

6.2.  Работа  с  файлами

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

xls” и т. д.

Каждая рабочая книга состоит из листов, которые могут содержать таблицы данных, одну или несколько диаграмм, а также внедренные или связанные объекты других приложений. Каждый лист имеет имя, которое выводится на ярлычке данного листа внизу (рис. 6.1). По умолчанию используются имена листов “Лист1”, “Лист2” и т. д. Отдельный лист с диаграммой называется листом диаграммы.

6.2.1.  Создание новой рабочей книги

При стандартном запуске Excel автоматически создается одна новая рабочая книга (с названием Книга1), рис. 6.1.

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

1.  Нажать кнопку “Создать” на панели инструментов “Стандартная”.

2.  Нажать комбинацию клавиш +.

3.  Выполнить команду “Файл | Создать” и на появившейся вкладке “Создание книги” выбрать пункт “Чистая книга”.

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

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

1) раскрыть меню “Окно” и выбрать в нем имя нужной рабочей книги;

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

6.2.2.  Открытие, сохранение и закрытие рабочих книг

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

После выполнения любого из этих действий на экране откроется диалоговое окно “Открытие документа”, показанное на рис. 6.2.

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

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

При поиске файла следует обращать внимание на установленный тип файлов. Список “Тип файлов” находится в нижней части окна “Открытие документа”. На рис. 6.2. данный список открыт. По умолчанию в Excel установлен тип файлов “Файлы Microsoft Office Excel”, при этом список файлов будет содержать только файлы с расширение “xls”.

Рис.  6.2.

В процессе выполнения работ в лаборатории “Физика низких температур” исходная информация, полученная на лабораторном стенде, передается на ПК в виде файла с расширением “csv”. По этой причине, для открытия исходных файлов необходимо выбрать тип файлов “Все файлы”, см. рис. 6.2.

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

Рис.  6.3.

Для закрытия рабочей книги следует выполнить команду меню “Файл | Закрыть”, либо нажать крестик в правом верхнем углу окна рабочей книги. Если рабочая книга была предварительно сохранена, то окно исчезнет с экрана, в противном случае, на экране появится

Источник: https://vunivere.ru/work40094