Как скопировать сумму выбранных ячеек в Excel в буфер обмена
Обновлено: 20.11.2024
Майкл может выбрать несколько ячеек на листе, а панель задач (внизу листа) покажет некоторый анализ этих выбранных ячеек, например сумму или среднее значение ячеек. Майкл интересуется, есть ли способ скопировать значение с панели задач в буфер обмена, чтобы он мог вставить это значение в другое место на листе.
Короткий ответ: нет, нет способа получить доступ к информации на панели задач. При этом есть несколько подходов, которые вы можете использовать для воссоздания информации, чтобы вы могли получить к ней доступ.
Первый обходной путь не требует использования макроса, но вы можете счесть его излишним. (Кто сказал, что нельзя подстрелить комара из базуки в Excel?) Этот подход предполагает использование инструмента анализа данных. Этот инструмент реализуется за счет использования надстройки; вы можете узнать, включен ли он в вашей системе, посмотрев на вкладку «Данные» на ленте, где вы должны увидеть инструмент «Анализ данных» с правой стороны. Если вы его не видите, выполните следующие действия:
- Отображение диалогового окна "Параметры Excel". (В Excel 2007 нажмите кнопку «Офис», а затем выберите «Параметры Excel». В Excel 2010 и более поздних версиях отобразите вкладку «Файл» на ленте и нажмите «Параметры».)
- В левой части диалогового окна нажмите "Надстройки".
- В нижней части диалогового окна используйте раскрывающийся список "Управление", чтобы выбрать надстройки Excel.
- Нажмите кнопку «Перейти». Excel отображает диалоговое окно надстроек. (См. рис. 1.)
Рисунок 1. Диалоговое окно надстроек.
При активированной надстройке «Анализ данных» выберите ячейки, которые нужно проанализировать. Вы должны увидеть свою статистику на панели задач, как обычно. Теперь откройте вкладку «Данные» на ленте и щелкните инструмент «Анализ данных». Excel отображает диалоговое окно «Анализ данных». (См. рис. 2.)
Рисунок 2. Диалоговое окно "Анализ данных".
Это диалоговое окно ничего не делает, кроме списка множества различных анализов, которые вы можете выполнить. Вы хотите выбрать опцию «Описательная статистика», а затем нажать «ОК». Затем Excel отображает диалоговое окно Описательная статистика. (См. рис. 3.)
Рисунок 3. Диалоговое окно описательной статистики.
Если ячейки, которые вы хотите проанализировать, еще не отмечены в поле «Входной диапазон», щелкните один раз в этом поле, а затем выберите ячейки на листе. Затем в нижней части диалогового окна установите флажок Суммарная статистика. Когда вы нажимаете «ОК», Excel создает новый рабочий лист, содержащий всю статистику (и многое другое), которая может отображаться на панели задач. Затем вы можете скопировать любой из них обратно на свой рабочий лист, если хотите, и удалить добавленный рабочий лист, когда закончите с ним.
(Видишь ли, я говорил тебе, что это может быть излишеством.)
Если вы не возражаете против использования макроса, вы можете создать определяемую пользователем функцию, которая будет возвращать нужную информацию. Можно использовать следующее:
Чтобы использовать эту функцию, просто введите в ячейку примерно следующее:
Просматривая макрос, вы можете определить различные коды операций, которые можно передать функции через второй параметр. Определяемая пользователем функция тоже по-своему кажется излишней. Почему? Потому что вы можете просто использовать обычные формулы для получения нужной информации:
Вместо этого вы можете создать макрос, привязанный к сочетанию клавиш. Затем это можно использовать с любыми выбранными ячейками, чтобы поместить нужную информацию в буфер обмена:
Помните, что вы хотите назначить этот макрос сочетанию клавиш. Таким образом, вы можете вызвать его (используя ярлык), пока у вас есть несколько ячеек, выбранных на вашем листе. Если вы получаете сообщение об ошибке при попытке запустить макрос, это, несомненно, связано с тем, что для вашего проекта не включена библиотека объектов Microsoft Forms 2.0. Вам нужно в редакторе VBA выбрать Tools | Ссылки для включения необходимой библиотеки.
Когда вы на самом деле вызываете макрос, кажется, что ничего не происходит. Это нормально; помните, что информация помещается в буфер обмена. После запуска макроса вам нужно выбрать другую ячейку, а затем нажать Ctrl+V, чтобы вставить это содержимое буфера обмена на лист.
ExcelTips – это экономичное обучение работе с Microsoft Excel. Этот совет (6232) относится к Microsoft Excel 2007, 2010, 2013, 2016, 2019 и Excel в Office 365.
Биография автора
На его счету более 50 научно-популярных книг и множество журнальных статей. Аллен Уайатт является всемирно признанным автором. Он является президентом Sharon Parq Associates, компании, предоставляющей компьютерные и издательские услуги. Узнайте больше об Аллене.
БОЛЬШЕ ОТ АЛЛЕНА
Простое добавление пустых строк
Хотите добавить несколько пустых строк к своим данным и распределить эти строки между существующими строками? Вот .
Импорт пользовательских списков
Пользовательские списки — это удобный способ ввода повторяющихся данных на листе. Вот как вы можете импортировать свои собственные списки из .
Скрытие системной даты и времени
Обычно Windows отображает текущую дату и время в правой части панели задач. Вы можете скрыть дату и время, .
Успешно запрограммируйте в Excel! Имя Джона Уокенбаха является синонимом мастерства в расшифровке сложных технических вопросов. В этом всеобъемлющем руководстве «Г-н Электронная таблица» показывает, как максимально использовать возможности Excel, используя профессиональные советы по разработке приложений для работы с электронными таблицами из его личной книжной полки. Ознакомьтесь с Excel 2013 Power Programming with VBA сегодня!
Дополнительные советы по Excel (лента)
Общие сведения об автозаполнении ячеек
Автоматическое заполнение формул может быть полезным инструментом при добавлении данных на листы. Было бы еще больше .
Начиная с большой буквы
Изменение заглавных букв в тексте, хотите верьте, хотите нет, обычная задача в Excel. Обычный или нет, но это может раздражать .
Колесо прокрутки мыши не работает при редактировании формул
Использование мыши для выбора ячеек для включения в формулу может быть бесполезным упражнением в некоторых системах. Вот почему.
Подписаться
БЕСПЛАТНАЯ СЛУЖБА. Получайте подобные советы каждую неделю в ExcelTips, бесплатном информационном бюллетене по повышению эффективности. Введите свой адрес и нажмите "Подписаться".
Задумывались ли вы когда-нибудь, хватит ли вам сил пережить первый год?
Цитата дня
Ищете все цитаты в одной книге? Не смотрите дальше. Вот все 365 цитат в одной книге.
Популярные сообщения
Избранные темы
- Как стать счастливым сейчас
- Ежедневное вдохновение
- Жизненные уроки
- Жизнь аудитора BIG 4
- Исключительно развлечение
- Советы по работе с Excel
- Места, которые вы хотели посетить
- Обзоры книг, обязательные к прочтению
- Открытие собственного бизнеса
28 марта 2010 г. — Автор: StockKevin
Как скопировать/вставить сумму выбранных ячеек в Excel
В марте я заказал около трехсот часов работы. Это не мой выбор, это просто характер бизнеса, но я всегда ищу способы быть более эффективным. Как аудитор, есть много черных задач, которые можно устранить с помощью макросов в файлах Excel. Нас интересуют итоги, а копировать набор чисел на другой рабочий лист и суммировать их, чтобы получить нужное число, очень сложно. Обычно, когда вы выделяете набор чисел, общее количество появляется в левом нижнем углу. Я хотел иметь возможность скопировать эту сумму напрямую и вставить ее в отдельную таблицу.
В этом вам поможет макрос.
См. приведенный ниже пятиэтапный процесс:
1) Нажмите Alt + F11 (откроется окно VBE)
2) Выберите "Модуль" в меню "Вставка" и вставьте приведенный ниже код.
Sub mySum()
Dim MyDataObj As New DataObject
MyDataObj.SetText Application.Sum(Selection)
MyDataObj.PutInClipboard
End Sub
3) Выберите «Ссылки» в «Меню инструментов» и убедитесь, что выбрана «Библиотека объектов Microsoft Forms 2.0». Если его нет в списке, нажмите «Обзор» и выберите «Fm20.dll»
4) Закройте окно VBE
5) Нажмите Alt + F8, выберите "mySum", нажмите "Параметры" и установите сочетание клавиш.
Помните, когда в Excel появилась функция автовычисления, когда вы могли выбрать группу ячеек, и сумма этих ячеек волшебным образом появлялась в строке состояния?
Вскоре после того, как я начал использовать AutoCalulate, я обнаружил, что мне часто нужно использовать СУММУ выделенных ячеек в другом месте. Мне было интересно, есть ли в Excel функция, о которой я не знал, которая позволила бы мне скопировать СУММУ этих выбранных ячеек в другую ячейку. К сожалению, ответ был НЕТ.
В то время я уже несколько лет использовал макросы. Макросы позволили мне делать в Excel многие, казалось бы, невозможные вещи, поэтому я подумал, что решение может заключаться в программировании на VBA.
Ниже я написал макрос, который вычисляет СУММУ всех выбранных видимых ячеек (т. е. исключает ячейки в скрытых или отфильтрованных строках) и сохраняет ее в буфере обмена. Затем вы можете просто вставить это значение в другую ячейку, в электронное письмо или где-нибудь еще. Я до сих пор использую этот макрос несколько раз в неделю. Это, конечно, лучше, чем пытаться «запомнить» общее количество и ввести его в ячейку.
Ниже приведены шаги, которые необходимо выполнить, чтобы сделать эту функцию доступной для вас.
Отказ от ответственности. Следующие инструкции предоставляются без каких-либо гарантий относительно их удобства использования или производительности. Хотя я приложил все усилия, чтобы убедиться, что они будут работать, как описано, я не могу гарантировать, что они будут работать для любой возможной конфигурации компьютера.
Шаг 1. Добавьте ссылку на DataObject
Этот макрос требует использования объекта с именем DataObject . Чтобы использовать его, вам нужно добавить ссылку на него в VBA. Не пугайтесь, если вы не знаете, что все это значит. Вы просто нажимаете ALT+F11, чтобы открыть редактор Visual Basic, а затем в меню «Инструменты» выбираете «Ссылки». Убедитесь, что «Библиотека объектов Microsoft Forms 2.0» находится в списке доступных ссылок. Если нет, нажмите «Обзор» , выберите FM20.DLL , нажмите «Открыть» и «ОК» .
Кроме того, временная вставка пользовательской формы ( Insert , UserForm ) в проект VBA автоматически добавляет ссылку на библиотеку объектов Microsoft Forms 2.0.
Шаг 2. Добавьте этот макрос в свою личную книгу макросов
Если вы уже используете личную книгу макросов, в редакторе Visual Basic откройте модуль кода в PERSONAL.XLSB и вставьте следующий код в окно кода .
Sub CopySUM()
Dim DataObj As New MSForms.DataObject
При ошибке GoTo BailOut
DataObj.SetText Application.Sum(Selection.SpecialCells(xlCellTypeVisible))
DataObj. PutInClipboard
BailOut:
End Sub
Если у вас нет личной книги макросов, прочтите этот совет, чтобы получить инструкции о том, как создать личную книгу макросов, а затем вернитесь к шагу 3.
Шаг 3. Добавьте кнопку на панель быстрого доступа для запуска макроса
1) Щелкните правой кнопкой мыши ленту и выберите "Настроить панель быстрого доступа" ;
2) В раскрывающемся списке "Выбрать команды из" выберите "Макросы" ;
3) Выберите макрос (CopySum) из списка и нажмите кнопку Добавить>>;
4) Щелкните имя макроса в правой части диалогового окна "Параметры Excel" и нажмите кнопку "Изменить";
5) Выберите символ, который будет использоваться для запуска макроса из QAT, измените «Отображаемое имя» на CopySUM и нажмите «ОК»;
Шаг 4. Проверьте макрос CopySUM
1) Введите несколько чисел на листе;
2) Выберите числа и обратите внимание на СУММУ в строке состояния;
3) Скрыть некоторые строки;
4) Снова выберите ячейки и обратите внимание, что сумма СУММ в строке состояния не включает скрытые значения;
5) Нажмите кнопку CopySUM на QAT, чтобы скопировать сумму выбранных ячеек в буфер обмена;
6) Наконец, выберите пустую ячейку и нажмите CTRL+V, чтобы вставить (или щелкните правой кнопкой мыши, Вставить).
Вы бы поверили мне, если бы я сказал, что
вы можете УДВОИТЬ свою производительность в Excel?
Как насчет ТРОЙНОЙ ? или УВЕЛИЧИТЬ это? или БОЛЬШЕ ?
Если бы я показал вам совет, как вы можете выполнить задачу в Excel за пять секунд, на что обычно у вас уходит от 20 минут до часа или больше, как бы вы это назвали? Продуктивность на стероидах?
<р>. и это только ОДИН совет!" Дайте мне 10 или 15 минут каждую неделю, и я покажу вам, как регулярное изучение нескольких моих экономящих время "Советов по электронным таблицам от пользователя Excel" покажется почти легким, НО быстро поможет вам ПОВЫСИТЬ вашу производительность. в Excel за гранью вашего воображения», — Фрэнсис Хейс, The Excel Addict
Получите БЕСПЛАТНО мой еженедельный информационный бюллетень
"Советы по таблицам от пользователя Excel"
Сейчас его читают более 35 000 пользователей Excel по всему миру
Во время семинара по Excel в Тампе возник вопрос: было бы здорово, если бы вы могли копировать статистику из строки состояния в буфер обмена для последующей вставки в диапазон?
Я надавил на человека, который задал вопрос, как именно должна работать паста. Конечно, вы не можете вставить статистику сразу, потому что у вас выделена куча важных ячеек. Вам придется подождать, выбрать другой пустой диапазон электронной таблицы, вставить (как в Ctrl+V ), и статистика появится в диапазоне из 6 строк на 2 столбца. Человек, задавший вопрос, предположил, что это будут статические значения.
Я не пытался ответить на вопрос во время семинара, потому что знал, что это может быть немного сложно.
Но недавно я запустил макрос, чтобы посмотреть, можно ли это сделать. Моя идея состояла в том, чтобы создать длинную текстовую строку, которую можно было бы вставить. Чтобы элементы отображались в двух столбцах, текстовая строка должна иметь метку для столбца 1 (Сумма), а затем табуляцию и значение для столбца 2. Затем вам потребуется возврат каретки, метка для строка 2, столбец 1, затем еще одна вкладка, значение и т. д.
Я знал, что Application.WorksheetFunction — отличный способ вернуть результаты функций Excel в VBA, но он не поддерживает все 400+ функций Excel. Иногда, если в VBA уже есть аналогичная функция (LEFT, RIGHT, MID), Application.WorksheetFunction не будет поддерживать эту функцию. Я запустил VBA с помощью Alt+F11 , отобразил панель Immediate с помощью Ctrl+G , а затем набрал несколько команд, чтобы убедиться, что все шесть функций строки состояния поддерживаются. К счастью, все шесть возвращаемых значений совпадали с тем, что появлялось в строке состояния.
Чтобы сделать макрос короче, можно присвоить Application.WorksheetFunction переменной:
Затем, позже в макросе, вы можете просто ссылаться на WF.Sum(Selection) вместо того, чтобы снова и снова вводить Application.WorksheetFunction.
Что такое код ASCII для вкладки?
Я начал создавать текстовую строку. Я выбрал переменную MS для MyString.
Это тот момент, когда мне понадобился символ табуляции. Я достаточно гик, чтобы знать несколько символов ASCII (10 = перевод строки, 13 = возврат каретки, 32 = пробел, 65 = A, 90 = Z), но я не мог вспомнить табуляцию. Когда я собирался отправиться в Bing, чтобы найти его, я вспомнил, что вы можете использовать vblf в своем коде для перевода строки или vbcr в своем коде для возврата каретки, поэтому я набрал vbtab в нижнем регистре. Затем я перешел на новую строку, чтобы позволить Excel VBA использовать слова, которые он понимает, с заглавной буквы. Я надеялся, что vbtab наберет заглавную букву, и действительно, строка стала заглавной, указывая на то, что VBA собирался дать мне символ табуляции.
Если вы вводите код VBA строчными буквами, при переходе на новую строку вы увидите, что все правильно написанные слова начинаются с заглавной буквы где-то в слове. На изображении ниже vblf, vbcr, vbtab известны vba и записываются с заглавной буквы после перехода на новую строку. Однако то, что я придумал, vbampersand, не известно VBA, поэтому оно не пишется с заглавной буквы.
На данный момент нужно было объединить 6 меток и 6 значений в одну длинную строку. Помните, что в приведенном ниже коде знак _ в конце каждой строки означает, что строка кода продолжается на следующей строке.
После объединения всех меток и значений мне захотелось полюбоваться своей работой, поэтому я отобразил результат в MsgBox. Я запустил код, и он прекрасно сработал:
Я думал, что дома свободен. Если бы я мог просто добавить MS в буфер обмена, я мог бы начать записывать Podcast 1894. Может быть, MS.Copy поможет?
К сожалению, это было не так просто. MS.Copy не является допустимой строкой кода.
Итак, я пошел в Google и искал "Excel VBA Copy Variable to Clipboard". Одним из лучших результатов стал этот пост на доске сообщений MrExcel. В этом посте мои старые друзья Хуан Пабло и НатеО пытались помочь ОП. Настоящая подсказка, однако, заключалась в том, что Хуан Пабло предложил использовать некоторый код с сайта Excel MVP Чипа Пирсона. Я нашел эту страницу, на которой объяснялось, как поместить переменную в буфер обмена.
Чтобы добавить что-либо в буфер обмена, сначала нужно перейти в меню "Инструменты" окна VBA и выбрать "Ссылки". Сначала вы увидите несколько ссылок, отмеченных по умолчанию. Библиотека Microsoft Forms 2.0 не будет проверена. Вам нужно найти его в очень длинном списке и добавить. К счастью для меня, он был на первой странице выбора, где его показывает зеленая стрелка. Как только вы поставите галочку рядом со ссылкой, она переместится наверх.
Код чипа не будет работать, если вы не добавите ссылку, поэтому не пропускайте указанный выше шаг!
Добавив ссылку, завершите макрос, используя код Чипа:
Перед записью подкаста я проверил, работает ли он. Конечно же, когда я запустил макрос, затем выбрал новый диапазон и нажал Ctrl+V для вставки, буфер обмена был очищен до диапазона 6 строк x 2 столбца.
Ура! Я подготовил заставку для эпизода в PowerPoint, включил Camtasia Recorder и записал все вышеперечисленное. Но. Когда я собирался показать заключительные титры, меня охватило щемящее чувство. Этот макрос вставлял статистику как статические значения. Что делать, если базовые данные изменились? Разве вы не хотите, чтобы вставленный блок обновлялся? В подкасте была долгая пауза, во время которой я обдумывал, что делать. Наконец, я щелкнул значок Camtasia Pause Recording и пошел посмотреть, смогу ли я поместить формулу в строку MS и правильно ли она будет вставлена. Конечно, так оно и было. Я даже не доделал макрос полностью или не сделал не одного теста, когда снова включил диктофон и рассказал об этом макросе. В подкасте я предположил, что это никогда не сработает для несмежных выборок, но в более позднем тестировании это работает. Вот макрос для вставки в виде формул:
После публикации видео постоянный зритель Майк Флисс спросил, есть ли способ создать формулы, которые будут постоянно обновляться, чтобы отображать статистику для любого выбранного диапазона. Для этого потребуется макрос Worksheet_SelectionChange, который будет постоянно обновлять именованный диапазон в соответствии с выбором. Хотя это крутой трюк, он заставляет макрос запускаться каждый раз, когда вы перемещаете указатель ячейки, и это будет постоянно очищать стек отмены. Таким образом, если вы используете этот макрос, его нужно добавить в каждую панель кода рабочего листа, где вы хотите, чтобы он работал, и вам придется жить без отмены на этих рабочих листах.
Во-первых, в Excel щелкните правой кнопкой мыши вкладку листа и выберите "Просмотреть код". Затем вставьте этот код.
Вернуться в Excel. Выберите новую ячейку и введите формулу =СУММ(ВыбранныеДанные) . Сначала вы получите циклическую ссылку. Но затем выберите другой диапазон числовых ячеек, и сумма только что созданной формулы обновится.
Выберите новый диапазон, и формула обновится:
Для меня большим открытием стало то, как скопировать переменную в VBA в буфер обмена.
Если вы хотите поэкспериментировать с книгой, заархивированную версию можно скачать отсюда.
Читайте также: