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

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

Эта функция точно есть в Excel 2013 года. К примеру, в списке с полными ФИО нужно сократить имена и отчества. Например: из Алексеев Алексей Алексеевич в Алексеев А. А. Для этого в соседнем столбце необходимо прописать 2-3 строчки таких сокращения вручную, а дальше программа предложит автоматически повторить действия с оставшимися данными – потребуется просто нажать enter.

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

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

1. Выделить область
2. Скопировать данные
3. Правой кнопкой мыши нажать на ячейку, куда должны переместиться данные, в появившемся окне выбрать значок «Транспонировать»

В старых версиях Excel такого значка нет, зато эта команда выполняется с помощью нажатия комбинации клавиш ctrl + alt + V и выбора функции «транспонировать» . И такими несложными движениями мыши в руке столбцы и строки меняются местами.

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

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

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

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

Спарклайны

1. Нажать «Вставка»
2. Открыть «Спарклайны»
3. Выбрать команду «График» или «Гистограмма»
4. В открывшемся окне указать диапазон с числами и ячейки, в которых должны появиться спарклайны, основанные на этих числах

Макросы

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

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

Прогнозы

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

1. Указать не меньше 2 ячеек с исходными данными
2. Открыть «Данные» – «Прогноз» – «Лист прогноза»
3. В окошке «Создание листа прогноза» кликнуть на график или гистограмму
4. В области «Завершение прогноза» установить дату окончания, нажать «Создать»

Заполнить пустые ячейки списка

Это избавит от монотонного ввода одинаковых фраз во множество ячеек. Конечно, можно воспользоваться старым добрым копипастом (копировать ctrl+c, вставить ctrl+v), но, если нужно заполнить не 10 ячеек, а, например, сотню-другую, и местами текст будет разным, – следующая подсказке точно пригодится.

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

Дальше выделяется столбец с нашими днями недели, во вкладке «Главная» нажимаются кнопки «Найти и выделить» , «Выделить группу ячеек» , «Пустые ячейки» . Потом в первой пустой ячейке нужно поставить знак «=» , стрелкой «вверх» на клавиатуре вернуться к заполненной ячейке (на примере в таблице – «понедельник»). Нажать ctrl+enter . Готово, теперь все пустые ячейки должны заполниться продублированными данными.

Найти ошибки в формуле

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

Часть формулы вычисляется прямо в строке формул. Для этого необходимый участок нужно выделить и нажать F9. Все просто, но есть одно «но». Если забыть вернуть все на место, то есть отменить вычисление функции, и нажать enter – посчитанная часть останется в виде числа.

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

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

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

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

Копирование с сохранением форматов

Наверняка с маркером автозаполнения знакомы все. Это маленький черный крестик в правом углу ячейки, который позволяет копировать содержимое или формулу из одной ячейки в несколько других, если потянуть его вниз или в сторону. Так вот, в этом случае копирование может нарушить «внешний вид» таблицы, ведь копируется еще и формат ячейки. Если потянуть за крест и нажать смарт-тег (значок появится внизу справа в скопированном диапазоне), формат не скопируется. Другой вариант – опция «Копировать только значения» , в этом случае дизайн таблицы тоже не будет испорчен.

Удалить пустые ячейки

Быстро расправиться со всеми ненужными пустыми ячейками очень просто:

1. Выделить столбец
2. Вкладка «данные»
3. Нажать «фильтр»

Над столбцами появится стрелка, на которую нужно нажать, а затем в открывшемся меню под цифрами 1-9 поставить галочку напротив пункта «Пустые ячейки» .

Найти отличия и совпадения двух областей

Если нужно быстро найти одинаковые или разные данные в 2-х списках, эту работу Excel проделает сам. Несколько кликов – и программа выделит схожие или отличные элементы:

Необходимо выделить списки (при этом зажать ctrl). Во вкладке «Главная» перейти к кнопке «Условное форматирование» . Дальше нажать «Правила выделения ячеек» – «Повторяющиеся значения» или «Уникальные» . Готово.

Быстрый подбор значений

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

1. Открыть «Данные» – «Работа с данными» – «Анализ «что если» – «Подбор параметра»
2. В область «Установить в ячейке» вставить ссылку на ячейку с нужной формулой
3. В области «Значение» написать нужный результат формулы
4. В области «Изменяя значение ячейки» вставить ссылку на ячейку с корректируемым значением и нажать Ок

Быстрое перемещение

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

Быстро добавить новые значения в диаграмму

Новые данные в уже готовую диаграмму можно поместить просто с помощью обычного копипаста: выделить необходимые значения, скопировать их (ctrl+c ) и вставить в диаграмму (ctrl+v ).

Расширенный поиск

Комбинация ctrl + f , как все знают, ведет в меню поиска, который найдет любую информацию в этой программе. Функция имеет несколько секретов – знаки «?» и «*» включат в поиске настоящего сыщика: с их помощью можно отыскать данные, если нет уверенности в точности запроса. Знак вопроса заменит один неизвестный символ, а астериск (этот знак в простонародье называют звездочкой) – сразу несколько неизвестных.

Когда в куче данных нужно найти именно эти знаки – перед ними ставят значок «~» . Тогда программа не примет их за неизвестные символы.

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

На случай наступления конца света.

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

MS Excel 2010. Последовательность команд такая: «Файл» - «Последние» - (кнопка внизу справа).

MS Excel 2013. «Файл» - «Сведения» - «Управление версиями» - «Восстановить несохраненные книги» . И программа откроет тайный мир всех временных копий книг, которые создавались или изменялись, но не были сохранены.

Горячие комбинации клавиш

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

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

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

Тормоза программ пакета MS Office дело распространенное. Excel тормозит из-за целого ряда причин: неправильное использование функций программы (лишнее условное форматирование, сводные таблицы основанные на огромных массивах, лишние строки в документах). Excel тоже не идеален и тормоза происходят из-за не совершенства программной части (появляющиеся объекты, папка printerSettings в структуре). Поэтому рекомендуем вам пройтись по всем 10 шагам ниже и не останавливаться на одном.

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

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

2. Убрать излишнее условное форматирование (если Excel тормозит при прокрутке)

Что такое условное форматирование, читайте . Очень полезная штука, если правильно применять, если нет, то это заставит Ваш файл работать медленно.

Дело в том, что при копирование ячеек, копируется и форматирование — если в файл постоянно копируются новая информация, то в ней сохраняются условия формата, в особенности условного форматирования.

Убираем так. Выбираете лист. Выберите нужный диапазон или весь лист. В верхней панели Главная — Условное форматирование — Удалить правила — Удалить правила из выделенных ячеек/с листа. Жмем.

3. Удалить лишние строки/столбцы (если ползунок прокрутки очень маленький)

Самая частая проблема для excel с которой я встречаюсь. Если кто-то случайно переместился на конец листа (на строку номер 1 млн) и так сохранил книгу. Размер файла сразу увеличился. Дойти до конца таблицы можно и случайно — если нажать сочетание Ctrl + стрелка вниз или вправо. Бывает, что в конце книги находится какой-нибудь случайный символ или заливка.

Главный признак тормозов — это размер ползунка, он очень маленький, когда файл сохранен неверно, как на картинке.

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

4. Удалить лишние объекты

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

Чтобы удалить такие объекты , нажмите Alt + F11 и копируйте текст ниже.

Sub DelOb() For Each i In ActiveSheet.Shapes i.Delete Next end sub

Или выделите и удалите объекты вручную. Перейдите в меню Главная — Редактирование — Найти и выделить — пункт Выделение группы ячеек — Объекты. Теперь удаляйте.

5. Удалить лишние данные в структуре файла Excel

Даже опытные пользователи не знают, что файл Excel, как говорит Википедия , это файл-архив. Начиная с 2007 выпуска.

Т.е. файл Excel открывается, к примеру, архиваторами 7-zip или WinRar. Внутри открытого файла могут храниться ненужные файлы, что тормозит Excel порой в десятки раз.

Удалим неудобство? Сперва сделайте резервную копию файла:) Затем запустите 7-zip или другой архиватор, меню «Файл» — «Открыть внутри». Возможно открыть файл кликнув правой кнопкой мыши — Открыть с помощью и выбрав.exe файл WinRar или 7-zip.

Откроется архив, он же файл Excel c папками и файлам.

Находим папки «drawings» и/или «printerSettings» (скорее всего они будут в папке xl) и удаляем их.

Для WinRar делаем тоже самое.

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

Будьте внимательны, если в вашем файле есть нарисованные кнопки или другие фигуры, то удалять всю папку drawings — значит и удалять полезные фигуры.

Поэтому, в папке, удалите только файлы vmlDrawing.vml, они могут накапливать информацию и весить до 100 мб.

6. Тормозит Excel — правильно настройте сводные таблицы

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

Что позволит уменьшить файл почти в два раза.

7. Измените формат файла на.xlsb

Если вы работаете с огромными таблицами и ваши файлы больше 0,5 мб весом, то лучше сохранять такие книги в формате . Двоичный формат книги Excel, т.е. специальный формат для создания «базы данных» на основе электронных таблиц. Если сохранить большой файл в таком формате, вес книги уменьшится в два-три раза. Расчеты в файл тоже будут проходить быстрее, в некоторых случаях в 2 раза быстрее.

8. Установлен неопознанный принтер

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

Бывает что даже при удалении настроек принтера из пункта 5 настройки принтера тормозят файл.

9. Удалите файл PERSONAL

Зайдите в …Application Data\Microsoft\Excel\XLSTART или …Microsoft Office\Office12 найдите в этих папках файл под название PERSONAL и удалите его. Подробнее читайте .

10. Замените много созданных формул макросами

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

11. Уменьшите размер рисунков

Самый простой способ для версии 2007 и выше — выбрать рисунок, на верхней панели появиться вкладка Работа с рисунками — Формат.

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

12. Перейдите на поздний Excel — 2013 или 2016

Разработчики нашего любимого редактора действительно не останавливаются в развитии продукта. Проведя ряд экспериментов, я выявил, что большинство сложных операций таких как расчет ячеек, выполнение макроса, сохранение и другие в версии 2016 выполняются быстрее ~на 15% от 2007.

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

Так что еще один хороший способ ускорить работу в Excel — перейти на MS Office 2013 и выше.

Теперь попробуйте сами.

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

Логичное продолжение статьи — оптимизация ресурсов компьютера под использование Excel — читайте в .

Если ничего не помогает пишите комментарии, постараемся помочь.

Поделитесь нашей статьей в ваших соцсетях:

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

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

Мазок, ещё мазок… Проходит несколько часов и… Пару небольших табличек и график с стандартным оформлением. Грустно не так ли?

И у меня такое бывало. Работаешь, работаешь. Уже вымотался, а работа только начата. Что же с этим делать?

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

Интересно? Тогда поехали!

Первое что нужно сделать — выкинуть мышку!

Именно так вы всё верно прочитали. Чтобы работать быстрее в Excel нужно убрать мышку. ТОЛЬКО не спешите закрывать вкладку, сейчас всё станет понятно.

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

Всё дело в горячих клавишах которые позволяют просто молниеносно переключаться между страницами, выделять, добавлять или удалять стройки/столбцы. В общем знание буквально 5-7 комбинаций ускоряет работу на порядок. Многие навигационные операции с помощью мыши делаются в 3-5, а то и 10 раз дольше.

Приведу пример. Чтобы добавить несколько строк в Excel есть три варианта:

1. Вариант 1. Взять мышь, навести её на соответствующий столбец или несколько столбцов в зависимости того сколько столбцов нужно вставить. Нажать правой кнопкой, в выпадающем окошке выбрать вставить.
2. Вариант 2. С помощью кнопок на вкладке Главная в группе ячейки. Опять же выделяем, идем на вкладку Главная. Щелкаем по соответствующим кнопкам для получения результата.
3. Вариант 3. Для выделения столбца нажать Ctrl + ‘Пробел’ , для добавления столбца нажать Ctrl + ‘+’ .

Вы можете сказать, что экономия не велика. Одним способом я сделаю за 15-20 секунд, другим за 3-5. И что я буду делать с освободившимися 12-15 секундами? И вообще сколько нужно выучить горячих клавиш чтобы почувствовать разницу?

