В Excel все время приходится проводить различные манипуляции с таблицами, и далее мы рассмотрим, как в Экселе скопировать таблицу, и как вставить таблицу в Эксель. Операции эти на самом деле очень простые, но при их использовании необходимо учитывать ряд тонкостей при работе в данной программе.
Чтобы скопировать таблицу в Excel ее естественно нужно предварительно выделить. Для копирования быстрее всего использовать комбинацию клавиш «Ctrl + C» или использовать соответствующий пункт меню «Копировать» на вкладке «Главная» .
Рядом с пунктом меню «Копировать» имеется стрелочка, при нажатии на которую появляется дополнительная возможность «Копировать как рисунок…» . При выборе данного варианта выделенная нами таблица будет скопирована как рисунок, но при этом будет показано диалоговое окно с дополнительными настройками копирования.
Скопированная таким образом таблица в Excel будет хранится в буфере обмена как картинка.
Будем считать, что таблицу скопировать нам удалось, теперь пришло время разобраться, как вставить таблицу в Excel. Тут тоже может быть все очень просто, так как можно воспользоваться комбинацией клавиш «Ctrl + V» . Но при вставке таблицы в Эксель, как и при вставке любых других данных, следует учитывать тот факт, что вставляемые данные будут начиная с выделенной ячейки на листе занимать необходимое пространство, замещая собой всю информацию, размещенную в необходимых для данной таблицы ячейках. Вставляются данные начиная с левой верхней ячейки. И еще следует помнить, что если после копирования таблицы или других данных будет произведена любая манипуляция на листе, кроме перемещения курсора, то данные из буфера обмена будут удалены. Это значит, что вставлять таблицу в Эксель нужно сразу же после ее копирования.
Также для вставки можно воспользоваться пунктом меню «Вставить» на вкладке «Главная» .
В этой статье мы покажем Вам ещё несколько полезных опций, которыми богат инструмент Специальная вставка , а именно: Значения, Форматы, Ширины столбцов и Умножить / Разделить. С этими инструментами Вы сможете настроить свои таблицы и сэкономить время на форматировании и переформатировании данных.
Если Вы хотите научиться транспонировать, удалять ссылки и пропускать пустые ячейки при помощи инструмента Paste Special (Специальная вставка) обратитесь к статье Специальная вставка в Excel: пропускаем пустые ячейки, транспонируем и удаляем ссылки .
Возьмём для примера таблицу учёта прибыли от продаж печенья на благотворительной распродаже выпечки. Вы хотите вычислить, какая прибыль была получена за 15 недель. Как видите, мы использовали формулу, которая складывает сумму продаж, которая была неделю назад, и прибыль, полученную на этой неделе. Видите в строке формул =D2+C3 ? Ячейка D3 показывает результат этой формулы – $100 . Другими словами, в ячейке D3 отображено значение. А сейчас будет самое интересное! В Excel при помощи инструмента Paste Special (Специальная вставка) Вы можете скопировать и вставить значение этой ячейки без формулы и форматирования. Эта возможность иногда жизненно необходима, далее я покажу почему.
Предположим, после того, как в течение 15 недель Вы продавали печенье, необходимо представить общий отчёт по итогам полученной прибыли. Возможно, Вы захотите просто скопировать и вставить строку, в которой содержится общий итог. Но что получится, если так сделать?
Упс! Это совсем не то, что Вы ожидали? Как видите, привычное действие скопировать и вставить в результате скопировало только формулу из ячейки? Вам нужно скопировать и выполнить специальную вставку самого значения. Так мы и поступим! Используем команду Paste Special (Специальная вставка) с параметром Values (Значения), чтобы все было сделано как надо.
Заметьте разницу на изображении ниже.
Применяя Специальная вставка > Значения , мы вставляем сами значения, а не формулы. Отличная работа!
Возможно, Вы заметили ещё кое-что. Когда мы использовали команду Paste Special (Специальная вставка) > Values (Значения), мы потеряли форматирование. Видите, что жирный шрифт и числовой формат (знаки доллара) не были скопированы? Вы можете использовать эту команду, чтобы быстро удалять форматирование. Гиперссылки, шрифты, числовой формат могут быть быстро и легко очищены, а у Вас останутся только значения без каких-либо декоративных штучек, которые могут помешать в будущем. Здорово, правда?
На самом деле, Специальная вставка > Значения – это один из моих самых любимых инструментов в Excel. Он жизненно необходим! Часто меня просят создать таблицу и представить её на работе или в общественных организациях. Я всегда переживаю, что другие пользователи могут привести в хаос введённые мной формулы. После того, как я завершаю работу с формулами и калькуляциями, я копирую все свои данные и использую Paste Special (Специальная вставка) > Values (Значения) поверх них. Таким образом, когда другие пользователи открывают мою таблицу, формулы уже нельзя изменить. Это выглядит вот так:
Обратите внимание на содержимое строки формул для ячейки D3 . В ней больше нет формулы =D2+C3 , вместо этого там записано значение 100 .
И ещё одна очень полезная вещь касаемо специальной вставки. Предположим, в таблице учёта прибыли от благотворительной распродажи печенья я хочу оставить только нижнюю строку, т.е. удалить все строки, кроме недели 15. Смотрите, что получится, если я просто удалю все эти строки:
Появляется эта надоедливая ошибка #REF! (#ССЫЛ!). Она появилась потому, что значение в этой ячейке рассчитывается по формуле, которая ссылается на ячейки, находящиеся выше. После того, как мы удалили эти ячейки, формуле стало не на что ссылаться, и она сообщила об ошибке. Используйте вместо этого команды Copy (Копировать) и Paste Special (Специальная вставка) > Values (Значения) поверх исходных данных (так мы уже делали выше), а затем удалите лишние строки. Отличная работа:
Специальная вставка > Форматы это ещё один очень полезный инструмент в Excel. Мне он нравится тем, что позволяет достаточно легко настраивать внешний вид данных. Есть много применений для инструмента Специальная вставка > Форматы , но я покажу Вам наиболее примечательное. Думаю, Вы уже знаете, что Excel великолепен для работы с числами и для выполнения различных вычислений, но он также отлично справляется, когда нужно представить информацию. Кроме создания таблиц и подсчёта значений, Вы можете делать в Excel самые различные вещи, такие как расписания, календари, этикетки, инвентарные карточки и так далее. Посмотрите внимательнее на шаблоны, которые предлагает Excel при создании нового документа:
Я наткнулся на шаблон Winter 2010 schedule и мне понравились в нем форматирование, шрифт, цвет и дизайн.
Мне не нужно само расписание, а тем более для зимы 2010 года, я хочу просто переделать шаблон для своих целей. Что бы Вы сделали на моём месте? Вы можете создать черновой вариант таблицы и вручную повторить в ней дизайн шаблона, но это займёт очень много времени. Либо Вы можете удалить весь текст в шаблоне, но это тоже займёт уйму времени. Гораздо проще скопировать шаблон и сделать Paste Special (Специальная вставка) > Formats (Форматы) на новом листе Вашей рабочей книги. Вуаля!
Теперь Вы можете вводить данные, сохранив все форматы, шрифты, цвета и дизайн.
Вы когда-нибудь теряли уйму времени и сил, кружа вокруг своей таблицы и пытаясь отрегулировать размеры столбцов? Мой ответ – конечно, да! Особенно, когда нужно скопировать и вставить данные из одной таблицы в другую. Существующие настройки ширины столбцов могут не подойти и даже, несмотря на то, что автоматическая настройка ширины столбца – это удобный инструмент, местами он может работать не так, как Вам хотелось бы. Специальная вставка > Ширины столбцов – это мощный инструмент, которым должны пользоваться те, кто точно знает, чего хочет. Давайте для примера рассмотрим список лучших программ US MBA.
Как такое могло случиться? Вы видите, как аккуратно была подогнана ширина столбца по размеру данных на рисунке выше. Я скопировал десять лучших бизнес-школ и поместил их на другой лист. Посмотрите, что получается, когда мы просто копируем и вставляем данные:
Содержимое вставлено, но ширина столбцов далеко не подходящая. Вы хотите получить точно такую же ширину столбцов, как на исходном листе. Вместо того чтобы настраивать ее вручную или использовать автоподбор ширины столбца, просто скопируйте и сделайте Paste Special (Специальная вставка) > Column Widths (Ширины столбцов) на ту область, где требуется настроить ширину столбцов.
Видите, как все просто? Хоть это и очень простой пример, но Вы уже можете представить, как будет полезен такой инструмент, если лист Excel содержит сотни столбцов.
Кроме этого, Вы можете настраивать ширину пустых ячеек, чтобы задать им формат, прежде чем вручную вводить текст. Посмотрите на столбцы E и F на картинке выше. На картинке внизу я использовал инструмент Специальная вставка > Ширины столбцов , чтобы расширить столбцы. Вот так, без лишней суеты Вы можете оформить свой лист Excel так, как Вам угодно!
Помните наш пример с печеньем? Хорошие новости! Гигантская корпорация узнала о нашей благотворительной акции и предложила увеличить прибыль. После пяти недель продаж, они вложатся в нашу благотворительность, так что доход удвоится (станет в два раза больше) по сравнению с тем, какой он был в начале. Давайте вернёмся к той таблице, в которой мы вели учёт прибыли от благотворительной распродажи печенья, и пересчитаем прибыль с учётом новых вложений. Я добавил столбец, показывающий, что после пяти недель продаж прибыль удвоится, т.е. будет умножена на 2 .
Весь доход, начиная с 6-й недели, будет умножен на 2 .Чтобы показать новые цифры, нам нужно умножить соответствующие ячейки столбца C на 2 . Мы можем выполнить это вручную, но будет гораздо приятнее, если за нас это сделает Excel с помощью команды Paste Special (Специальная вставка) > Multiply (Умножить). Для этого скопируйте ячейку F7 и примените команду на ячейки C7:C16 . Итоговые значения обновлены. Отличная работа!
Как видите, инструмент Специальная вставка > Умножить может быть использован в самых разных ситуациях. Точно так же обстоят дела с Paste Special (Специальная вставка) > Divide (Разделить). Вы можете разделить целый диапазон ячеек на определённое число быстро и просто. Знаете, что ещё? При помощи Paste Special (Специальная вставка) с опцией Add (Сложить) или Subtract (Вычесть) Вы сможете быстро прибавить или вычесть число.
Итак, в этом уроке Вы изучили некоторые очень полезные возможности инструмента Специальная вставка , а именно: научились вставлять только значения или форматирование, копировать ширину столбцов, умножать и делить данные на заданное число, а также прибавлять и удалять значение сразу из диапазона ячеек.
Возможность проверки в Excel - это замечательная вещь, но она не умеет выполнять одно важное действие (без следующего трюка): автоматически добавлять новую запись в список, который используется в качестве источника для списка проверки.
Если вы уже работали с проверкой, то знаете, что это весьма искусная возможность. Вероятно, наиболее впечатляющим является ее умение добавлять в любую ячейку список, в котором пользователь может выбирать значения. Было бы здорово, если бы при вводе нового имени в проверенную ячейку Excel автоматически добавлял ее в список. Это возможно благодаря следующему трюку.
Пусть есть список имен в диапазоне А1:А10 (рис. 2.24).
Эти имена представляют сотрудников компании. Не так уже редко в подобный список добавляются имена новых сотрудников, но сейчас единственный способ сделать это - добавить новые имена в конце списка, а затем выбирать их в списке в проверенной ячейке. Чтобы обойти это ограничение, сделайте следующее. В ячейке А11 введите следующую формулу и скопируйте ее вниз до строки 20 (рис. 2.25) - обратите внимание на относительную ссылку на ячейку А10: =IF(OR($D$1="";COUNTIF($A$1:A10;$D$l));"x";$D$l) , в русской версии Excel: =ЕСЛИ(ИЛИ($D$1="";СЧЁТЕСЛИ($А$1:А10;$D$1));"х";$D$1) .
Теперь выберите команду Формулы > Присвоить Имя (Formulas > Define Name) и в поле Имя (Names in workbook) введите MyNames. В поле Формула (Refers to) введите следующую формулу (рис. 2.26), щелкните кнопку Добавить (Add), затем щелкните кнопку ОК: =OFFSET(Sheet1!$А$1;0;0;COUNTIF(Sheet1!$А:$А;"х");1) , в русской версии Excel: =CMEЩ(Sheet1!$А$1;0;0;СЧЕТЕСЛИ(Sheet1!$А:$А;"х");1) .
Выделите ячейку D1 и выберите команду Данные > Проверка (Data > Validation). В поле Тип данных (Allow) выберите пункт Список (List), а в поле Источник (Source) введите =MyNames; удостоверьтесь, что установлен флажок Список допустимых значений (In-Cell). Перейдите на вкладку Сообщение об ошибке (Error Alert) и сбросьте флажок Выводить сообщение об ошибке (Show error alert after invalid data is entered). Щелкните кнопку ОК. Вы увидите результат, как на рис. 2.27.
Правой кнопкой мыши щелкните ярлычок листа и в контекстном меню выберите команду Исходный текст (View Code). Введите код, приведенный в листинге 2.5.
| 1 2 3 4 5 6 7 8 | // Листинг 2.5 Private Sub Worksheet_Calculate() On Error Resume Next Application. EnableEvents = False Range ("MyNames" ) = Range ("MyNames" ) . Value Application. EnableEvents = True On Error GoTo 0 End Sub |
// Листинг 2.5 Private Sub Worksheet_Calculate() On Error Resume Next Application.EnableEvents = False Range("MyNames") = Range("MyNames").Value Application.EnableEvents = True On Error GoTo 0 End Sub
Чтобы вернуться обратно в Excel и сохранить рабочую книгу, закройте окно. Теперь выделите ячейку D1, введите любое имя, не входящее в список, и нажмите клавишу Enter. Снова выделите ячейку D1 и взгляните на список. Теперь в списке должно присутствовать новое имя (рис. 2.28).
Если вы хотите добавить больше 10 новых имен в список, скопируйте формулу ниже строки 20.
Вставляйте и удаляйте строки, столбцы и ячейки для оптимального размещения данных на листе.
Примечание: В Microsoft Excel установлены следующие ограничения на количество строк и столбцов: 16 384 столбца в ширину и 1 048 576 строк в высоту.
Чтобы вставить столбец, выделите его, а затем на вкладке Главная нажмите кнопку Вставить и выберите пункт Вставить столбцы на лист .
Чтобы удалить столбец, выделите его, а затем на вкладке Главная нажмите кнопку Вставить и выберите пункт Удалить столбцы с листа .
Можно также щелкнуть правой кнопкой мыши в верхней части столбца и выбрать команду Вставить или Удалить .
Чтобы вставить строку, выделите ее, а затем на вкладке Главная нажмите кнопку Вставить и выберите пункт Вставить строки на лист .
Чтобы удалить строку, выделите ее, а затем на вкладке Главная нажмите кнопку Вставить и выберите пункт Удалить строки с листа .
Можно также щелкнуть правой кнопкой мыши выделенную строку и выбрать команду Вставить или Удалить .
Выделите одну или несколько ячеек. Щелкните правой кнопкой мыши и выберите команду Вставить .
В окне Вставка выберите строку, столбец или ячейку для вставки.
Например, чтобы вставить новую ячейку между ячейками "Лето" и "Зима":
Щелкните ячейку "Зима".
На вкладке Главная Вставить и выберите команду Вставить ячейки (со сдвигом вниз) .
Новая ячейка добавляется над ячейкой "Зима":
Чтобы вставить одну строку : щелкните правой кнопкой мыши всю строку, над которой требуется вставить новую, и выберите команду Вставить строки .
Чтобы вставить несколько строк, выполните указанные ниже действия. Выделите одно и то же количество строк, над которым вы хотите добавить новые. Щелкните выделенный фрагмент правой кнопкой мыши и выберите команду Вставить строки.
Чтобы вставить один новый столбец, выполните указанные ниже действия. Щелкните правой кнопкой мыши весь столбец справа от того места, куда вы хотите добавить новый столбец. Например, чтобы вставить столбец между столбцами B и C, щелкните правой кнопкой мыши столбец C и выберите команду Вставить столбцы.
Чтобы вставить несколько столбцов, выполните указанные ниже действия. Выделите то же количество столбцов, справа от которых вы хотите добавить новые. Щелкните выделенный фрагмент правой кнопкой мыши и выберите команду Вставить столбцы .
Если вам больше не нужны какие-либо ячейки, строки или столбцы, вот как удалить их:
Выделите ячейки, строки или столбцы, которые вы хотите удалить.
На вкладке Главная щелкните стрелку под кнопкой Удалить и выберите нужный вариант.
При удалении строк или столбцов следующие за ними строки и столбцы автоматически сдвигаются вверх или влево.
Совет: Если вы передумаете сразу после того, как удалите ячейку, строку или столбец, просто нажмите клавиши CTRL+Z, чтобы восстановить их.
В этом приеме описывается три способа получить данные, содержащиеся в веб-странице: вставить статическую копию информации; создать обновляемую ссылку на сайт; открыть страницу непосредственно в Excel.
Один из способов получить данные из веб-страницы на лист - просто выделить текст в браузере, нажать Ctrl+C , чтобы скопировать его в буфер обмена, а затем вставить текст в таблицу. Результат может быть разным, в зависимости от того, какой браузер вы используете. Если это Internet Explorer, то вставленные данные будут, вероятно, очень похожи на оригинал - в комплекте с настройками форматирования, гиперссылками и графикой.
Если вы используете браузер, отличный от Internet Explorer, то, выбрав Главная Буфер обмена Вставить , можно вставить все, что вы скопировали с веб-страницы, в одну ячейку, а это, скорее всего, не то, чего вы хотите. Решение состоит в том, чтобы выбрать команду Главная Буфер обмена Вставить Специальная вставка , а затем пробовать различные варианты вставки.
Если вы хотите регулярно получать доступ к обновленным данным из веб-страницы, создайте веб-запрос. На рис. 176.1 показан сайт, который содержит курсы валют в таблице с тремя столбцами. Выполнив следующие шаги, можно создать веб-запрос, позволяющий извлекать эту информацию, а затем обновлять ее в любое время одним щелчком кнопкой мыши.
По умолчанию импортированные данные - это веб-запрос. Чтобы обновить информацию, щелкните правой кнопкой мыши на любой ячейке импортированного диапазона и выберите в контекстном меню команду Обновить . Если вы не хотите создавать обновляемый запрос, укажите это в шаге 5 предыдущего списка действии. В окне Импорт данных нажмите кнопку Свойства и снимите флажок сохранить определение запроса.
Еще один способ получить данные веб-страницы на лист - открыть URL-адрес напрямую, с помощью команды Файл Открыть . Просто введите полный URL-адрес в поле Имя файла и нажмите кнопку Открыть . Результат будет отличаться в зависимости от того, какая разметка у веб-страницы, но в большинстве случаев он вас удовлетворит. Иногда таким способом извлекается довольно много посторонней информации.
