Первый шаг при перемещении в Excel

Обновлено: 24.11.2024

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

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

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

Создание полосы прокрутки в Excel

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

Что-то вроде этого показано ниже:

Шаги по созданию полосы прокрутки в Excel

Первый шаг – привести данные в порядок. Для целей этого поста я использовал данные переписи 2001 года по 28 индийским штатам с указанием их площади и населения.
Перейдите на вкладку «Разработчик» –> «Вставка» –> «Полоса прокрутки» (управление формой).Если вы не можете найти вкладку «Разработчик» на ленте, значит, она не включена. По умолчанию он скрыт в Excel. Сначала необходимо добавить на ленту вкладку разработчика.
Нажмите кнопку полосы прокрутки (управление формой) и щелкните в любом месте рабочего листа. Это добавит полосу прокрутки на лист Excel.
Щелкните правой кнопкой мыши полосу прокрутки и выберите «Управление форматом». Откроется диалоговое окно «Управление форматом».
В диалоговом окне "Управление форматом" перейдите на вкладку "Управление" и внесите следующие изменения:
- Текущее значение: 1
- Минимальное значение: 1
- Максимальное значение: 19 (здесь 19, поскольку мы отображаем 10 строк за раз. Поэтому, когда пользователь устанавливает значение полосы прокрутки 19, отображаются строки 19–28)
- Дополнительное изменение: 1
- Смена страницы: 5
- Ссылка на ячейку: $L$3
  $L$3 — это ячейка, связанная с полосой прокрутки в Excel. Его значение варьируется от 1 до 19. Это значение ячейки, которое мы используем для создания прокручиваемого списка. Не волнуйтесь, если это не имеет смысла на данный момент. Просто продолжайте читать, и все станет ясно!!
Измените размер полосы прокрутки так, чтобы она соответствовала длине 10 столбцов (это просто для того, чтобы придать ей хороший вид, как показано на рисунке ниже).
Теперь введите следующую формулу в первую ячейку (H4), а затем скопируйте ее, чтобы заполнить все остальные ячейки:
=OFFSET(C3,$L$3,0)

Обратите внимание, что эта формула OFFSET зависит от ячейки L3, которая связана с полосой прокрутки.

Теперь у вас все готово для полосы прокрутки в Excel.

Как это работает?

Формула OFFSET использует ячейку C3 в качестве опорной ячейки и смещает ее на значения, указанные в ячейке L3. Поскольку L3 связан со значением полосы прокрутки, когда значение полосы прокрутки становится равным 1, формула ссылается на имя первого состояния. Когда он становится равным 2, это относится ко второму состоянию.

Кроме того, поскольку ячейка C3 не заблокирована, во второй строке формула принимает вид =СМЕЩ(C4,$L$3,0) и работает так же.

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

При записи макроса средство записи макросов записывает все шаги кода Visual Basic для приложений (VBA). Эти шаги могут включать ввод текста или чисел, выбор ячеек или команд на ленте или в меню, форматирование ячеек, строк или столбцов или даже импорт данных из внешнего источника, например, Microsoft Access. Приложение Visual Basic (VBA) — это подмножество мощного языка программирования Visual Basic, которое входит в состав большинства приложений Office. Хотя VBA дает вам возможность автоматизировать процессы внутри и между приложениями Office, вам не обязательно знать код VBA или компьютерное программирование, если Macro Recorder делает то, что вам нужно.

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

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

Записать макрос

Есть несколько полезных вещей, которые вам следует знать о макросах:

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

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

Не обязательно, чтобы в макросе можно было записывать только задачи в Excel. Процесс макроса можно распространить на другие приложения Office и любые другие приложения, поддерживающие приложение Visual Basic (VBA). Например, вы можете записать макрос, в котором вы сначала обновите таблицу в Excel, а затем откроете Outlook, чтобы отправить таблицу по электронной почте на адрес электронной почты.

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

На вкладке "Разработчик" в группе "Код" нажмите "Записать макрос".

Нажмите клавиши ALT+T+M+R.

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

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

Чтобы назначить сочетание клавиш для запуска макроса, в поле "Сочетание клавиш" введите любую букву (подойдет как прописная, так и строчная), которую вы хотите использовать. Лучше всего использовать комбинации клавиш Ctrl + Shift (верхний регистр), поскольку сочетание клавиш макроса переопределяет любое эквивалентное сочетание клавиш Excel по умолчанию, когда открыта рабочая книга, содержащая макрос. Например, если вы используете Ctrl+Z (Отменить), вы потеряете возможность Отменить в этом экземпляре Excel.

В списке "Сохранить макрос в" выберите, где вы хотите сохранить макрос.