Согласен! Время, сэкономленное одной горячей клавишей не велико, но вся наша работа в Excel — это монотонное повторение одних и тех же операций по сотне раз в разных последовательностях. Значит если условная экономия на одной операции предположим 10 секунд. При повторении одной операции 20-30 раз в день экономия от знания одной комбинации горячих клавиш — 3-5 минут. Значит знание 10 горячих клавиш экономит 30-50 минут времени!!! А вот это уже круто!!!

Совет номер два вытекает из первого — учите горячие клавиши. Полезных горячих клавиш не так уж и много, поэтому выучив 10-20 сочетаний вы быстро почувствуете разницу в скорости работы.

«Хорошенький совет!» — возразите вы. — «И как их учить? Становиться на табуретку перед коллегами и рассказывать как стихи?» 🙂

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

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

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

Горячие клавиши уж точно помогут вам рабоатать быстрее в Excel. Трениуртесь в их использовании и очень быстро скорость вашей работы существенно возрастёт. Но это ещё не всё!

У меня в запасе есть ещё несколько советов. Готовы? Поехали!

Совет по Excel номер ТРИ. Функции и их комбинации, которые обязатлеьно освоить

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

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

Вместо формулы

=СУММ(Лист1!А1; Лист2!А1; Лист3!А1; Лист4!А1; Лист5!А1; Лист6!А1; Лист7!А1; … ЛистN!A1)

Формула будет выглядеть как

=СУММ(Лист1:ЛистN!А1)

Всё это к чему? Помимо богатого функционала Excel, который есть в его стандартных формулах, существует множество комбинаций, знание которых позволяет решать и не стандартные задачи. Так, у Excel совсем нет функции МИНЕСЛИ. Да, есть СУММЕСЛИ, СЧЁТЕСЛИ, а МИНЕСЛИ не сделали. Также МАКСЕСЛИ, МЕДИАНАЕСЛИ и т.п., но при этом всё это решается путем использования функций областей. Возможно вы видели, когда формула облачается в фигурные скобки.

Некоторые функции отлично работают только в связке. Это я сейчас говорю о ИНДЕКС и ПОИСКПОЗ. Казалось бы, бестолковые функции по отдельности, но в паре дают отличный функционал.

Что для этого нужно? Например, подписаться на нашу группу в Facebook и ждать выхода новых постов. 😉

Также посмотрите список формул, на которые я рекомендую вам обратить внимание:

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

Совет по Excel номер ЧЕТЫРЕ. Структура и форматирование в файлах

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

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

Как этого можно избежать? Дам несколько советов:

• Отформатируйте файл. Пусть у вас будет одинаковое количество знаков после запятой, одинаковый шрифт по всему документу и ограниченная цветовая палитра. Мне вот нравится использовать палитры одного цвета но разных тонов (голубой, синий и тёмно-синий). Смотрится очень стильно.
• Разделите исходные данные, расчеты и результаты. Не всегда это нужно, но когда данных становится очень много наличие сводной таблички просто спасает.
• Делайте одинаковые формулы по столбцу или строке. согласитесь не очень правильно когда один и тот же показатель в разные периоды считается по разному. При этом обычно этого не видно пока не заглянешь в формулу. а често можно ещё и забыть.
• Старайтесь избегать циклических ссылок и ссылок на внешние файлы. Обычно именно они дают разный результат на разных компьютерах. Был случай, когда погрешность при обновлении внешних ссылок была больше чем с 7 нулями. Ай!
• Упрощайте и не эконономьте место. Excel позволяет сделать сколь угодно много строк и столбцов. За свои 10 лет работы с программой я никогда не использовал лист полностью. Вряд ли у вас это получится, поэтому не городите сложные формулы. Лучше сделайте расчет в несколько действий.

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

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

Хотелось бы дать интенсив на 28 минут, после которого все будут знать Excel лучше чем знают его разработчики, но увы. Такого не бывает. Постоянная и усердная работа приводит к поистине впечатляющим результатам. Именно поэтому совсем скоро мы запускаем тренинг по Excel, который можно будет пройти на нашем сайте совершенно бесплатно (по крайней мере первым 1000 пользователям).

