Которые позволяют оптимизировать работу в MS Excel. А сегодня хотим предложить вашему вниманию новую порцию советов для ускорения действий в этой программе. О них расскажет Николай Павлов - автор проекта «Планета Excel», меняющего представление людей о том, что на самом деле можно сделать с помощью этой замечательной программы и всего пакета Office. Николай является IT-тренером, разработчиком и экспертом по продуктам Microsoft Office, Microsoft Office Master, Microsoft Most Valuable Professional. Вот проверенные им лично приёмы для ускоренной работы в Excel. ↓
Быстрое добавление новых данных в диаграмму
Если для вашей уже построенной диаграммы на листе появились новые данные, которые нужно добавить, то можно просто выделить диапазон с новой информацией, скопировать его (Ctrl + C) и потом вставить прямо в диаграмму (Ctrl + V).
Эта функция появилась только в последней версии Excel 2013, но она стоит того, чтобы обновиться до новой версии досрочно. Предположим, что у вас есть список полных ФИО (Иванов Иван Иванович), которые вам надо превратить в сокращённые (Иванов И. И.). Чтобы выполнить такое преобразование, нужно просто начать писать желаемый текст в соседнем столбце вручную. На второй или третьей строке Excel попытается предугадать наши действия и выполнит дальнейшую обработку автоматически. Останется только нажать клавишу Enter для подтверждения, и все имена будут преобразованы мгновенно.
Подобным образом можно извлекать имена из email’ов, склеивать ФИО из фрагментов и т. д.
Копирование без нарушения форматов
Вы, скорее всего, знаете про «волшебный» маркер автозаполнения - тонкий чёрный крест в правом нижнем углу ячейки, потянув за который можно скопировать содержимое ячейки или формулу сразу на несколько ячеек. Однако есть один неприятный нюанс: такое копирование часто нарушает дизайн таблицы, т. к. копируется не только формула, но и формат ячейки. Этого можно избежать, если сразу после протягивания чёрным крестом нажать на смарт-тег - специальный значок, появляющийся в правом нижнем углу скопированной области.
Если выбрать опцию «Копировать только значения» (Fill Without Formatting), то Microsoft 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.
Если этот подробный обзор охватил не все полезные фишки MS Excel, о которых вы знаете, делитесь ими в комментариях!
Макросы позволяют существенно расширить возможности в программе Excel. Они автоматизируют рабочие процессы и берут большую часть рутинной работы пользователя на себя. Просто нужно научится пользоваться макросами и производительность труда возрастет в десятки раз!
Вам даже не нужно быть программистом и знать язык программирования «VBA» чтобы создавать свои макро-программы с помощью инструмента записи макросов.
Сначала надо включить панель разработчика. Для этого в меню «Файл» открываем группу опций «Параметры». В появившемся окне «Параметры Excel» открываем группу «Настройка ленты». Обратите внимание на правую колонку настроек под аналогичным названием «Настройка ленты». В ней следует отметить галочкой опцию «Разработчик» как показано ниже на рисунке:
Теперь нам доступна на ленте новая закладка «Разработчик» со всеми своими инструментами для автоматизации работы в Excel и создания макросов.
Применение VBA и макросов в Microsoft Excel
Макросы – это внутренние приложения, которые берут на себя всю рутинную работу, облегчая жизнь пользователю. Каждый пользователь может создать макрос без знания языков программирования. Для этого существует макрорекодер, который запускается с помощью кнопки «Запись макроса».
В этом режиме все действия пользователя макрорекодер в Excel записывает, переводя на язык программирования VBA-код в автоматическом режиме. После завершения записи мы получаем готовую программу, которая сама выполняет те действия, которые выполнял пользователь при записи.
Как записать готовый макрос в Excel? Очень просто:
С помощью макропрограмм можно увеличить производительность труда пользователя в десятки раз. Но чтобы использовать запись пользовательских макросов на все 100% следует соблюдать простые правила, которые существенно влияют на их качество в момент записи и эффективность при выполнении.
Как работать с макросами в Excel
5 простых советов, которые помогут в создании макросов без программирования. Воспользуйтесь этими простыми советами, которые позволяют быстро и просто создавать качественные макропрограммы автоматически:
1 Правильные имена в макросах.
Присваивайте макросам короткие, но содержательные имена. Когда вы войдете вкус, со временем вам придется создавать много макросов. При выборе в диалоговом окне их легче найти с короткими и содержательными названиями. Система VBA предоставляет вам возможность указать описание к имени. Обязательно используйте ее.
Имя макроса обязательно должно начинаться с букв и не может содержать пробелы, символы или знаки препинания. После первого символа, вы можете использовать больше букв, цифр или нижнее подчеркивание, но максимальная длина имени составляет 80 символов.
2 Используйте относительные (не абсолютные) адреса ячеек
Абсолютный адрес ячейки – это точное местонахождение курсора, когда информация о его размещении записывается в макро-адреса с жесткой привязкой к конкретной ячейке в момент записи. Абсолютные адреса ограничивают возможности макроса, если будут добавляться / удаляться данные на листе Excel или список данных будет становиться больше. Относительные средства не привязывают курсор к конкретному адресу ячейки.
По умолчанию в Excel включен режим «Абсолют», но вы можете изменить его, включив кнопку «Относительные ссылки» расположенную ниже под кнопкой «Запись макроса» на панели инструментов вкладки «Разработчик»:
3 Всегда начинайте запись с курсором в A1
Абсолютный отсчет ячеек, всегда ведется с исходного положения (адрес ячейки А1) – до адреса курсора с вашими данными. Если вы сохранили ваш макрос в книге личных макросов (рекомендуется так и делать), то вы можете использовать свою программу на других листах с аналогичными данными. Независимо от того, где ваш курсор позиционируется, когда вы начинаете запись макроса! Даже если он уже находится в ячейке A1, ваш первый макрос лучше записывать после нажатия клавиш должны быть Ctrl + Home.
Пример: Представьте себе, что каждый месяц вы получаете десятки таблиц из всех филиалов. От вас требуется организовать данные и рассчитать показатели, чтобы произвести еще один ежемесячный отчет. Вы можете записать макрос для выполнения всех этих функций, в том числе открытие и соединение всех листов в одну комбинированную таблицу.
4 Всегда перемещаться с клавиш направления в момент записи макроса
Используйте кнопки со стрелками для управления курсором (Ctrl + Up, и т.п.). Позиционируйте курсор, так чтобы вы могли добавить, изменить или удалить данные внутри таблицы по мере необходимости.
Использование мыши для навигации является более сложным и не так надежным в момент записи. Когда дело доходит до макросов, использовать мышь лучше только для вызова меню.
5 Создавайте макросы для конкретных небольших задач
Держите ваши макросы для небольших специфичных задач. Чем больше программный код в макросе, тем медленнее он работает, особенно если это требуется для выполнения многих функций или рассчитать много формул в большой электронной таблице.
Если вы запустите каждый процесс отдельно, вы можете быстро просмотреть результаты для проверки точности их выполнения.
Если нельзя разбить длинный макрос на короткие приложения, а требуется проверить его функциональность пошагово («отладкой»). Нажмите клавишу F8 каждый раз, когда вы хотите перейти к следующему шагу выполнения задачи. Процесс выполнения программы останавливается, когда он видит ошибку. Вы можете исправить ошибку, которую легко найти с помощью «отладки» или записать по-новому.
Эту программу можно использовать в следующих целях:
· Бухгалтерский учет. Программа имеет достаточно мощные функции для вычисления финансовой и бухгалтерской отчетности.
· Составление бюджета. В данной программе можно составлять различные виды бюджета для деловых и личных потребностей, например, можно составить маркетинговый бюджетный план.
· Отчеты. В этой программе можно создавать отчеты для анализа и суммирования данных, например, отчеты, измеряющие эффективность проекта.
· Планирование. Программа является отличным средством для создания профессиональных планов.
· Использование календарей. Рабочая область программы имеет вид сетки, поэтому ее можно использовать для создания различных типов календарей, например,календаря финансового года для Отслеживания деловых мероприятий и выдающихся для бизнеса событий.
Окно программы состоит из:
· Панели вкладок (лента), которая расположена сверху окна программы. На ленте расположены определенные наборы команд (функции программы), которые собраны в команды и группы (по умолчанию отображена лента,содержащая 7 вкладок, при запуске программы активной является вкладка «Главная»). При переходе к новым объектам работы (диаграммы формулы),появляются новые вкладки.
· Над панелью вкладок находится панель быстрого доступа, которая включает в себя наиболее используемые команды (по умолчанию - панель содержит кнопку сохранения данных, отмены и повторения действия). Правее находится пиктограмма, нажав по которой левой кнопкой мышки, вы можете открыть список дополнительных задач. Там вы можете выбрать кнопки которые будут отображаться на панели быстрого доступа.
· Ниже ленты вкладок расположены строка имени и строка формул. В строке имени будет отображаться имя активного объекта или ячейки, а в строке формул будет отображаться формула активной ячейки.
Строка имени.
Строка формул.
· Основную часть окна занимает рабочее поле.
· В правом нижнем окне программы находятся команды, которые служат для наиболее выгодного просмотра документа.
Книга в Excel 2010
Книга состоит из:
3 листов - это по стандарту, максимально 255 листов. Каждый лист состоит из ячеек, которые образуются пересечением строк и столбцов. Строки пронумерованы арабскими цифрами (1,2,3), а столбцы большими английскими буквами (А, B, C).
Редактирование таблиц в Excel 2010
Для того, чтобы изменить ширину столбца или высоту строки,нужно вставить между буквами с названиями столбцов или цифрами названиями строк и протянуть в нужном направлении.
Чтобы задать правило заполнение ячеек, нужно ввести данные в 2 смежные ячейки,выделить их и, с помощью маркера заполнения,который находится в нижнем правом углу, протянуть на нужное количество.
Создания формул в Excel 2010
Формула начинается со знака «= », адреса ячеек записываются только на английском языке. Для того, чтобы не записывать адрес ячейки, можно просто при написании формулы выделить ее левой кнопкой мышки.
Как построить функцию в Excel 2010 (как сделать расчеты в Excel 2010)
Microsoft Excel – чрезвычайно полезная программка в разных областях. Готовая таблица с возможностью автозаполнения, быстрых расчетов и вычислений, построения графиков, диаграмм, создания отчетов или анализов и т.д.
Инструменты табличного процессора могут значительно облегчить труд специалистов из многих отраслей. Представленная ниже информация – азы работы в Эксель для чайников. Освоив данную статью, Вы приобретете базовые навыки, с которых начинается любая работа в Excel.
Инструкция по работе в Excel
Книга Excel состоит из листов. Лист – рабочая область в окне. Его элементы:
Чтобы добавить значение в ячейку, щелкаем по ней левой кнопкой мыши. Вводим с клавиатуры текст или цифры. Жмем Enter.
Значения могут быть числовыми, текстовыми, денежными, процентными и т.д. Чтобы установить/сменить формат, щелкаем по ячейке правой кнопкой мыши, выбираем «Формат ячеек». Или жмем комбинацию горячих клавиш CTRL+1.
Для числовых форматов можно назначить количество десятичных знаков.
Примечание. Чтобы быстро установить числовой формат для ячейки - нажмите комбинацию горячих клавиш CTRL+SHIFT+1.
Для форматов «Дата» и «Время» Excel предлагает несколько вариантов изображения значений.
Отредактируем значение ячеек:
Для удаления значения ячейки используется кнопка Delete.
Чтобы переместить ячейку со значением, выделяем ее, нажимаем кнопку с ножницами («вырезать»). Или жмем комбинацию CTRL+X. Вокруг ячейки появляется пунктирная линия. Выделенный фрагмент остается в буфере обмена.
Ставим курсор в другом месте рабочего поля и нажимаем «Вставить» или комбинацию CTRL+V.
Таким же способом можно перемещать несколько ячеек сразу. На этот же лист, на другой лист, в другую книгу.
Чтобы переместить несколько ячеек, их нужно выделить:
- Ставим курсор в крайнюю верхнюю ячейку слева.
- Нажимаем Shift, удерживаем и с помощью стрелок на клавиатуре добиваемся выделения всего диапазона.
Чтобы выделить столбец, нажимаем на его имя (латинскую букву). Для выделения строки – на цифру.
Для изменения размеров строк или столбцов передвигаем границы (курсор в этом случае принимает вид крестика, поперечная перекладина которого имеет на концах стрелочки).
Чтобы значение поместилось в ячейке, столбец можно расширить автоматически: щелкнуть по правой границе 2 раза.
Чтобы стало красивее, границу столбца Е немного подвинем, текст выровняем по центру относительно вертикали и горизонтали.
Объединим несколько ячеек: выделим их и нажмем кнопку «Объединить и поместить в центре».
В Excel доступна функция автозаполнения. Вводим в ячейку А2 слово «январь». Программа распознает формат даты – остальные месяцы заполнит автоматически.
Цепляем правый нижний угол ячейки со значением «январь» и тянем по строке.
Апробируем функцию автозаполнения на числовых значениях. Ставим в ячейку А3 «1», в А4 – «2». Выделяем две ячейки, «цепляем» мышью маркер автозаполнения и тянем вниз.
Если мы выделим только одну ячейку с числом и протянем ее вниз, то это число «размножиться».
Чтобы скопировать столбец на соседний, выделяем этот столбец, «цепляем» маркер автозаполнения и тянем в сторону.
Таким же способом можно копировать строки.
Удалим столбец: выделим его – правой кнопкой мыши – «Удалить». Или нажав комбинацию горячих клавиш: CTRL+"-"(минус).
Чтобы вставить столбец, выделяем соседний справа (столбец всегда вставляется слева), нажимаем правую кнопку мыши – «Вставить» - «Столбец». Комбинация: CTRL+SHIFT+"="
Чтобы вставить строку, выделяем соседнюю снизу. Комбинация клавиш: SHIFT+ПРОБЕЛ чтобы выделить строку и нажимаем правую кнопку мыши – «Вставить» - «Строку» (CTRL+SHIFT+"=")(строка всегда вставляется сверху).
Как работать в Excel: формулы и функции для чайников
Чтобы программа воспринимала вводимую в ячейку информацию как формулу, ставим знак «=». Например, = (2+3)*5. После нажатия «ВВОД» Excel считает результат.
Последовательность вычисления такая же, как в математике.
Формула может содержать не только числовые значения, но и ссылки на ячейки со значениями. К примеру, =(A1+B1)*5, где А1 и В1 – ссылки на ячейки.
Чтобы скопировать формулу на другие ячейки, необходимо «зацепить» маркер автозаполнения в ячейке с формулой и протянуть вниз (в сторону – если копируем в ячейки строки).
При копировании формулы с относительными ссылками на ячейки Excel меняет константы в зависимости от адреса текущей ячейки (столбца).
В каждой ячейке столбца С второе слагаемое в скобках – 3 (ссылка на ячейку В1 постоянна, неизменна).
Значительно расширяют функционал программы встроенные функции. Чтобы вставить функцию, нужно нажать кнопку fx (или комбинацию клавиш SHIFT+F3). Откроется окно вида:
Чтобы не листать большой список функций, нужно сначала выбрать категорию.
Когда функция выбрана, нажимаем ОК. Откроется окно «Аргументы функции».
Функции распознают и числовые значения, и ссылки на ячейки. Чтобы поставить в поле аргумента ссылку, нужно щелкнуть по ячейке.
Excel распознает и другой способ введения функции. Ставим в ячейку знак «=» и начинаем вводить название функции. Уже после первых символов появится список возможных вариантов. Если навести курсор на какой-либо из них, раскроется подсказка.
Дважды щелкаем по нужной функции – становится доступным порядок заполнения аргументов. Чтобы завершить введение аргументов, нужно закрыть скобку и нажать Enter.
ВВОД – программа нашла квадратный корень из числа 40.
Если вы никогда раньше не использовали табличный процессор для создания документов, советуем прочитать наше руководство Эксель (Excel) для чайников.
После этого вы сможете создать свой первый табличный лист с таблицами, графиками, математическими формулами и форматированием.
Подробная информация о базовых функциях и возможностях табличного процессора .
Описание основных элементов документа и инструкция для работы с ними в нашем материале.
Кстати, чтобы эффективнее работать с таблицами Exel можете ознакомиться с нашим материалом .
Работа с ячейками. Заполнение и форматирование
Прежде чем приступать к конкретным действиям, необходимо разобраться с базовым элементом любого .
Файл Эксель состоит из одного или нескольких листов, разграфленных на мелкие ячейки.
Ячейка – это базовый компонент любого экселевского отчета, таблицы или графика. В каждой клеточке содержится один блок информации. Это может быть число, дата, денежная сумма, единица измерения или другой формат данных.
Чтобы заполнить ячейку, достаточно просто кликнуть по ней указателем и ввести нужную информацию.
Чтобы отредактировать ранее заполненную ячейку, нажмите на неё двойным кликом мышки.
Рис. 1 – пример заполнения ячеек
Каждая клеточка на листе имеет свой уникальный адрес. Таким образом, с ней можно проводить расчеты или другие операции.
При нажатии на ячейку в верхней части окна появится поле с ее адресом, названием и формулой (если клеточка учуствует в каких-либо расчетах).
Выберем ячейку «Доля акций» . Адрес её расположения – А3. Эта информация указана в открывшейся панели свойств. Также мы можем увидеть содержимое.
Формул у этой клетки нет, поэтому они не показываются.
Больше свойств ячейки и функций, которые можно задействовать по отношению к ней, доступны в контекстном меню.
Кликните на клеточку правой клавишей манипулятора.
Откроется меню, с помощью которого можно отформатировать ячейку, провести анализ содержимого, присвоить другое значение и прочие действия.
Рис. 2 – контекстное меню ячейки и ее основные свойства
Сортировка данных
Часто пользователи сталкиваются с задачей сортировки данных на листе в Эксель. Эта функция помогает быстро выбрать и просмотреть только нужные данные из всей таблицы.
Перед вами уже (как её создать разберемся дальше в статье). Представьте, что вам нужно отсортировать данные за январь по возрастанию.
Как бы вы это сделали? Банальное перепечатывание таблицы – это лишняя работа, к тому же, если она объемная, делать этого никто не будет.
Для сортировки в Excel есть специально отведенная функция. От пользователя требуется только:
- Выделить таблицу или блок информации;
- Открыть кладку «Данные» ;
- Кликнуть на иконку «Сортировка» ;
Рис. 3 – вкладка «Данные»
- В открывшемся окошке выберите колонку таблицы, над которой будем проводить действия (Январь ).
- Далее тип сортировки (мы выполняем группировку по значению) и, наконец, порядок – по возрастанию.
- Подтвердите действие, нажав на «ОК» .
Рис. 4 – настройка параметров сортировки
Произойдет автоматическая сортировка данных:
Рис. 5 – результат сортировки цифр в столбце «Январь»
Аналогичным образом можно проводить сортировку по цвету, шрифту и другим параметрам.
Математические расчеты
Главное преимущество Excel – возможность автоматического проведения расчетов в процессе заполнения таблицы. К примеру, у нас есть две ячейки со значениями 2 и 17. Как в третью ячейку вписать их результат, не делая расчеты самостоятельно?
Для этого, вам необходимо кликнуть на третью ячейку, в которую будет вписан конечный результат вычислений.
Затем нажмите на значок функции f(x), как показано на рисунке ниже.
В открывшемся окошке выберите действие, которое хотите применить. СУММ – это сумма, СРЗНАЧ – среднее значение и так далее.
Полный список функций и их наименований в редакторе Excel можно найти на официальном сайте компании Microsoft.
Нам нужно найти сумму двух ячеек, поэтому нажимаем на «СУММ» .
Рис. 6 – выбор функции «СУММ»
В окне аргументов функции есть два поля: «Число 1» и «Число 2» . Выберите первое поле и кликните мышкой на ячейку с цифрой «2».
Её адрес запишется в строку аргумента.
Кликните на «Число 2» и нажмите на ячейку с цифрой «17». Затем подтвердите действие и закройте окно.
Если необходимо выполнить математические действия с тремя или большим количеством клеточек, просто продолжайте вводить значения аргументов в поля «Число 3» , «Число 4» и так далее.
Если в дальнейшем значение суммируемых ячеек будет изменяться, их сумма будет обновляться автоматически.
Рис. 7 – результат выполнения подсчетов
Создание таблиц
В экселевских таблицах можно хранить любые данные.
С помощью функции быстрой настройки и форматирования, в редакторе очень просто организовать систему контроля личного бюджета, список расходов, цифровые данные для отчетности и прочее.
Имеют преимущество перед аналогичной опцией в и других офисных программах.
Здесь у вас есть возможность создать таблицу любой размерности. Данные заполняются легко. Присутствует панель функций для редактирования содержимого.
К тому же, готовую таблицу можно интегрировать в файл docx с помощью обычной функции копирование-вставка.
Чтобы создать таблицу, следуйте инструкции:
- Откройте вкладку «Вставка» . В левой части панели опций выберите пункт «Таблица» . Если вам необходимо провести сведение каких-либо данных, выбирайте пункт «Сводная таблица»;
- С помощью мышки выделите место на листе, которое будет отведено для таблицы. А также вы можете ввести расположение данных в окно создания элемента;
- Нажмите ОК , чтобы подтвердить действие.
Рис. 8 – создание стандартной таблицы
Чтобы отформатировать внешний вид получившейся таблички, откройте содержимое конструктора и в поле «Стиль» кликните на понравившийся шаблон.
При желании, можно создать собственный вид с другой цветовой гаммой и выделением ячеек.
Рис. 9 – форматирование таблицы
Результат заполнения таблицы данными:
Рис. 10 – заполненная таблица
Для каждой ячейки таблицы также можно настроить тип данных, форматирование и режим отображения информации. Окно конструктора вмещает в себя все необходимые опции для дальнейшей конфигурации таблички, исходя из ваших требований.