Как правило, вы сохраняете свой макрос в папке "Эта книга", но если вы хотите, чтобы макрос был доступен при каждом использовании Excel, выберите "Личная книга макросов" . Когда вы выбираете Личная книга макросов, Excel создает скрытую личную книгу макросов (Personal.xlsb), если она еще не существует, и сохраняет макрос в этой книге.

В поле "Описание" при желании введите краткое описание того, что делает макрос.

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

Нажмите "ОК", чтобы начать запись.

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

На вкладке "Разработчик" в группе "Код" нажмите "Остановить запись" .

Нажмите клавиши ALT+T+M+R.

Работа с записанными макросами в Excel

На вкладке "Разработчик" нажмите "Макросы", чтобы просмотреть макросы, связанные с книгой. Или нажмите Alt+F8. Откроется диалоговое окно «Макрос».

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

Дополнительная информация о работе с макросами в Excel.

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

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

Для редактирования макросов, прикрепленных к книге, используется редактор Visual Basic.

Если книга содержит макрос VBA, который вы хотели бы использовать в другом месте, вы можете скопировать модуль, содержащий этот макрос, в другую открытую книгу с помощью редактора Microsoft Visual Basic.

Назначить макрос объекту, фигуре или графике

На рабочем листе щелкните правой кнопкой мыши объект, графику, фигуру или элемент, которому вы хотите назначить существующий макрос, а затем нажмите "Назначить макрос".

В поле "Назначить макрос" щелкните макрос, который вы хотите назначить.

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

Вы можете назначать макросы формам и элементам управления ActiveX на листе.

Узнайте, как включить или отключить макросы в файлах Office.

Откройте редактор Visual Basic

Нажмите Alt+F11

Узнайте, как найти справку по элементам Visual Basic.

Работа с записанным кодом в редакторе Visual Basic (VBE)

Вы можете работать с записанным кодом в редакторе Visual Basic (VBE) и добавлять свои собственные переменные, управляющие структуры и другой код, который не может записать средство записи макросов. Поскольку средство записи макросов фиксирует почти каждый шаг, который вы делаете во время записи, вы также можете очистить любой ненужный записанный код, который не используется в макросе. Просмотр записанного кода — отличный способ изучить или отточить свои навыки программирования VBA.

Дополнительную информацию об изменении записанного кода в примере см. в разделе Начало работы с VBA в Excel.

Записать макрос

Есть несколько полезных вещей, которые вам следует знать о макросах:

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

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

Выберите Excel > Настройки… > Лента и панель инструментов.

В категории "Настройка ленты" в списке "Основные вкладки" установите флажок "Разработчик" и нажмите "Сохранить".

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

На вкладке "Разработчик" нажмите "Записать макрос".

В списке "Сохранить макрос в" выберите, где вы хотите сохранить макрос.

Как правило, вы сохраняете свой макрос в папке «Эта книга», но если вы хотите, чтобы макрос был доступен при каждом использовании Excel, выберите «Личная книга макросов». Когда вы выбираете Личная книга макросов, Excel создает скрытую личную книгу макросов (PERSONAL.XLSB), если она еще не существует, и сохраняет макрос в этой книге. Книги в этой папке открываются автоматически при каждом запуске Excel, и любой код, который вы сохранили в личной книге макросов, будет указан в диалоговом окне «Макрос», которое объясняется в следующем разделе.

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

В поле "Описание" при желании введите краткое описание того, что делает макрос.

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

Нажмите "ОК", чтобы начать запись.

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

На вкладке "Разработчик" нажмите "Остановить запись".

Работа с записанными макросами в Excel

На вкладке "Разработчик" нажмите "Макросы", чтобы просмотреть макросы, связанные с книгой. Откроется диалоговое окно «Макрос».

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

Дополнительная информация о работе с макросами в Excel.

Узнайте, как включить или отключить макросы в Excel для Mac.

Назначить макрос объекту, фигуре или графике

В поле "Назначить макрос" щелкните макрос, который вы хотите назначить.

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

Вы можете назначать макросы формам и элементам управления ActiveX на листе.

Откройте редактор Visual Basic

На вкладке "Разработчик" выберите Visual Basic или выберите меню "Сервис" > "Макрос" > "Редактор Visual Basic...".

Узнайте, как найти справку по элементам Visual Basic.

Нужна дополнительная помощь?

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

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

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

Нажмите «Файл», а затем «Создать».

В разделе "Создать" щелкните пустую книгу.

Нажмите на пустую ячейку.

Например, ячейка A1 на новом листе. На ячейки ссылаются по их расположению в строке и столбце на листе, поэтому ячейка A1 находится в первой строке столбца A.

Введите текст или число в ячейку.

Нажмите Enter или Tab, чтобы перейти к следующей ячейке.

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