Павлов Николай

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

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

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

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

Копирование без повреждения форматирования

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

Начиная с Excel 2002, есть решение этой проблемы - простое и изящное. Сразу после копирования (протаскивания) формулы на весь столбец, нужно воспользоваться смарт-тегом - небольшим значком, который временно появляется в правом нижнем углу диапазона. Нажатие на него выведет список возможных вариантов копирования, где и можно выбрать Копировать только значения (Fill without formatting). В этом случае формулы копируются, а форматирование - нет:

Копирование только видимых ячеек

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

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

Можно решить проблему, кропотливо выделяя каждую строку итогов и удерживая при этом клавишу CTRL - как для выделения несмежных диапазонов. Но если таких строк не три-пять, а несколько сотен или тысяч? Есть другой, более быстрый и удобный путь:

Выделите копируемый диапазон (в нашем примере - это A1:C29)

Нажмите на клавиатуре клавишу F5 и затем кнопку Выделить (Special) в открывшемся окне.
Появится окно, позволяющее пользователю выделять не все подряд, а только нужные ячейки:

В этом окне выберите опцию Только видимые ячейки (Visible cells only) и нажмите ОК.

Полученное выделение теперь можно смело копировать и вставлять. В результате мы получим копию именно видимых ячеек и вставим вместо ненужных 29-ти только необходимые нам 5 строк.

Если есть подозрение, что подобную операцию вам придется проделывать часто, то имеет смысл добавить на панель инструментов Microsoft Excel кнопку для быстрого вызова такой функции. Это можно сделать через меню Сервис>Настройка (Tools> Customize), затем перейти на вкладку Команды (Commands), в категории Правка (Edit) найти кнопку Выделить видимые ячейки (Select visible cells) и перенести ее мышью на панель инструментов:

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

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

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

Щелкаем правой кнопкой мыши по пустой ячейке и выбираем команду Специальная вставка (Paste Special)

В открывшемся окне ставим флаг Транспонировать (Transpose) и жмем ОК:

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

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

Другой путь - простой, быстрый и красивый - выделить ячейки с новыми данными, скопировать их (CTRL+C) и вставить (CTRL+V) прямо в диаграмму. Excel 2003, в отличие от более поздних версий, поддерживает даже возможность перетаскивания выделенного диапазона ячеек с данными и забрасывания его прямо в диаграмму с помощью мыши!

Если хочется контролировать все нюансы и тонкости, то можно использовать не обычную, а специальную вставку, выбрав в меню Правка>Специальная вставка (Edit> Paste Special). В этом случае Microsoft Excel отобразит диалоговое окно, позволяющее настроить куда и как именно будут добавлены новые данные:

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

Заполнение пустых ячеек

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

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

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

Выделите все ячейки в столбце с пустотами (т.е. диапазон A1:A12 в нашем случае)

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

Установите переключатель в положение Пустые (Blank) и нажмите ОК. Теперь в выделении должны остаться только пустые ячейки:

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

Чтобы ввести созданную формулу сразу во все выделенные пустые ячейки, нажмите не клавишу ENTER, а сочетание CTRL+ ENTER. Формула заполнит все пустые ячейки:

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

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

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

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

Выбор товара из прайс-листа, имени клиента из клиентской базы, ФИО сотрудника из штатного расписания и т.д. Вариантов применения этой функции множество.

Чтобы создать выпадающий список в ячейке:

Выделите ячейки, в которых вы хотите создать выпадающий список.

Если у вас Excel 2003 или старше, то выберите в меню Данные>Проверка (Data>Validation). Если у вас Excel 2007/2010, то перейдите на вкладку Данные (Data) и нажмите кнопку Проверка данных (Data validation).

В открывшемся окне выберите вариант Список (List) из раскрывающегося списка.

В поле Источник (Source) надо указать значения, которые должны быть в списке. Тут возможны варианты:

Вписать в это поле текстовые варианты через точку с запятой

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

