Пользователи, которые постоянно работают с Excel, наверняка сталкивались с проблемой копирования данных ячейки и их вставки в другие книги (листы), а именно, проблема в том, что при копировании формул и функций, возникают проблемы смещения, да и форматы ячеек могут мешать. Чтобы это избежать в Excel для этого предусмотрена "Специальная вставка ", и возможно копию вставить как: "Значения ", "Формулы ", "Форматы " и т.д. Вот только на варианты этой вставки почему-то не предусмотрены "горячие клавиши", а без "горячих клавиш" очень замедляется процесс работы. Я решил для себя проблему с помощью макросов и сейчас расскажу как.
Если вам нужно отменить связь, просто нажмите правую кнопку мыши в таблице и выберите команду «Свойства внешних данных». Теперь снимите флажок «Сохранить запрос» и подтвердите операцию. Рисунок 66 - Свойства связанной таблицы. Этот способ импорта в других браузерах недоступен, по крайней мере, без соответствующих надстроек.
Вы увидите очень упрощенный веб-браузер, который позволяет вам выбирать конкретные таблицы для импорта по желтым стрелкам. После этого импорта также вставлена связанная таблица, в которой ссылка может быть отменена, как в предыдущем случае. Рисунок 67 - Простой веб-браузер для указания таблицы для импорта.
Итак, создадим новую книгу, сохраним ее под именем "Вставка.xls" и перейдем в Редактор Visual Basic
(Alt+F11).
Вставим новый модуль "Insert->Module" (как создать макросы, описано
). Теперь добавим туда следующий код:
Sub
ВставитьЗначАнгл()
"Сочетание клавиш: Ctrl+q
ВставитьКак 1
End Sub
Sub
ВставитьЗначРус()
"Сочетание клавиш: Ctrl+й
ВставитьКак 1
End Sub
При импорте данных из текста открывается мастер импорта текста, который должен указать тип делителя данных или задать фиксированную ширину столбца. Рисунок 68 - Обзор элементов управления формы на вкладке «Разработчик». Как только элементы управления доступны, вы можете выбрать и нарисовать их. Как только элемент управления является частью листа, он должен быть связан с ячейками или ячейками. Это можно сделать с помощью свойств объекта, к которым можно получить доступ, щелкнув правой кнопкой мыши на элементе управления и выбрав команду «Формат управления».
Sub
ВставитьФормулаАнгл()
" Сочетание клавиш: Ctrl+e
ВставитьКак 2
End Sub
Sub
ВставитьФормулаРус()
" Сочетание клавиш: Ctrl+у
ВставитьКак 2
End Sub
Private Sub
ВставитьКак(id As
Byte)
If
Application.CutCopyMode = xlCopy Then
"проверка скопированы ячейки или нет
Select Case
id
Case 1
: Selection.PasteSpecial Paste:=xlPasteValues "вставляет в выбранную ячейку значение
Case 2
: Selection.PasteSpecial Paste:=xlPasteFormulas "вставляет в выбранную ячейку формулу
End Select
End If
End Sub
Рисунок 70 - Форматирование элемента управления. Аналогично, макрос может быть присоединен к элементу управления формой. Элементы управления формой могут использоваться как простые параметры ввода для формул или диаграмм. Вы, скорее всего, вводите меньше ошибок.
Первая - группа зарегистрированных шагов, которые часто повторяются. Вы можете записывать их с помощью макросов и просто запускать их в других ситуациях либо с помощью готовой кнопки, либо с помощью сочетания клавиш. Это самый распространенный способ записи простых макросов. Будьте готовы совершить ошибки или лишние дополнительные шаги во время макросъемки. Если это произойдет, вам нужно будет отредактировать макрос или начать его запись снова. Запись макроса может быть приостановлена во время процесса, но важно, особенно для многоступенчатых разработчиков, тщательно подумать о процессе или предварительно изучить его.
Процедуры "ВставкаЗначАнгл
" и "ВставкаЗначРус
" вставляют скопированную ячейку как значение.
Процедуры "ВставкаФормулаАнгл
" и "ВставкаФормулаАнгл
" вставляют как формулы.
Процедура "ВставитьКак (id as byte)"
id - идентификатор команды, указывающий как необходимо вставить (1- Значения, 2 - Формулы, ....)
Эта деятельность занимает вас каждую неделю в течение получаса, она является обычной и может быть безошибочной. Чтобы записать начало значка макрокоманды в правом нижнем углу экрана. Рисунок 72 - Кнопка для активации макросъемщика. Или на вкладке «Вид» в группе команд «Макросы».
Рисунок 73 - Запись макроса на вкладке «Вид». Когда команда активирована, появляется окно, в котором вы можете назвать макрос, назначить комбинацию клавиш вместо сохранения. Для более важных или более сложных макросов рекомендуется четко описать их в комментарии.
В условии If проверятся, есть ли скопированные ячейки(-ка) в приложении Excel (Application.CutCopyMode = xlCopy, для определения "Вырезать " значение xlCut).
Внимание: Если не предусмотреть проверку копирования ячейки, то при отсутствии скопированных ячеек, выполнение макроса завершится ошибкой .
Если есть скопированные ячейки, то Select Case id:
Существуют так называемые действия или события, которые являются просто щелчками мыши. После запуска автоответчика вы записываете каждый шаг. Автоответчик можно отключить в тех же местах, где вы включили его. Это остановит или приостановит запись. Вы можете включить относительные ссылки при записи, а это значит, что макрос будет работать относительно активной позиции ячейки. Во время записи эту функцию можно переключать непрерывно.
Как запустить записанный макрос?
Рисунок 75 - Использование относительных ссылок в макросе. После того как вы записали макрос, обязательно выключите автоответчик. Макросы можно найти в книге, которую вы сохранили при создании, в частности в каталоге модулей, где находятся отдельные макросы.
- если id = 1, то выполняем команду Selection.PasteSpecial Paste:=xlPasteValues (вставка значения)
- если id = 2, то Selection.PasteSpecial Paste:= xlPasteFormulas (вставка формулы)
- и т.д.
Как работает Select Case читаем в статье " ".
Для того чтоб сократить код программы, я вынес весь функционал в отдельную процедуру ВставитьКак , иначе б пришлось в каждой процедуре дублировать один и тот же код.
Рисунок 76 - макрокод, который сохраняет несколько столбцов, добавляет другой столбец, вставляет вычисления и форматирует всю таблицу. Рисунок 77 - Создание модуля и процедур. Поместите процедуру в пустой модуль. Рисунок 78 - Настройка процедуры. Рисунок 79 - Пример макроса, который переключает сетку с помощью сочетания клавиш.
Рисунок 80 - Пример пользовательской функции пользователя. Список пользовательских функций также доступен в окне ввода функций. При работе с макросами или их создании рекомендуется знать уровень безопасности макроса в соответствии с его местоположением, которое устанавливается, например, на вкладке «Разработчик», группе команд «Код», кнопке «Безопасность макроса».
В процедурах "ВставкаЗначАнгл ", "ВставкаЗначРус ", "ВставкаФормулаАнгл " и "ВставкаФормулаАнгл " происходит вызов процедуры ВставитьКак с указанием в ней идентификатора команды 1 или 2.
Закрываем редактор Visual Basic и переходим в окно управления макросами "Сервис->Макрос->Макросы... " (Alt+F8). Мы видим наши четыре макроса:
Рисунок 82 - Защита макросов по местоположению. Рисунок 83 - Сохранение файла макроса. При печати меньших таблиц вы можете не заметить более серьезных проблем. Рисунок 84 - Основные настройки печати. В этом диалоговом окне стоит упомянуть вариант печати только выделения, т.е. если вы обозначаете таблицу из 30 ячеек и вы выбираете здесь «Выбор печати», вы печатаете только то, что вы выбрали.
Если вы хотите печатать большие сборки, вы можете выбрать «Печать всей книги» и распечатать все ваши 40 листов за раз. Если у вас есть отдельное представление о макете печатной страницы, например, вы используете фиксированную область печати, рекомендуется настроить отдельные параметры на вкладке «Макет страницы».
Теперь необходимо каждому макросу присвоить горячую клавишу. Запуск макросов в Excel может осуществляться с помощью комбинации "Ctrl + назначенная клавиша ". Из клавиш, сочетающихся с ctrl, я нашел свободные Q(Й) и E(У). Для удобства в самый раз, нажатие левой рукой(правая с мышей) и клавиши не далеко друг от друга, вот правда сосед опасный, ctrl+w - закрывает книгу.
Рисунок 85 - Макет страницы. Используйте диалоговое окно «Параметры страницы», чтобы подробно отредактировать страницы, заголовки и нижние колонтитулы или листы. Полностью новая, практичная компоновка макета страницы, аналогичная предварительным просмотрам, может использоваться для редактирования перед печатью, с которой она позволяет изменять ячейки, края и доступ к верхним и нижним колонтитулам.
Рисунок 87 - Изменение представлений на обычный, макет страницы и просмотр разрывов страницы. Если вы печатаете большие электронные таблицы, важно иметь хорошую идею в последнем представлении «Показывать разрывы страницы». Этот вид позволяет перетаскивать горизонтальные и вертикальные синие пунктирные линии, которые определяют границы печати.
Выбираем макрос "ВставкаЗначАнгл ", жмем кнопку "Параметры... "
в открывшемся окне, там где "сочетание клавиш", указываем "q ". Жмем "ОК ". Затем присвоим клавишу "й " для "ВставкаЗначРус ".
Для чего такое назначение клавиш?
Чтобы изменить представления и другие команды отображения, перейдите на вкладку «Вид». В отличие от обычной пасты, вы больше контролируете, как вы вставляете содержимое своей ячейки. Специальная паста работает только тогда, когда содержимое ячеек копируется и не разрезается. Чтобы использовать специальную пасту, скопируйте содержимое выбранных ячеек и выберите место, где вы хотите вставить эти значения. Щелкните правой кнопкой мыши, чтобы развернуть контекстное меню.
Здесь вы найдете самые популярные варианты вставки. 
Параметры вставки делятся на три группы: вставки, вставки и другие параметры вставки. Это стандартное универсальное вложение данных. Бывает, что если у вас открыто более одного экземпляра программы, тогда, когда вы используете параметр вставки, даже если вы скопируете формулу, он будет рассматриваться как обычное значение.
Просто в Excel, сочетание CTRL с одной и той же клавишей, но в разных раскладках(RU, EN), почему-то отличаются. Поэтому, для того чтоб вставка произошла при любой раскладке клавиатуры, и сделано по два макроса с присвоенными им клавишами в разной раскладке.
Аналогично присвойте клавиши "e " и "у " процедурам "ВставкаФормулаАнгл " и "ВставкаФормулаАнгл ".
Форматирование формул и чисел
Поэтому в этом случае безопасно использовать эту опцию. Липкое значение сохраняет тот же формат записи.
Сохранять исходное форматирование
Этот метод пасты очень похож на приведенный выше. Он отличается от него тем, что он также копирует такие изменения в внешний вид ячеек, таких как шрифт или фон.Сохранять ширину столбцов источника
Этот метод пасты отличается от вышеизложенного только тем, что, когда скопированная ячейка содержит фрейм, она не вставлена в новую ячейку. Пасты, а также сохранение исходного формата, дополнительно устанавливая ширину ячейки в ширину скопированной ячейки.
Все! Теперь можете опробовать работу макросов.
Для того чтобы макросы работали при запуске Excel во всех книгах я подключил их как "Надстройку". Чтобы это сделать необходимо:
Сохранить нашу книгу "Вставка.xls", в формат надстройки - "Файл->Сохранить как
", в списке тип файла выбрать "Надстройка Microsoft Office Excel (*.xla)
"
Используется для замены столбцов строками. 
Предположим, что ячейка отображает значение 6, но на самом деле это формула = 2 1 3, то для этого типа пасты копируется 6, а не формула = 2 1. Вставьте эту ячейку с этим значением, и она будет сохранена и отображена как 0.
Форматирование чисел и значений
Форматирование значений и источников
Точно так же, как и выше, с той разницей, которая дополнительно копируется среди других шрифтов или цвета фона. В этом случае мы не копируем содержимое ячейки, а только форматируем ее. Копирует ссылку на эту ячейку. В этом случае ни формат, ни графический формат не копируются. Копирует содержимое ячейки вместе с форматом и форматом изображения и вставляет его в качестве изображения. Вы можете выполнять те же операции на каждом другом изображении.Затем, в Excel необходимо открыть окно "Надстройки
" (Сервис->Над
стройки...
)
Щелкнуть "Обзор"
и выбрать сохраненную нами надстройку "Вставка.xla
". В списке должна появится новая надстройка "Вставка", если не стоит галочка, то отметьте ее. Жмем ОК
.
Кнопка «Расширенная вставка»
Как и в случае выше, он создает изображение. Это, однако, связано с ячейкой, которую мы копировали, и поэтому она обновляется каждый раз, когда содержимое этой ячейки изменяется. В дополнение к этим параметрам вы также можете открыть окно специальной вставки.
Здесь вы найдете такие опции, как автоматическое сложение, вычитание, деление или умножение. Кроме того, вы можете скопировать ту же ширину столбца или проверку. Вы должны ввести дату вручную. Просто войдите и готов. Следующие ярлыки позволяют быстро скопировать содержимое ячейки над активной ячейкой. Также хорошо, что активная ячейка после копирования содержимого всегда находится в режиме ввода, поэтому его можно изменить.
А на этом все. Благодарю за внимание. Надеюсь статья для Вас будет полезна.
Горячие клавиши помогают пользователям упростить работу и ускорить ее, заменяя несколько действий одним. Тем более это важно для такой офисной программы, как Microsoft Excel, которая призвана облегчить человеку работу с массивами данных разного рода. Рассмотрим популярные применимые во многих версиях этого приложения (от Excel 2003 до Excel 2013). Причем в приложении Excel 2003 нет комбинаций для работы с лентой - панелью быстрого доступа, поскольку эта возможность появилась только в Excel 2007 и имеется в более поздних версиях. Некоторые из приведенных комбинаций могут не срабатывать для локализованных вариантов рассматриваемого приложения.
А что, если мы поместим текст в ячейку, чтобы переместить курсор на новую строку в этой ячейке? Чтобы отредактировать ячейку вообще, нам нужно перейти в режим редактирования. Обычно мы делаем это двойным щелчком по ячейке. Это не единственный «интересный» функциональный ключ. Практически все они содержат полезные функции. Моими любимыми функциональными клавишами являются.
В конце трех интересных ярлыков для простого форматирования ячеек. Это всего лишь несколько быстрых клавиш, которые мне нужно знать. Чтобы скопировать формулу или значение в другое поле, вам не нужно перетаскивать мышь над рассматриваемой ячейкой. Когда мы хотим скопировать некоторые ячейки, они будут умножаться на функции, которые мы задали для них. Если у нас есть много ячеек, которые мы хотим «заблокировать», процесс может занять очень много.
Наиболее популярные горячие клавиши Excel
С помощью нажатия этих комбинаций клавиш выполняются основные функции программы. При описании горячих клавиш символ «+» используется для того, чтобы показать одновременное нажатие клавиш, стоящих справа и слева от знака «+». Комбинации клавиш указываются в круглых скобках, одновременно могут нажиматься одна, две, три или даже четыре клавиши на клавиатуре. Обычно одной, двумя или тремя из них являются клавиши служебные - Ctrl, Shift или Alt. Их нужно нажимать первыми и, удерживая в нажатом состоянии, нажимать другую (третью или четвертую) из комбинации горячей клавиши.
Первая функция будет подсчитывать, сколько раз определенное значение появляется в выбранном периметре данных. Вторая функция связана с критерием выбора, который вы можете установить. В частности, вы можете сделать так, чтобы ячейки с данными в диапазоне были выделены или текст, который вы выберете, все, что вы выбрали, появится в этих ячейках.
Эта функция выберет значение, которое вы хотите сделать, например, список. Первый раздел, который вы заполните, - это значение, которое вы хотите отслеживать. Второй раздел относится к диапазону данных, содержащему указанное значение, тогда как третий раздел - это информация, которая должна быть вычислена.
Для создания нового файла надо нажать (Ctrl + N). Для перехода к меню или открытия файла - (Ctrl + O), сохранения файла - (Ctrl + S), «Сохранить как» - (F12), печати - (Ctrl + P). Закрыть файл - (Ctrl + F4). Последнее действие: отменить - (Ctrl + Z), повторить - (Ctrl + Y). Начать создание формулы - (=). Закрыть файл - (Alt + F4). Добавить столбец - (Ctrl + Shift + клавиша плюс). Вставить новый лист - (Shift + F11). Вызвать диалог создания таблицы - (Ctrl + L). Свернуть текст в ячейке - (Alt + Enter).
Операции навигации
Для выполнения переходов предусмотрены следующие горячие клавиши Excel. Вызов окна «Перейти» - (Ctrl + G). Перейти: к ячейке справа - (Tab); к ячейке слева - (Shift + Tab); вверх на один экран - (PageUp); вниз на один экран - (PageDown); на следующий лист - (Ctrl + PageDown); на предыдущий лист - (Ctrl + PageUp); в области данных: к начальной ячейке - (Ctrl + Home); к заключающей ячейке - (Ctrl + End); в сторону стрелки к первой ячейке - (Ctrl + Стрелка). Для перемещения между листами открытой книги - (Ctrl + F6).
Форматирование текста и ячейки
Для в выбранной ячейке (области ячеек) используются горячие клавиши Excel, которые приведены ниже. В этих сочетаниях используется клавиша Ctrl плюс другие клавиши, указанные в этом подразделе в скобках.
Шрифт: полужирный - (B), подчеркнутый - (U), курсивный - (3), зачеркнутый - (5). Меню: смены шрифта - (Shift + F), размера шрифта - (Shift + P). Применить начертание границ - (Shift + 7). Удалить все границы - (Shift + Подчеркивание).
Вызвать меню: форматирования ячеек - (1), выбора шрифта - (Shift + F), выбора размера шрифта - (Shift + P). Установить формат: процентный - (Shift + 5), числовой - (Shift + 1), времени - (Alt + 2), даты - (Alt + 3), денежный - (Alt + 4), валютный - (Shift + 4), общий (для удаления форматирования) - (Shift + #).
Выделение и редактирование текста
Выделить: весь лист - (Ctrl + A), полную строку - (Shift + Пробел), весь столбец - (Ctrl + Пробел), массив ячеек в сторону стрелки - (Shift + Стрелка), массив ячеек, включающий все данные листа книги - (Ctrl + Shift + 8).
Выделенный текст: вырезать (в буфер) - (Ctrl + X), копировать (в буфер) - (Ctrl + C). Вставить из буфера - (Ctrl + V). Удалить один символ от курсора: справа (Delete), слева - (Backspace). Изменение активной ячейки - (F2). Удаление текущего значения и изменение активной ячейки - (Пробел).
Другие горячие клавиши Excel
Вызов окна поиска текста - (Ctrl + F), замены текста - (Ctrl + H), выбора стиля - (Alt + "). Автоматически создать диаграмму на новом листе (F11). Изменить комментарий к ячейке - (Shift + F2). Вычисление AutoSum выделенных ячеек в столбце - (Alt + =); значение суммы проставляется в ячейке данного столбца в следующей под выделением строке. Вставить в выбранную ячейку актуальную: дату - (Ctrl + Shift +4), время - (Ctrl + Shift +6). Проверка правописания - (F7).
Алгоритм действий
Если нужно объединить ячейки в Excel, возникают некоторые трудности. Ведь выполнение этой операции связано с потерей данных во всех ячейках объединяемой области, кроме самой верхней левой. Сначала надо выделить область ячеек, подлежащих объединению, нажать правой клавишей мыши на области выделения, выбрать в выпадающем меню пункт «Формат ячеек…». Появляется само окошко «Формат ячеек», в нем выбрать вкладку «Выравнивание» и поставить галочку напротив пункта «объединение ячеек». Для выполнения рассматриваемой операции в целом не предусмотрены в приложении Excel горячие клавиши.
Объединение ячеек проще выполнить с помощью специальной кнопки «Объединить и поместить в центре», которая находится в меню «Главная». Если вам не надо размещать данные в центре, нажмите на метку справа от этой кнопки и в выпадающем подменю выберите подходящий пункт. Кстати, если вам приходится часто пользоваться этой операцией, то лучше разместить данную кнопку на панели быстрого доступа. Для сохранения данных из других ячеек объединяемой области, кроме левой верхней, надо перенести их содержимое в другое место или добавить их в эту ячейку с помощью операций копирования (или вырезания) и вставки.
Другие возможные способы объединения ячеек, исключающие потерю данных, связаны с использованием макросов, которые можно найти, например, на форумах по работе в Excel. Принцип работы макросов такой же, как и вручную, но выполняется быстрее и незаметнее для пользователя.
Как вставить строку с помощью горячих клавиш
Часто возникает необходимость в рабочий лист Excel вставить строку. Горячие клавиши, используемые для этой операции - Ctrl + знак плюс. При этом следует выделить строку, над которой надо вставить новую. Это выделение можно выполнить комбинацией горячих клавиш (Shift + Пробел) или щелкнув левой клавишей мыши в области номера слева соответствующей строки, когда курсор примет форму стрелки. Если строка не выделена, то комбинация горячих клавиш (Ctrl + знак плюс) приводит к появлению соответствующего меню «Добавление ячеек», которое предлагает выбрать, что вставлять: ячейки со сдвигом вниз или вправо, строку или столбец.
Горячая клавиша Excel «Удалить строку» - (Ctrl + знак минус). Для удаления строки надо ее выделить и выполнить нажатие этого сочетания клавиш.
При нажатии клавиши Alt на панели меню приложения появляются подсказки горячих клавиш соответствующих разделов меню. Это иллюстрируется на рисунке. Доступно для версий программы начиная с Excel 2007.
Специальная вставка в Excel
Часто надо выполнить вставку в ячейку только значения или только формулы. Для этого существует такая опция в Excel - специальная вставка. Горячие клавиши для этого - (Ctrl + Alt + V).
При нажатии этих клавиш откроется окно диалога «Специальная вставка». Оно доступно, если непосредственно перед этим было сделано копирование или вырезание объекта, содержимого ячейки, текста на листе или в другой программе. В окне следует выбрать интересующий вас пункт: формулы, значение, форматы, примечание или другой из предлагаемых вариантов.
Таким образом, ознакомившись с содержанием этой статьи, вы узнали, какие существуют горячие клавиши Excel и как ими пользоваться. Естественно, в статье описаны не все существующие комбинации. Для более полного изучения темы следует воспользоваться специальной литературой.