На вкладке "Главная" в группе "Шрифт" нажмите стрелку рядом с элементом "Границы", а затем выберите нужный стиль границы.

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

На вкладке "Главная" в группе "Шрифт" щелкните стрелку рядом с пунктом "Цвет заливки", а затем в разделе "Цвета темы" или "Стандартные цвета" выберите нужный цвет.

Дополнительную информацию о том, как применить форматирование к рабочему листу, см. в разделе Форматирование рабочего листа.

После того, как вы ввели числа в свой лист, вы можете сложить их. Быстрый способ сделать это — использовать автосумму.

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

Перейдите на вкладку "Главная", а затем нажмите "Автосумма" в группе "Редактирование".

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

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

Выберите ячейку и введите знак равенства (=).

Это сообщает Excel, что эта ячейка будет содержать формулу.

Введите комбинацию чисел и операторов вычисления, например знак плюс (+) для сложения, знак минус (-) для вычитания, звездочку (*) для умножения или косую черту (/) для деления.< /p>

Например, введите =2+4, =4-2, =2*4 или =4/2.

Выполняется расчет.

Вы также можете нажать Ctrl+Enter, если хотите, чтобы курсор оставался в активной ячейке.

Чтобы различать разные типы чисел, добавьте формат, например валюту, проценты или даты.

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

Перейдите на вкладку "Главная", а затем нажмите на стрелку в поле "Общие".

Выберите числовой формат.

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

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

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

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

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

Нажмите стрелку в заголовке столбца таблицы.

Чтобы отфильтровать данные, снимите флажок "Выбрать все", а затем выберите данные, которые хотите отобразить в таблице.

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

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

Пример анализа данных

Советы по анализу данных

Перед использованием функции сортировки или сводных таблиц данные необходимо «очистить». Это означает, что первым шагом в анализе данных является просмотр данных и обеспечение согласованности стиля ввода данных в столбцах. В этом случае для диагнозов важно убедиться, что для описания каждого диагноза используется только одно слово, фраза или аббревиатура. Если для описания одного и того же явления используется несколько слов — например, если «аллергия» записано в базе данных как «аллергия», «аллергия» и «аллергия» — анализ будет более сложным, поэтому лучше выбрать один термин и использовать его последовательно. Возможно, потребуется изменить терминологию, используемую в наборе данных, чтобы обеспечить единообразие во всем; такие изменения следует вносить на этом предварительном этапе. Если имеется несколько диагнозов для одного субъекта, важно перечислить диагнозы отдельно. Может потребоваться создать дополнительные столбцы с пометками «Диагноз 2» и «Диагноз 3», указав по одному диагнозу в каждом столбце. (В этом случае можно создать несколько сводных таблиц и вручную сложить результаты вместе.)

Использование функции сортировки в Excel

В Excel 2016 для Windows сначала выберите данные («Control-A» выбирает все). В верхней части панели инструментов Excel выберите вкладку «Данные». Затем щелкните функцию сортировки (обведена синим). В появившемся окне нажмите «Сортировать по диагнозу». Чтобы снова отсортировать по полу, нажмите кнопку в верхнем левом углу окна с надписью «Добавить уровень». Затем нажмите «Пол» и кнопку «ОК». (См. рисунок ниже.)

Сортировка — отличный инструмент для выявления тенденций и анализа небольших объемов данных. В приведенном выше примере после сортировки данных по диагнозу, а затем по полу, просто подсчитайте количество людей с каждым диагнозом и запишите разбивку по полу либо вручную, либо с помощью формулы Excel «СЧЁТЕСЛИМН». Чтобы использовать формулу для подсчета числа женщин с диагнозом аллергии, выберите пустую ячейку и введите «=СЧЁТЕСЛИМН», а затем диапазон и критерии. В этом примере первый диапазон — D2:D23, а первый критерий — «Аллергия». Второй диапазон — C2:C23, а второй критерий — «F». Правильные формульные обозначения см. на рисунке ниже.

Нажмите «Ввод», и в ячейке появится количество лиц женского пола с диагнозом «аллергия».

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

Использование сводных таблиц в Excel

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

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

Чтобы затем определить количество людей с каждым диагнозом, перетащите «Диагноз» (тот, рядом с которым установлен флажок) в поле с заголовком «Значения». Это должно выглядеть так:

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

Кроме того, чтобы отсортировать сначала по полу, а затем по диагнозу, поменяйте местами "Пол" и "Диагноз" в поле "Строки".

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

Создание отображения данных

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

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

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

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

Важность сообщения обо всех результатах

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

Свяжитесь с нами

Unite For Sight является некоммерческой организацией 501(c)(3).

Читайте также: