Я часто сожалел, что в MS Excel нет такого удобного сочетания клавиш для вставки значения, как сочетания Ctrl+Shift+V , которое используется в Calc . Как ни странно, но в Excel действительно нет НИКАКИХ штатных и работающих по умолчанию сочетаний клавиш для вставки значений. Однако альтернативные способы есть. Ниже мы их рассмотрим.
Способ первый
, самый известный, самый распространённый и... самый времязатратный.
Рисунок 21 - Команда команды Выравнивание на вкладке Главная. Другая вкладка в окне «Формат ячеек» называется шрифтом. Стоит упомянуть о возможности форматирования содержимого в верхнем или нижнем индексе или в разных стилях подчеркивания шрифтов. Вкладка «Граница» предлагает конкретные способы разграничения ячейки или группы ячеек.
На вкладке «Заливка» вы можете выбрать цвет заливки или цвет и образец образца в наборе 80 цветов, или вы можете смешать любой цвет из доступной 24-битной палитры цветов над кнопкой «Следующий цвет». Здесь вы можете получить интересные и оригинальные комбинации цветов заливки, стилей и образцов.
В ячейке А1 находится формула, которая суммирует значения ячеек В1 (1) и С1 (2). Вместо неё мы хотим вставить результат её работы (3). Что мы для этого делаем?
1. Выделяем ячейку А1
2. Правой кнопкой мыши вызываем контекстное меню
3. В меню выбираем пункт Копировать
4. Опять правой кнопкой мыши вызываем контекстное меню (потому что оно пропадает после выбора любой команды из него)
5. Выбираем пункт Специальная вставка
Закладка блокировки используется для установки ячеек, которые нельзя заблокировать. Это только переключатель, который определяет, будет ли ячейка заблокирована или нет. Сама фиксация выполняется на главной вкладке команды «Редактирование», «Заблокировать письмо».
Копируем ширину столбцов на другой лист
Условное форматирование - очень простой и практичный инструмент для выделения ключевых данных в ячейках. Для правильного использования всегда необходимо выбрать ячейку или диапазон ячеек. Затем просто нажмите вкладку «Главная» в группе «Стили», чтобы активировать кнопку «Условное форматирование».
6. В появившемся окне выбираем значения
7. Жмём ОК
Всё, теперь у нас вместо формулы =В1+С1 значение 3
Рисунок 22 - Форматирование условных ячеек. Выделите правила ячеек Наиболее распространенные или наименее используемые правила Строки данных Значки цветовой шкалы. В дополнение к основным способам создания условного форматирования можно создавать настраиваемые условия, которые не зависят от содержимого ячейки, а от результатов формул. Эти пользовательские форматы могут быть созданы с помощью кнопки «Новое правило» в основном меню или с помощью диспетчера правил условного формата. Еще одним новшеством по сравнению с предыдущими версиями является способность условно дублировать значения и значения выше и ниже среднего.
Не кажется ли Вам, что семь шагов для такого простого действия, как вставка значения, многовато?
Способ второй (работает во всех версиях Excel) , малоизвестный, но выполняется гораздо быстрее.
1. Выделяем ячейку А1
2. Мышью наводим на нижнюю границу ячейки, пока не получим курсор в виде крестика, каждый из лучей которого, увенчан стрелочками
3. Нажимаем правую кнопку мыши и перетаскиваем ячейку куда-нибудь (достаточно даже в соседнюю) и возвращаем назад
4. Отпускаем правую кнопку мыши и в появившемся меню выбираем пункт Копировать только значения
Сводные таблицы и сводные таблицы
Рисунок 23 - Условное форматирование независимо от содержимого ячейки, но по результату вычисления. Арифметический логический текст. . Сводная таблица - простой, но очень практичный инструмент анализа данных, в основном большие таблицы. Вы будете использовать сводные таблицы в бухгалтерском, финансовом, фондовом планировании и контроле в целом.
Он может отображать только необходимые поля из большой таблицы ввода или перекрестные ссылки с другими важными полями, а затем суммировать данные с одной из 11 доступных вычислительных процедур. Сводная таблица упрощается с помощью умной комбинации фильтров, сортировки и сводных инструментов без изменения исходной таблицы данных. Если у вас есть входные данные с правильным порядком, просто выберите первую сводную таблицу в своих данных и вкладку «Вставка», чтобы активировать сводную таблицу.
Готово. Но и это долго, к тому же не подходит тем кто привык пользоваться "горячими клавишами", а не мышкой.
Способ третий (работает в версиях Excel, вышедших после Excel 2003 ) , неизвестный (в интернете я его не нашел), но выполняется ещё быстрее, хотя и требует разовой предварительной настройки Excel.
Рисунок 42 - Один из способов запуска сводной таблицы. Новая новая сводная таблица будет создана на новом рабочем листе, где вы будете манипулировать им над правой панелью. Вы вводите категории ключевых данных в строки описания, цифры в значения и метки столбцов и фильтр для больших входных данных.
Если вам нужно настроить представление своих данных, вы перемещаете отдельные поля в правой панели. Рисунок 43. Ключевая обработка полей таблицы непредвиденных обстоятельств. Эти операции доступны в раскрывающемся меню ключевых полей. Рисунок 44 - Поля сортировки и фильтрации в сводных таблицах.
Итак, сначала добавим кнопку вставки значений на панель быстрого доступа (ПБД).
1. Нажимаем на кнопку настройки ПБД и из появившегося списка выбираем пункт Другие команды...
2. В меню Выбрать команды из: выбираем пункт Все команды и из появившегося списка выбираем иконку помеченную как Вставить значения .
Сводная таблица представляет собой отображение данных, называемых отчетами или отчетами. Рисунок 45 - Выбор вычисления суммирования в числовом поле значений сводной таблицы. В дополнение к основным вычислениям суммирования результаты могут отображаться здесь, например, процентные отношения относительно ключевого поля. Это выбирается в том же окне, как раз на вкладке «Показать значения».
Макет данных в сводной таблице можно легко изменить на вкладке «Сводные инструменты» на вкладке «Дизайн» в группе команд «Макет». Рисунок 46 - Параметры макета в сводной таблице. Переключение выполняется автоматически при открытии сводной таблицы в режиме совместимости. Сводная таблица не влияет на исходные данные, это всего лишь односторонний поток данных. Когда вы меняете данные в исходной таблице, не забудьте обновить сводную таблицу. Переключение может быть достигнуто в параметрах сводной таблицы. . Рисунок 47 - Обзор инструментов базы данных.
3. Жмём кнопку Добавить и перемещаем появившуюся в списке справа иконку вверх списка.
4. На ПБД появилась наша кнопка
Вкладка «Данные» содержит все простые и расширенные инструменты фильтрации и сортировки, однако самый простой способ быстро отфильтровать или сортировать - предложить контекстное меню в ключевом поле таблицы, которое вы щелкните правой кнопкой мыши. Рисунок 48 - Самый быстрый способ фильтрации или сортировки данных в таблице.
При работе с большими электронными таблицами вы можете использовать расширенный фильтр, который скрыт на вкладке «Данные» под кнопкой «Дополнительно». Аналогично, большие электронные таблицы могут использовать расширенную сортировку, доступную под кнопкой «Сортировка».
5. Нажимаем Alt и видим номер нашей кнопки (в данном случае это номер 1)
Всё, настройка закончена.
А как теперь пользоваться этой кнопкой?
Можно по прямому назначению:
1. Выделяем ячейку А1
2. Копируем (можно мышью, можно сочетанием клавиш)
3. Нажимаем кнопку Вставить значения
Группы и автоматические отчеты
Рисунок 49 - Расширенная сортировка может сортировать таблицу в соответствии с критериями в до 64 столбцов. С помощью групп вы можете быстро отображать или скрывать нужные столбцы или строки. Используйте соседнюю кнопку, чтобы отделить помеченные строки или столбцы от группы, чтобы удалить или разбить всю группу. Вы можете применять автоматический просмотр только к электронным таблицам, которые имеют регулярные макеты шаблонов в строках или столбцах. Этот отличный инструмент предполагает строки или столбцы с совокупными расчетами.
Но помните, я говорил о том что можно обойтись без мыши? Тогда процесс будет выглядеть так:
1. Выделяем ячейку А1
2. Ctrl+C
3. Alt+1
, где 1 - это номер нашей кнопки на ПБД.
Быстро? Удобно? Думаю любители хоткеев оценят этот способ:)
Способ четвёртый , макросом.
Вставьте этот код в стандартный модуль личной книги макросов и при запуске Excel Вам всегда будет доступна быстрая вставка значений назначенным сочетанием клавиш (в примере это сочетание Ctrl+Shift+V ).
Рисунок 51 - Вы можете отменить автоматический просмотр, нажав кнопку «Снять отметку» и выбрав «Очистить отчет». Сводка - это инструмент для обобщения категориальных данных в таблицах. Для правильного использования сначала необходимо отсортировать таблицу по категориям или категориям, которые необходимо суммировать.
Резюме могут быть вложенными или объединенными в соответствии с потребностями. Будьте осторожны, чтобы работать с сводкой, которую вы должны быть в таблице, которую вы суммируете. Если область создана из вашей таблицы, резюме не могут использоваться в этой форме. Тем не менее, можно создать сводную таблицу из этой таблицы и легко анализировать другие процессы с помощью инструментов сводной таблицы.
Sub CopyPasteValue()
Selection.PasteSpecial Paste:=xlPasteValues
End Sub
У этого способа есть два недостатка:
1. После применения макроса стек истории изменений очищается и отмена последних действий становится невозможна.
2. Макросы должны быть разрешены.
Способ пятый , самый экзотический и самый... бесполезный Я упоминаю о нём только для полноты картины, ни в коем случае не претендуя на то что кто-то им будет пользоваться.
Рисунок 52 - Чтобы отменить сводку, в итоговой таблице, активируйте кнопку «Сводка» и подтвердите «Удалить все». Текст столбца - это инструмент, который может обрабатывать разрозненный ввод данных, если он имеет систему правил, организует и анализирует в обычные столбцы, чтобы данные могли быть обработаны.
Используйте эту команду, если данные уже находятся на листе. В диалоговом окне «Открыть» вам нужно выбрать только тип файла «Все файлы». Рисунок 54 - Выбор типа файла данных. Этот инструмент может объединять данные из нескольких идентичных таблиц в одну область данных.
Этот способ основан на том, что при обмене данными между приложениями формулы не сохраняются. Соответственно, если Вы будете копировать данные из Excel одной версии и вставлять в другую, то вставка будет производится значениями а не формулами. На видео я копирую формулу из Excel 2010 в Excel 2003 и наоборот. И в том и в том случае вставляется значение.
Ядро этого инструмента используется в Мастере сводной таблицы. Рисунок 55 - Объединение данных из нескольких пунктов назначения. Рисунок 56 - Простой способ избавиться от дубликатов. Просто отметьте столбец, в котором вы хотите удалить дубликаты, и инструмент «Удалить дубликаты» удаляет все другие дубликаты и удаляет те же записи одной и той же записи. Двойственный критерий может быть определен на одном или нескольких столбцах.
Аутентификация данных - это инструмент, который может проверять пользовательские входы. В определенных ячейках можно задать только значения в требуемом диапазоне. Рисунок 57 - Аутентификация данных, инструмент для ограничения записей ячеек. Предельные параметры могут быть указаны как значения, диапазоны ячеек, названные области и формулы. Также можно влиять на степень соблюдения ограничительной информации. Либо будут абсолютные, либо менее ограничительные типы предупреждений и информации.
В этой статье мы покажем Вам ещё несколько полезных опций, которыми богат инструмент Специальная вставка , а именно: Значения, Форматы, Ширины столбцов и Умножить / Разделить. С этими инструментами Вы сможете настроить свои таблицы и сэкономить время на форматировании и переформатировании данных.
Если Вы хотите научиться транспонировать, удалять ссылки и пропускать пустые ячейки при помощи инструмента Paste Special (Специальная вставка) обратитесь к статье Специальная вставка в Excel: пропускаем пустые ячейки, транспонируем и удаляем ссылки .
Если вы предполагаете, что данные были введены за пределами разрешенного фрейма, вы можете графически выделить его, обведя неверные данные. Ниже кнопки анализа гипотезы представлена группа практических инструментов, в том числе. Сценарий можно рассматривать как расширенную память, которая запоминает ряд значений, которые могут отображаться или использоваться в расчете для одной или нескольких конкретных ситуаций. Сценарии могут быть полезны, если вам нужно показать больше вариантов расчета клиенту на встрече или коллегами.
Либо вы можете подготовить несколько листов с разными входами, либо ввести входные данные, когда вам нужно изменить вручную или подготовить сценарии. Рисунок 58 - Менеджер сценариев с 6 подготовленными ситуациями. Сценарий может запоминать до 32 значений в смежных или прерывистых ячейках, но значения могут быть только числовыми, текстовыми или датами. Невозможно сохранить формулу или функцию в сценарии.
Вставляем только значения
Возьмём для примера таблицу учёта прибыли от продаж печенья на благотворительной распродаже выпечки. Вы хотите вычислить, какая прибыль была получена за 15 недель. Как видите, мы использовали формулу, которая складывает сумму продаж, которая была неделю назад, и прибыль, полученную на этой неделе. Видите в строке формул =D2+C3 ? Ячейка D3 показывает результат этой формулы – $100 . Другими словами, в ячейке D3 отображено значение. А сейчас будет самое интересное! В Excel при помощи инструмента Paste Special (Специальная вставка) Вы можете скопировать и вставить значение этой ячейки без формулы и форматирования. Эта возможность иногда жизненно необходима, далее я покажу почему.
Предварительно подготовленные входы в сценарии могут быть экспортированы в сводную или сводную таблицу, включая варианты результатов расчета. Сценарии позволяют получить обзор только ключевых данных, то есть всех входов и только всех выходов. Поиск решения пытается рассчитать входные данные по принципу циклического цикла.
Поиск решения - очень практичный инструмент. Рисунок 59 - Поиск решения - способ анализа расчета входов. Инструмент «Таблица» может использоваться для так называемого анализа чувствительности. Этот инструмент может анализировать одну или несколько формул по принципу перекрестных помех и отображать варианты на разные шаги ввода. Инструмент полезен для крупномасштабных анализов, что экономит время проведения различных расчетов. Анализ чувствительности может быть выполнен с одной переменной, в которой можно проанализировать один или несколько вычислений или две переменные, но можно проанализировать только один расчет.
Предположим, после того, как в течение 15 недель Вы продавали печенье, необходимо представить общий отчёт по итогам полученной прибыли. Возможно, Вы захотите просто скопировать и вставить строку, в которой содержится общий итог. Но что получится, если так сделать?
Важным элементом ввода является ячейка замещения, которая косвенно вводится косвенным образом, но при вычислении. Рисунок 60 - Описание анализа чувствительности. Однако эти инструменты обычно недоступны, они должны быть активированы. После отметки и подтверждения аналитических инструментов и решений, на вкладке «Данные» появится новая группа значков справа. Анализ данных и решение.
Анализ данных предлагает полный обзор конкретных аналитических инструментов. Примеры задач, которые могут быть решены. Емкостные задачи - решает, например, производственную программу - сколько может производить больше типов продуктов для достижения максимальной производительности, если мы ограничены возможностями машин, рабочих и т.д. режущие задачи - при разрезании материала на куски разного размера мы ищем минимальные затраты на распределение задач - оптимальное распределение действий между машинами. Известной проблемой является транспортная проблема, в которой мы ищем подходящий маршрут, так что его длина минимальна для задач смешивания - мы ищем оптимальную смесь из нескольких материалов при минимальных затратах для удовлетворения требуемых свойств, например, для подачи исходной смеси. Рисунок 64 - Параметры настройки.
Упс! Это совсем не то, что Вы ожидали? Как видите, привычное действие скопировать и вставить в результате скопировало только формулу из ячейки? Вам нужно скопировать и выполнить специальную вставку самого значения. Так мы и поступим! Используем команду Paste Special (Специальная вставка) с параметром Values (Значения), чтобы все было сделано как надо.
Заметьте разницу на изображении ниже.
Применяя Специальная вставка > Значения , мы вставляем сами значения, а не формулы. Отличная работа!
Возможно, Вы заметили ещё кое-что. Когда мы использовали команду Paste Special (Специальная вставка) > Values (Значения), мы потеряли форматирование. Видите, что жирный шрифт и числовой формат (знаки доллара) не были скопированы? Вы можете использовать эту команду, чтобы быстро удалять форматирование. Гиперссылки, шрифты, числовой формат могут быть быстро и легко очищены, а у Вас останутся только значения без каких-либо декоративных штучек, которые могут помешать в будущем. Здорово, правда?
На самом деле, Специальная вставка > Значения – это один из моих самых любимых инструментов в Excel. Он жизненно необходим! Часто меня просят создать таблицу и представить её на работе или в общественных организациях. Я всегда переживаю, что другие пользователи могут привести в хаос введённые мной формулы. После того, как я завершаю работу с формулами и калькуляциями, я копирую все свои данные и использую Paste Special (Специальная вставка) > Values (Значения) поверх них. Таким образом, когда другие пользователи открывают мою таблицу, формулы уже нельзя изменить. Это выглядит вот так:
Обратите внимание на содержимое строки формул для ячейки D3 . В ней больше нет формулы =D2+C3 , вместо этого там записано значение 100 .
И ещё одна очень полезная вещь касаемо специальной вставки. Предположим, в таблице учёта прибыли от благотворительной распродажи печенья я хочу оставить только нижнюю строку, т.е. удалить все строки, кроме недели 15. Смотрите, что получится, если я просто удалю все эти строки:
Появляется эта надоедливая ошибка #REF! (#ССЫЛ!). Она появилась потому, что значение в этой ячейке рассчитывается по формуле, которая ссылается на ячейки, находящиеся выше. После того, как мы удалили эти ячейки, формуле стало не на что ссылаться, и она сообщила об ошибке. Используйте вместо этого команды Copy (Копировать) и Paste Special (Специальная вставка) > Values (Значения) поверх исходных данных (так мы уже делали выше), а затем удалите лишние строки. Отличная работа:
Специальная вставка > Значения: основные моменты
- Выделите данные
- Скопируйте их. Если данные не скопировать, а вырезать, то команда Paste Special (Специальная вставка) будет недоступна, поэтому обязательно используйте копирование.
- Выделите ячейку, в которую нужно вставить скопированные данные.
- Нажмите Paste Special
(Специальная вставка). Это можно сделать:
- кликнув правой кнопкой мыши и выбрав в контекстном меню Paste Special (Специальная вставка).
- на вкладке Home (Главная), нажмите маленький треугольник под кнопкой Paste (Вставить) и в выпадающем меню выберите Paste Special (Специальная вставка).
- Отметьте опцию Values (Данные).
- Нажмите ОК .
Вставляем только форматы
Специальная вставка > Форматы это ещё один очень полезный инструмент в Excel. Мне он нравится тем, что позволяет достаточно легко настраивать внешний вид данных. Есть много применений для инструмента Специальная вставка > Форматы , но я покажу Вам наиболее примечательное. Думаю, Вы уже знаете, что Excel великолепен для работы с числами и для выполнения различных вычислений, но он также отлично справляется, когда нужно представить информацию. Кроме создания таблиц и подсчёта значений, Вы можете делать в Excel самые различные вещи, такие как расписания, календари, этикетки, инвентарные карточки и так далее. Посмотрите внимательнее на шаблоны, которые предлагает Excel при создании нового документа:
Я наткнулся на шаблон Winter 2010 schedule и мне понравились в нем форматирование, шрифт, цвет и дизайн.
Мне не нужно само расписание, а тем более для зимы 2010 года, я хочу просто переделать шаблон для своих целей. Что бы Вы сделали на моём месте? Вы можете создать черновой вариант таблицы и вручную повторить в ней дизайн шаблона, но это займёт очень много времени. Либо Вы можете удалить весь текст в шаблоне, но это тоже займёт уйму времени. Гораздо проще скопировать шаблон и сделать Paste Special (Специальная вставка) > Formats (Форматы) на новом листе Вашей рабочей книги. Вуаля!
Теперь Вы можете вводить данные, сохранив все форматы, шрифты, цвета и дизайн.
Специальная вставка > Форматы: основные моменты
- Выделите данные.
- Скопируйте их.
- Выделите ячейку, в которую хотите вставить данные.
- Нажмите Paste Special (Специальная вставка).
- Отметьте опцию Formats (Форматы).
- Нажмите ОК .
Копируем ширину столбцов на другой лист
Вы когда-нибудь теряли уйму времени и сил, кружа вокруг своей таблицы и пытаясь отрегулировать размеры столбцов? Мой ответ – конечно, да! Особенно, когда нужно скопировать и вставить данные из одной таблицы в другую. Существующие настройки ширины столбцов могут не подойти и даже, несмотря на то, что автоматическая настройка ширины столбца – это удобный инструмент, местами он может работать не так, как Вам хотелось бы. Специальная вставка > Ширины столбцов – это мощный инструмент, которым должны пользоваться те, кто точно знает, чего хочет. Давайте для примера рассмотрим список лучших программ US MBA.
Как такое могло случиться? Вы видите, как аккуратно была подогнана ширина столбца по размеру данных на рисунке выше. Я скопировал десять лучших бизнес-школ и поместил их на другой лист. Посмотрите, что получается, когда мы просто копируем и вставляем данные:
Содержимое вставлено, но ширина столбцов далеко не подходящая. Вы хотите получить точно такую же ширину столбцов, как на исходном листе. Вместо того чтобы настраивать ее вручную или использовать автоподбор ширины столбца, просто скопируйте и сделайте Paste Special (Специальная вставка) > Column Widths (Ширины столбцов) на ту область, где требуется настроить ширину столбцов.
Видите, как все просто? Хоть это и очень простой пример, но Вы уже можете представить, как будет полезен такой инструмент, если лист Excel содержит сотни столбцов.
Кроме этого, Вы можете настраивать ширину пустых ячеек, чтобы задать им формат, прежде чем вручную вводить текст. Посмотрите на столбцы E и F на картинке выше. На картинке внизу я использовал инструмент Специальная вставка > Ширины столбцов , чтобы расширить столбцы. Вот так, без лишней суеты Вы можете оформить свой лист Excel так, как Вам угодно!
Специальная вставка > Ширина столбцов: основные моменты
- Выделите данные.
- Скопируйте выделенные данные.
- Поставьте курсор на ту ячейку, ширину которой Вы хотите настроить.
- Нажмите Paste Special (Специальная вставка).
- Отметьте опцию Column Widths (Ширины столбцов).
- Нажмите ОК .
Специальная вставка: разделить и умножить
Помните наш пример с печеньем? Хорошие новости! Гигантская корпорация узнала о нашей благотворительной акции и предложила увеличить прибыль. После пяти недель продаж, они вложатся в нашу благотворительность, так что доход удвоится (станет в два раза больше) по сравнению с тем, какой он был в начале. Давайте вернёмся к той таблице, в которой мы вели учёт прибыли от благотворительной распродажи печенья, и пересчитаем прибыль с учётом новых вложений. Я добавил столбец, показывающий, что после пяти недель продаж прибыль удвоится, т.е. будет умножена на 2 .
Весь доход, начиная с 6-й недели, будет умножен на 2 .Чтобы показать новые цифры, нам нужно умножить соответствующие ячейки столбца C на 2 . Мы можем выполнить это вручную, но будет гораздо приятнее, если за нас это сделает Excel с помощью команды Paste Special (Специальная вставка) > Multiply (Умножить). Для этого скопируйте ячейку F7 и примените команду на ячейки C7:C16 . Итоговые значения обновлены. Отличная работа!
Как видите, инструмент Специальная вставка > Умножить может быть использован в самых разных ситуациях. Точно так же обстоят дела с Paste Special (Специальная вставка) > Divide (Разделить). Вы можете разделить целый диапазон ячеек на определённое число быстро и просто. Знаете, что ещё? При помощи Paste Special (Специальная вставка) с опцией Add (Сложить) или Subtract (Вычесть) Вы сможете быстро прибавить или вычесть число.
Специальная вставка > Разделить / Умножить: основные моменты
- Выделите ячейку с числом, на которое нужно умножить или разделить.
- Скопируйте выделенные данные.
- Поставьте курсор на те ячейки, в которых Вы хотите выполнить умножение или деление.
- Нажмите Paste Special (Специальная вставка).
- Отметьте опцию Divide (Разделить) или Multiply (Умножить).
- Нажмите ОК .
Итак, в этом уроке Вы изучили некоторые очень полезные возможности инструмента Специальная вставка , а именно: научились вставлять только значения или форматирование, копировать ширину столбцов, умножать и делить данные на заданное число, а также прибавлять и удалять значение сразу из диапазона ячеек.
