Хитрости эксель для продвинутых

Хитрости эксель для продвинутых

Здравствуйте!

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

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

Итак, что же это за возможности:

  1. Зафиксированная ячейка – позволит создать абсолютную ссылку и при сдвиге формулы обеспечит стабильный и точный результат.
  2. Нумерованный список – быстрое создание автоматической и динамической нумерации вашего списка для удобства в работе.
  3. Транспонация значений – это возможность перекидывать ваши данные со строки в столбец и наоборот.
  4. Скрыть или свернуть столбцы и строки – возможность скрыть или свернуть данные вашей таблицы для удобства использования их в работе.
  5. Выпадающий фиксированный список – возможность закрепления за ячейкой или диапазоном ячеек чётко определённые значение и их количество.
  6. Горячи клавши в Excel – список комбинаций клавиш, позволяющие вам производить вам мгновенные операции в программе Excel.
  7. Печать выделенного диапазона – это возможность, которая позволяет в один клик мышки задать вам необходимую область таблицы которую необходимо распечатать.
  8. Настройка панели быстрого доступа – позволит вам создать то меню функций, которые идеально вам подходит для работы с Excel и создаётся вами исключительно для вас.
  9. Сводная таблица – создание интеллектуальной и полуавтоматической таблицы для упорядочивания данных, удобства визуализации информации и вычисления согласно заданных критериев.
  10. Сцепить/отцепить значения в ячейках – позволит вам соединять и разъединять значения и аргументы в том объёме который вам необходим для работы.

А на этом у меня всё! Я очень надеюсь, что все 10 секретов Excel вам пригодятся в работе и станут незаменимыми и удобными. Буду очень благодарен за оставленные комментарии, так как это показатель читаемости и вдохновляет на написание новых статей! Делитесь с друзьями прочитанным и ставьте лайк!

Не забудьте поблагодарить автора!

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

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

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

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

Читайте также:  Как называется геометрическая фигура состоящая из множества

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Как установить защиту на все листы книги?

В MS Excel нет команды, которая бы устанавливала защиту сразу на все листы рабочей книги. Поэтому я сделал такую команду в надстройке ЁXCEL .

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

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

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

Автор проекта «Планета Excel», разработчик и IT-тренер.

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

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

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

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

Читайте также:  Интернет аптека как открыть

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

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

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

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

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

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

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

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

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

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

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

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

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

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

8. Умная таблица

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

  1. Автоматически растягивается при дописывании к ней новых строк или столбцов.
  2. Введённые формулы автоматом будут копироваться на весь столбец.
  3. Шапка такой таблицы автоматически закрепляется при прокрутке, и в ней включаются кнопки фильтра для отбора и сортировки.
  4. На появившейся вкладке «Конструктор» (Design) в такую таблицу можно добавить строку итогов с автоматическим вычислением.
Читайте также:  Usb vid 0b05 pid 17cb rev 0112

9. Спарклайны

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

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

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

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

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

В Excel 2013 путь немного другой: «Файл» → «Сведения» → «Управление версиями» → «Восстановить несохранённые книги» (File — Properties — Recover Unsaved Workbooks).

В последующих версиях Excel следует открывать «Файл» → «Сведения» → «Управление книгой».

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

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

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

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

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

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

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

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

Ссылка на основную публикацию
Формат записи видео mov
MOV против MP4 Существует много форматов файлов, которые можно использовать для хранения ваших видео в зависимости от ваших потребностей. MOV...
Усилитель pioneer a 405r
Вероятно, госпожа Симметрия владела умами дизайнеров Pioneer, когда они разрабатывали внешний вид этой серии усилителей. Но, расположив в центре регулятор...
Усилитель амфитон у 002 характеристики
усилитель Амфитон -002 . Доработан по статье Жуковского '' Оверклоккинг Амфитона . '' и по рекомендациям Вова мастер звук. T.е....
Формат ммгг как писать
Сбербанк Онлайн позволяет проводить различные платежи прямо из дома с любого устройства, имеющего доступ в Интернет. Это существенно экономит время...
Adblock detector