Если он находится на другом листе этой книги, то ему придется заранее дать имя (выделить ячейки, нажать CTRL+F3, ввести имя диапазона без пробелов), а затем прописать это имя в поле

September 16th, 2017

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

Сегодня хотим предложить вашему вниманию новую порцию советов для ускорения действий в этой программе. О них расскажет Николай Павлов — автор проекта «Планета Excel», меняющего представление людей о том, что на самом деле можно сделать с помощью этой замечательной программы и всего пакета Office. Николай является IT-тренером, разработчиком и экспертом по продуктам Microsoft Office, Microsoft Office Master, Microsoft Most Valuable Professional. Вот проверенные им лично приёмы для ускоренной работы в Excel. ↓

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

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

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

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

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

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

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

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

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

В последней версии Excel 2013 появилась возможность быстро отобразить на интерактивной карте ваши геоданные, например продажи по городам и т. п. Для этого нужно перейти в «Магазин приложений» (Office Store) на вкладке «Вставка» (Insert) и установить оттуда плагин Bing Maps. Это можно сделать и по прямой ссылке с сайта , нажав кнопку Add. После добавления модуля его можно выбрать в выпадающем списке «Мои приложения» (My Apps) на вкладке «Вставка» (Insert) и поместить на ваш рабочий лист. Останется выделить ваши ячейки с данными и нажать на кнопку Show Locations в модуле карты, чтобы увидеть наши данные на ней.

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

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

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

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

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

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

Ну и еще некоторые более простые полезность про Exel:

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

Выделение всех ячеек одним кликом

Все ячейки можно выделить комбинацией клавиш Ctrl + A, которая, кстати, работает и во всех других программах. Однако есть более простой способ выделения. Нажав на кнопку в углу листа Excel, вы выделите все ячейки одним кликом.

Открытие нескольких файлов одновременно

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

Перемещение по файлам Excel

Когда у вас открыто несколько книг в Excel, между ними можно легко перемещаться с помощью комбинации клавиш Ctrl + Tab. Эта функция также доступна по всей системе Windows, и ее можно использовать во многих приложениях. К примеру, для переключения вкладок в браузере.

Добавление новых кнопок на панель быстрого доступа

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

Перейдите в меню «Файл» ⇒ «Параметры» ⇒ «Панель быстрого доступа». Теперь можно выбрать любые кнопки, которые вам нужны.

Диагональная линия в ячейках

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

Добавление в таблицу пустых строк или столбцов

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

Скоростное копирование и перемещение информации

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

Быстрое удаление пустых ячеек

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

Расширенный поиск

Нажав Ctrl + F, мы попадаем в меню поиска, с помощью которого можно искать любые данные в Excel. Однако его функциональность можно расширить, используя символы «?» и «*». Знак вопроса отвечает за один неизвестный символ, а астериск — за несколько. Их стоит использовать, если вы не уверены, как выглядит искомый запрос.

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

Копирование уникальных записей

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

Создание выборки

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

Быстрая навигация с помощью Ctrl и стрелки

Нажимая Ctrl + стрелка, можно перемещаться в крайние точки листа. К примеру, Ctrl + ⇓ перенесет курсор в нижнюю часть листа.

Транспонирование информации из столбца в строку

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

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

Как скрывать информацию в Excel

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

Объединение текста с помощью «&»

Если вам нужно объединить текст из нескольких ячеек в одну, необязательно использовать сложные формулы. Достаточно выбрать ячейку, в которой будет соединен текст, нажать «=» и последовательно выбрать ячейки, ставя перед каждой символ «&».

Изменение регистра букв

С помощью определенных формул можно менять регистр всей текстовой информации в Excel. Функция «ПРОПИСН» делает все буквы прописными, а «СТРОЧН» — строчными. «ПРОПНАЧ» делает прописной только первую букву в каждом слове.

Внесение информации с нулями в начале

Если вы введете в Excel число 000356, то программа автоматически превратит его в 356. Если вы хотите оставить нули в начале, поставьте перед числом апостроф «’».

Ускорение ввода сложных слов

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

Больше информации

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

Переименование листа с помощью двойного клика

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

А вот еще полезности для вас: вот например , а вот некоторые . Знаете ли вы к тому же