Excel solution что это
Visual Studio предоставляет шаблоны проектов, которые можно использовать для создания настроек на уровне документа и надстроек VSTO для Microsoft Office Excel. Эти решения можно использовать для автоматизации Excel, расширения функциональных возможностей Excel и настройки пользовательского интерфейса Excel. дополнительные сведения о различиях между настройками уровня документа и надстройками VSTO см. в разделе общие сведения о разработке Office solutions (VSTO).
Применимо к: Сведения в этом разделе применимы к - проектам уровня документа и к добавлению VSTO - в проектах для Excel. Дополнительные сведения см. в разделе доступность функций по типам приложений Office и проектов.
Интересуетесь разработкой решений, расширяющих возможности Office на нескольких платформах? Ознакомьтесь с новой моделью надстроек Office. Надстройки Office имеют небольшой объем по сравнению с надстройками и решениями VSTO, и их можно создавать с помощью практически любой технологии веб-программирования, такой как HTML5, JavaScript, CSS3 и XML.
В данном подразделе содержатся следующие сведения.
Автоматизация Excel
Объектная модель Excel предоставляет различные типы, которые можно использовать для автоматизации Excel. Например, можно программно создавать диаграммы, форматировать листы и задавать значения диапазонов и ячеек. дополнительные сведения см. в статье общие сведения об объектной модели Excel.
При разработке своих решений Excel в Visual Studio можно также использовать ведущие элементы и элементы управления ведущего приложения . Данные элементы являются объектами, которые расширяют некоторые часто используемые объекты в объектной модели Excel, например объекты Worksheet и Range . Расширенные объекты ведут себя как объекты Excel, на которых они основаны, но добавляют объектам дополнительные события и возможности по привязке данных. дополнительные сведения см. в разделе автоматизация Excel с помощью расширенных объектов.
Разработка настроек уровня документа для Excel
Настройка на уровне документа для Microsoft Office Excel состоит из сборки, связанной с конкретной книгой. Как правило, сборка расширяет книгу посредством настройки пользовательского интерфейса и автоматизации Excel. В отличие от надстройки VSTO, которая связана с самим приложением Excel, функциональные возможности, реализуемые в настройке, доступны только в том случае, когда соответствующая книга открыта в Excel.
чтобы создать проект настройки на уровне документа для Excel, используйте шаблоны проектов Excel книги или Excel шаблона в диалоговом окне New Project (Visual Studio). дополнительные сведения см. в разделе инструкции. создание проектов Office в Visual Studio.
Дополнительные сведения о работе настроек уровня документа см. в разделе Архитектура настроек на уровне документа.
модель программирования настройки Excel
При создании проекта на уровне документа для Excel Visual Studio создает несколько классов, которые служат базой для вашего решения: ThisWorkbook , Sheet1 , Sheet2 и Sheet3 . Эти классы представляют книгу и листы, связанные с решением, а также отправную точку для написания кода.
Дополнительные сведения о созданных классах и других функциях, которые можно использовать в проекте уровня документа, см. в разделе Program настроек на уровне документа.
разработка надстроек VSTO для Excel
Надстройка VSTO для Microsoft Office Excel состоит из сборки, загружаемой в Excel. Как правило, сборка расширяет Excel посредством настройки пользовательского интерфейса и автоматизации Excel. в отличие от настройки на уровне документа, связанной с определенной книгой, функциональные возможности, реализуемые в VSTO надстройке, не ограничиваются ни одной книгой.
чтобы создать VSTO проекта надстройки для Excel, используйте шаблоны проектов Excel книги или шаблона Excel в диалоговом окне создание Project Visual Studio. дополнительные сведения см. в разделе инструкции. создание проектов Office в Visual Studio.
Общие сведения о работе надстроек VSTO см. в разделе Architecture of VSTO Add-ins.
Excel Модель программирования надстроек
При создании проекта надстройки VSTO Excel Visual Studio создает класс с именем ThisAddIn , который служит базой для вашего решения. Этот класс представляет отправную точку для написания собственного кода, а также предоставляет объектную модель Excel для надстройки VSTO.
дополнительные сведения о ThisAddIn классе и других функциях Visual Studio, которые можно использовать в VSTO надстройке, см. в разделе программы VSTO надстройки.
Настройка пользовательского интерфейса Excel
Для настройки пользовательского интерфейса Excel можно использовать несколько способов. Некоторые параметры доступны для всех типов проектов. Также есть параметры, доступные только для надстроек VSTO или настроек на уровне документа.
Параметры для всех типов проектов
В следующей таблице перечислены параметры настройки, доступные для настроек на уровне документа и надстроек VSTO.
Параметры для настроек уровня документа
В следующей таблице перечислены параметры настройки, доступные только для настроек на уровне документа.
Параметры для надстроек VSTO
В следующей таблице перечислены параметры настройки, доступные только для надстроек VSTO.
Visual Studio provides project templates you can use to create document-level customizations and VSTO Add-ins for Microsoft Office Excel. You can use these solutions to automate Excel, extend Excel features, and customize the Excel user interface (UI). For more information about the differences between document-level customizations and VSTO Add-ins, see Office solutions development overview (VSTO).
Applies to: The information in this topic applies to document-level projects and VSTO Add-in projects for Excel. For more information, see Features available by Office application and project type.
Interested in developing solutions that extend the Office experience across multiple platforms? Check out the new Office Add-ins model. Office Add-ins have a small footprint compared to VSTO Add-ins and solutions, and you can build them by using almost any web programming technology, such as HTML5, JavaScript, CSS3, and XML.
This topic provides the following information:
Automate Excel
The Excel object model exposes many types that you can use to automate Excel. For example, you can programmatically create charts, format worksheets, and set the values of ranges and cells. For more information, see Excel object model overview.
When developing Excel solutions in Visual Studio, you can also use host items and host controls in your solutions. These are objects that extend certain commonly used objects in the Excel object model, such as the Worksheet and Range objects. The extended objects behave like the Excel objects they are based on, but they add additional events and data binding capabilities to the objects. For more information, see Automate Excel by using extended objects.
Develop document-level customizations for Excel
A document-level customization for Microsoft Office Excel consists of an assembly that is associated with a specific workbook. The assembly typically extends the workbook by customizing the UI and by automating Excel. Unlike a VSTO Add-in, which is associated with Excel itself, functionality that you implement in a customization is available only when the associated workbook is open in Excel.
To create a document-level customization project for Excel, use the Excel workbook or Excel template project templates in the New Project dialog box of Visual Studio. For more information, see How to: Create Office projects in Visual Studio.
For more information about how document-level customizations work, see Architecture of document-level customizations.
Excel customization programming model
When you create a document-level project for Excel, Visual Studio generates several classes that are the foundation of your solution: ThisWorkbook , Sheet1 , Sheet2 , and Sheet3 . These classes represent the workbook and worksheets that are associated with your solution, and they provide a starting point for writing your code.
For more information about these generated classes and other features you can use in a document-level project, see Program document-level customizations.
Develop VSTO Add-ins for Excel
A VSTO Add-in for Microsoft Office Excel consists of an assembly that is loaded by Excel. The assembly typically extends Excel by customizing the UI and by automating Excel. Unlike a document-level customization, which is associated with a specific workbook, functionality that you implement in a VSTO Add-in is not restricted to any single workbook.
To create a VSTO Add-in project for Excel, use the Excel workbook or Excel template project templates in the New Project dialog box of Visual Studio. For more information, see How to: Create Office projects in Visual Studio.
For general information about how VSTO Add-ins work, see Architecture of VSTO Add-ins.
Excel Add-in programming model
When you create an Excel VSTO Add-in project, Visual Studio generates a class, called ThisAddIn , which is the foundation of your solution. This class provides a starting point for writing your code, and it also exposes the object model of Excel to your VSTO Add-in.
For more information about the ThisAddIn class and other Visual Studio features you can use in a VSTO Add-in, see Program VSTO Add-Ins.
Customize the user interface of Excel
There are several different ways to customize the user interface of Excel. Some options are available to all project types, and other options are available only to VSTO Add-ins or document-level customizations.
Options for all project types
The following table lists customization options that are available to both document-level customizations and VSTO Add-ins.
Options for document-level customizations
The following table lists customization options that are available only to document-level customizations.
Options for VSTO Add-ins
The following table lists customization options that are available only to VSTO Add-ins.
Благодаря применению Microsoft Office в качестве внешнего интерфейса для решений, можно эффективно использовать привычные пользовательские интерфейсы и инструменты Microsoft Office, например, функции обработки текста в Word, функции анализа данных в Excel и функции управления электронной почтой в Outlook. В Visual Studio можно разрабатывать решения для настройки приложений Office и добавления конкретных функций, необходимых для бизнес-процессов. Например, можно включить Word в генератор контрактов, собирающий контракты на основе уже существующих частей, которые можно сделать редактируемыми или нередактируемыми. С помощью Excel можно создать лист автоматической разработки бюджета, настраиваемый для различных проектов. Пользователи также могут применять решения Office в автономном режиме, что делает сложные решения более удобными, чем решения, построенные на основе веб-архитектуры.
В этой статье содержится обзор типов решений Office, которые можно создавать с помощью шаблонов набора средств Visual Studio для Office (VSTO), доступных в средствах разработки решений на базе Office в Visual Studio. общие сведения о разработке с помощью Office см. в центре разработчиков Office.
выбор типа проекта Office
ВVisual Studio имеются следующие типы шаблонов проектов для разработки приложений Office на основе VSTO.
Настройки на уровне документа , связанные с конкретным документом.
VSTO Add-ins связаны с самим приложением.
Чтобы решить, какой из этих типов проектов наилучшим образом подходит для вашего решения, определите, должен ли ваш код выполняться только в том случае, если открыт конкретный документ, или код должен быть доступен всегда, когда работает приложение. дополнительные сведения о шаблонах проектов см. в разделе Office общие сведения о шаблонах проектов.
Типы проектов, которые можно создавать, зависят от того, какие приложения Office установлены на компьютере разработчика. дополнительные сведения см. в разделе функции, доступные по Office приложении и типу проекта.
Настройки уровня документа.
Настройки на уровне документа состоят из сборки, которая связана с одиночным документом, книгой или шаблоном в Microsoft Office Word или Microsoft Office Excel. Сборка загружается при открытии связанного документа. Возможности создаваемых настроек доступны только в том случае, если связанный документ открыт. Настройки не могут вносить изменения на уровне приложения, например, отображать новый пункт меню или вкладку на ленте, если открыт любой документ.
Visual Studio содержит инструменты для создания настроек на уровне документа. Документ, подлежащий настройке, размещается в виде рабочей области конструирования в Visual Studio, что позволяет проектировать документ путем перетаскивания в него элементов управления. В проектах уровня документа доступны многие другие функции Visual Studio , например, элементы управления Windows Forms, привязка данных перетаскиванием и встроенный отладчик.
Дополнительные сведения о настройках см. в следующих статьях:
Надстройки VSTO
Надстройки VSTO состоят из сборки, которая связана с приложением Microsoft Office. Как правило, надстройка VSTO начинает работать при запуске связанного приложения, хотя пользователи также могут загружать надстройки VSTO после запуска приложения. Создаваемые функции в надстройках VSTO доступны самому приложению, независимо от того, какие документы открыты.
Visual Studioвключает средства, помогающие создавать VSTO надстройки. проекты надстроек включают автоматически создаваемый класс, представляющий надстройку VSTO. Этот класс предоставляет свойства и события, которые можно использовать для доступа к объектной модели ведущего приложения и выполнения кода, когда надстройка VSTO загружена и завершила работу. В проектах надстроек VSTO доступны многие другие функции Visual Studio , например Windows Forms и встроенный отладчик.
Дополнительные сведения о надстройках VSTO см. в следующих статьях.
автоматизация Office приложений с помощью основных сборок взаимодействия
Вы можете программным образом включить функции приложения Office в решение путем написания кода, который обращается к объектной модели приложения. Объектные модели — это схема расположения классов, которые предоставляют функциональные возможности с помощью различных свойств и методов. Объектные модели для приложений Office отличаются друг от друга.
Чтобы воспользоваться объектной моделью приложения Office из решения, созданного с помощью средств разработки решений на базе Office в Visual Studio, необходимо применить основную сборку взаимодействия (PIA) для приложения. Основная сборка взаимодействия позволяет управляемому коду вашего решения взаимодействовать с основанной на COM объектной моделью приложения Office.
Дополнительные сведения об использовании основных сборок взаимодействия в решениях VSTO для Office см. в следующих статьях:
запуск Office решений Microsoft VSTO на компьютерах конечных пользователей
При создании решения VSTO для Office следует учитывать, как требования к развертыванию могут повлиять на выбор метода развертывания.
Параметры развертывания
Для развертывания решений, созданных с помощью средств разработки решений на базе Office в Visual Studioиспользуйте ClickOnce или установщик Windows. Развертывание с помощью ClickOnce позволяет создавать самостоятельно обновляющиеся решения, которые могут устанавливаться и работать при минимальном участии пользователя. Windows Файлы установщика (.msi) можно легко распространять на компьютеры конечных пользователей или распространять с помощью Systems Management Server (SMS). дополнительные сведения о развертывании решений VSTO Office см. в разделе развертывание решения Office.
Установка необходимых компонентов
Прежде, чем пользователи смогут запустить решение, которые вы создали с помощью средств разработки решений на базе Office в Visual Studio, на их компьютерах должны быть установлены некоторые необходимые компоненты. При развертывании решения с помощью ClickOnce или путем создания файла установщика Windows эти необходимые компоненты можно установить вместе с решением. дополнительные сведения см. в статьях Office solution необходимые условия для развертывания и инструкции. установка необходимых компонентов на компьютерах конечных пользователей для запуска Office решений.
Безопасность
Для обеспечения безопасности решений VSTO для Office Набор средств Visual Studio для Office (cреда выполнения) выполняет ряд проверок безопасности при установке и загрузке решения. Эти проверки включают в себя проверку уровня доверия расположения манифеста развертывания или сертификата, используемого для подписания манифеста развертывания. дополнительные сведения см. в разделе безопасные Office решения.
Даже если вы совсем новичок в Excel и в программировании на VBA, вы можете легко записать макрос и автоматизировать часть своей работы. В этой статье речь я расскажу все, что вам нужно знать, чтобы начать работу с созданием макросов в Excel.
Что такое макрос?
Для начала немного о терминологии.
Макрос - это код, написанный на встроенном в Excel языке VBA (Visual Basic for Application). Макросы могут создаваться как вручную, так и записываться автоматически с помощью так называемого макрорекодера.
Макрорекодер - это инструмент в Excel, который пошагово записывает все что вы выполняете в Excel и преобразует это в код на языке VBA. Макрорекодер создает очень подробный код (как мы увидим позже), который вы сможете при необходимости отредактировать в дальнейшем.
Записанный макрос можно будет запускать неограниченное количество раз и Excel повторит все записанные шаги. Это означает, что даже если вы ничего не знаете о VBA, вы можете автоматизировать некоторые задачи, просто записав свои шаги и затем повторно использовать их позже.
Теперь давайте погрузимся и посмотрим, как записать макрос в Excel.
Отображение вкладки "Разработчик" в ленте меню
Перед тем как записывать макрос, нужно добавить на ленту меню Excel вкладку "Разработчик". Для этого выполните следующие шаги:
- Щелкните правой кнопкой мыши по любой из существующих вкладок на ленте и нажмите «Настроить ленту». Он откроет диалоговое окно «Параметры Excel».
- В диалоговом окне «Параметры Excel» у вас будут параметры «Настроить ленту». Справа на панели «Основные вкладки» установите флажок «Разработчик».
- Нажмите «ОК».
В результате на ленте меню появится вкладка "Разработчик"
Запись макроса в Excel
Теперь давайте запишем очень простой макрос, который выбирает ячейку и вводит в нее текст, например "Excel".
Вот шаги для записи такого макроса:
Поздравляем! Вы только что записали свой первый макрос в Excel. Хотя макрос не делает ничего полезного, но он поможет нам понять как работает макрорекордер в Excel.
Теперь давайте рассмотрим код который записал макрорекодер. Выполните следующие действия, чтобы открыть редактор кода:
Вы увидите, что как только вы нажмете кнопку "Выполнить", текст "Excel" будет вставлен в ячейку A2 и выбрана ячейка A3. Это происходит за миллисекунды. Но на самом деле макрос последовательно выполнил записанные действия.
Примечание. Вы также можете запустить макрос с помощью сочетания клавиш Ctrl + Shift + N (удерживайте клавиши Ctrl и Shift, а затем нажмите клавишу N). Это тот же самый ярлык, который мы назначили макросу при его записи.
Что записывает макрос?
Теперь перейдем к редактору кода и посмотрим что у нас получилось.
Вот шаги по открытию редактора VB в Excel:
- Перейдите на вкладку "Разработчик".
- В группе "Код" нажмите кнопку "Visual Basic".
Вы также можете использовать комбинацию клавиш Alt + F11 и перейти в редактор кода VBA.
Рассмотрим сам редактор кода. Далее коротко опишем интерфейс редактора.
- Панель меню: содержит команды, которые можно использовать во время работы с редактором VB.
- Панель инструментов - похожа на панель быстрого доступа в Excel. Вы можете добавить к ней дополнительные инструменты, которыми часто пользуетесь.
- Окно проектов (Project Explorer) - здесь Excel перечисляет все книги и все объекты в каждой книге. Например, если у нас есть книга с 3 рабочими листами, она появится в Project Explorer. Здесь есть несколько дополнительных объектов, таких как модули, пользовательские формы и модули классов.
- Окно кода - собственно сам код VBA размещается в этом окне. Для каждого объекта, указанного в проводнике проекта, есть окно кода, например, рабочие листы, книги, модули и т. д. В этом уроке мы увидим, что записанный макрос находится в окне кода модуля.
- Окно свойств - вы можете увидеть свойства каждого объекта в этом окне. Я часто использую это окно для обозначения объектов или изменения их свойств.
- Immediate Window (окно предпросмотра) - На начальном этапе оно вам не пригодится. Оно полезно, когда вы хотите протестировать шаги или во время отладки. Он по умолчанию не отображается, и вы можете его отобразить, щелкнув вкладку «View» и выбрав опцию «Immediate Window».
Когда мы записали макрос "ВводТекста", в редакторе VB произошли следующие вещи:
- Был добавлен новый модуль.
- Макрос был записан с именем, которое мы указали - "ВводТекста"
- В окне кода добавлена новая процедура.
Поэтому, если вы дважды щелкните по модулю (в нашем случае модуль 1), появится окно кода, как показано ниже.
Вот код, который записан макрорекодером:
В VBA, любая строка , которая следует за ' (знак апострофа) не выполняется. Это комментарий, который предназначен только для информационных целей. Если вы удалите первые пять строк этого кода, макрос по-прежнему будет работать.
Теперь давайте пробежим по каждой строке кода и опишем что и зачем.
Код начинается с Sub, за которым следует имя макроса и пустые круглые скобки. Sub - сокращение для подпрограммы. Каждая подпрограмма (также называемая процедурой) в VBA начинается с Sub и заканчивается End Sub.
- Range("A2").Select - эта строка выбирает ячейку A2.
- ActiveCell.FormulaR1C1 = «Excel» - эта строка вводит текст "Excel" в активной ячейке. Поскольку мы выбрали ячейку A2 в качестве первого шага, она становится нашей активной ячейкой.
- Range("A3").Select - выбор ячейки A3. Это происходит, когда мы нажимаем клавишу Enter после ввода текста, результатом которого является выбор ячейки A3.
Надеюсь, что у вас есть некоторое базовое понимание того, как записывать макрос в Excel.
Обращаем внимание, что код, записанный через макрорекордер, как правило, не является эффективным и оптимизированным кодом. Макрорекордер часто добавляет дополнительные ненужные действия. Но это не значит, что не нужно пользоваться макрорекодером. Для тех, кто только изучает VBA , макрорекордер может быть отличным способом проанализировать и понять как все работает в VBA.
Абсолютная и относительная запись макроса
Вы уже знаете про абсолютные и относительные ссылки в Excel? Если вы используете абсолютную ссылку для записи макроса, код VBA всегда будет ссылаться на те же ячейки, которые вы использовали. Например, если вы выберете ячейку A2 и введете текст "Excel", то каждый раз - независимо от того, где вы находитесь на листе и независимо от того, какая ячейка выбрана, ваш код будет вводить текст "Excel" в ячейку A2.
Если вы используете параметр относительной ссылки для записи макроса, VBA не будет привязываться к конкретному адресу ячейки. В этом случае программа будет "двигаться" относительно активной ячейки. Например, предположим, что вы уже выбрали ячейку A1, и вы начинаете запись макроса в режиме относительной ссылки. Теперь вы выбираете ячейку A2, вводите текст Excel и нажмите клавишу Enter. Теперь, если вы запустите этот макрос, он не вернется в ячейку A2, вместо этого он будет перемещаться относительно активной ячейки. Например, если выбрана ячейка B3, она переместится на B4, запишет текст "Excel" и затем перейдет к ячейке K5.
Теперь давайте запишем макрос в режиме относительных ссылок:
Макрос в режиме относительных ссылок будет сохранен.
Теперь сделайте следующее.
Как вы заметите, макрос записал текст "Excel" не в ячейки A2. Это произошло, потому что вы записали макрос в режиме относительной ссылки. Таким образом, курсор перемещается относительно активной ячейки. Например, если вы сделаете это, когда выбрана ячейка B3, она войдет в текст Excel - ячейка B4 и в конечном итоге выберет ячейку B5.
Вот код, который записал макрорекодер:
Обратите внимание, что в коде нет ссылок на ячейки B3 или B4. Макрос использует Activecell для ссылки на текущую ячейку и смещение относительно этой ячейки.
Не обращайте внимание на часть кода Range(«A1»). Это один из тех случаев, когда макрорекодер добавляет ненужный код, который не имеет никакой цели и может быть удален. Без него код будет работать отлично.
Что нельзя сделать с помощью макрорекодера?
Макро-рекордер отлично подходит для вас в Excel и записывает ваши точные шаги, но может вам не подойти, когда вам нужно сделать что-то большее.
- Вы не можете выполнить код без выбора объекта. Например, если вы хотите, чтобы макрос перешел на следующий рабочий лист и выделил все заполненные ячейки в столбце A, не выходя из текущей рабочей таблицы, макрорекодер не сможет этого сделать. В таких случаях вам нужно вручную редактировать код.
- Вы не можете создать пользовательскую функцию с помощью макрорекордера. С помощью VBA вы можете создавать пользовательские функции, которые можно использовать на рабочем листе в качестве обычных функций.
- Вы не можете создавать циклы с помощью макрорекордера. Но можете записать одно действие, а цикл добавить вручную в редакторе кода.
- Вы не можете анализировать условия: вы можете проверить условия в коде с помощью макрорекордера. Если вы пишете код VBA вручную, вы можете использовать операторы IF Then Else для анализа условия и запуска кода, если true (или другой код, если false).
Расширение файлов Excel, которые содержат макросы
Когда вы записываете макрос или вручную записываете код VBA в Excel, вам необходимо сохранить файл с расширением файла с поддержкой макросов (.xlsm).
До Excel 2007 был достаточен один формат файла - .xls. Но с 2007 года .xlsx был представлен как стандартное расширение файла. Файлы, сохраненные как .xlsx, не могут содержать в себе макрос. Поэтому, если у вас есть файл с расширением .xlsx, и вы записываете / записываете макрос и сохраняете его, он будет предупреждать вас о сохранении его в формате с поддержкой макросов и покажет вам следующее диалоговое окно:
Если вы выберете "Нет", Excel сохранить файл в формате с поддержкой макросов. Но если вы нажмете "Да", Excel автоматически удалит весь код из вашей книги и сохранит файл как книгу в формате .xlsx. Поэтому, если в вашей книге есть макрос, вам нужно сохранить его в формате .xlsm, чтобы сохранить этот макрос.
When you manually select all columns and apply a transformation (for example, changing the data type) the generated code lists each column name individually – it looks something like this:
I frequently import data from files with varying column names, and / or varying numbers of columns. This makes it impossible to effectively hard code data transformations by column name – if somebody renames any of those columns, then the step above will fail.
So, instead of the generated steps to change each column type, we can use the List.Transform function to apply a transformation to each Column:
This example is about as simple as it gets – you can of course apply a broad range of transformation functions in this way – for example, Text.Trim:
Power Query – How To Paste Code (video)
Power Query is great at solving data transformation and consolidation issues. But there are still loads of Excel users who have never used Power Query yet – and sometimes when they’re given some Power Query code to answer their question, they don’t know what to do with it.
Here’s a short video explaining how to use that Power Query code you’ve been given.
Power Query & Power Pivot
I realise I haven’t posted anything on here in quite a long time. That’s mainly because I’ve spent a long time learning all about Power Excel – and it’s a brave new world!
I used to spend most of my Excel time in the VBA editor. These days, I find I spend all my Excel time using Power Query, and Power Pivot (and I wish Microsoft hadn’t changed their names in Excel 2016!)
Have you learned how to use these power tools, yet? If not, I strongly suggest you take some time to look into them. They make it possible to work with data SO much more easily than in the past, and they’re so much faster to learn and use than VBA / formula solutions.
It’s simple to mash up data from a variety of sources, to reshape it fo fit your requirement, and to join up multiple sources in one view. Creating robust measures in Power Pivot makes it much easier and more reliable for report consumers to modify their views and visualisations, safe in the knowledge that the numbers will stay right, and changing layouts won’t break any of the calculations! They provide a much more transparent solution too, which is easier to share, and to understand.
I’m planning to make some time soon to write some beginner’s guides to Power Query and Power Pivot – in the meantime, this is a great link, explaining some Power Query basics
Using Merged Cells Effectively in Excel
There’s one simple piece of advice for using merged cells effectively in Excel:
NEVER MERGE CELLS
Simple, easy to remember, and effective.
Filter table, then delete filtered rows
It’s a pretty common requirement to filter out some values in an Excel table, then delete those rows from the table. It should be straightforward to do this with a little VBA, but it seems to catch lots of people out! Here’s one simple method:
You just need to change the sheet codename, the table name, the field index number, and the criteria value.
It would be simple to replace the user selected values with variables or parameters, so the code becomes more reusable:
This is a simple example, applying a very simple filter. But it’s usually the method for deleting the filtered rows which catches people out, and this approach makes it easy.
YTZ Dice Game in Excel
Here’s a “Yahtzee” style dice game in Excel; quickly developed, just for a bit of fun. Usual scoring rules apply – roll 5 dice up to 3 times, and score in each of the categories.
Click on dice number to “hold” that die, click on the blank score space to save roll score, then roll again. The high score table will show the 6 highest overall scores.
Excel Book Recommendation
I am often asked to recommend an Excel book. This isn’t always easy, as different people need different things from a book, depending not only on their Excel ability and aspirations, but also their personality and learning preferences.
One consistent recommendation I do give, though, is for John Walkenbach’s Excel Bibles:
Excel 2010 Bible – John Walkenbach – Amazon UK
If you only want to get ONE Excel book, this would be it. There’s a bible for each version of Excel, and they’re an excellent mix of how-to guides, background information and reference material.
Index / Match Formula – Basics
INDEX and MATCH are two of the most powerful and flexible functions in Excel – but they are also two of the least understood. I often meet users who are confident with VLOOKUP and HLOOKUP formulae, but don’t seem to understand INDEX and MATCH. So I thought I’d provide a basic guide to using these functions.
INDEX
This simply returns a value from a list, based on its position in the list.
The INDEX function requires 2 arguments; The first is the list of values, or range containing the list of values. The second argument is the position in that list from which to return a value.
Example: Who is the 4th name in this list? We can use INDEX to tell us:
Which returns “Roger”.
MATCH
Now, the MATCH function does the exact opposite of INDEX: it returns the position in a list where a specific value occurs.
The MATCH function requires 3 arguments; The first is the value we want to find, the second is the list (or range containing the list) that we want to look in, and the third argument specifies whether we want an exact match, or the next lowest / highest.
Example: Which position is Helen in, in our example data:
This formula returns the answer 3.
Note the third argument – 0 – which tells Excel we want an EXACT match.
INDEX / MATCH as a LOOKUP
These functions are each useful on their own. But when we combine them, they become even more powerful. Let’s look at the example data again, and ask the question:
Who drives the Hyundai?
Now, if the columns were reversed, you could simply use a VLOOKUP, to return the Name based on the Car value. So, you could restructure your data. Or you could add a helper column, to repeat the name in column C, and then use a VLOOKUP.
But it’s not always possible or practical to restructure a workbook, and it’s certainly not efficient to duplicate data. What we really want is a “left-looking” VLOOKUP – and this is where INDEX / MATCH can be used so effectively.
We can use MATCH to return the position in the list of Hyundai:
=MATCH(“Hyundai”,B2:B9,0)
This returns 6.
Now we can use the INDEX function to find the sixth name in the list:
=INDEX(A2:A9,6)
Which tells us it’s Ann.
Now we can simply combine the functions in one formula:
=INDEX(A2:A9,MATCH(“Hyundai”,B2:B9,0))
And with one little formula, we get the answer we wanted!
There are more advanced capabilities of both INDEX and MATCH functions, including 2 dimensional arrays, multiple areas, and returning closest values. But this post covers the basic use of the INDEX and MATCH functions in Excel.
Eurovision Song Contest 2014
Just a quick lighthearted post to share a scorecard table for the 2014 Eurovision Song Contest:
Recent Posts
Recent Comments
Archives
Categories
Links
Читайте также: