Автоматизация рутины в Microsoft Excel при помощи VBA / Хабр

Приветствую всех.

В этом посте я расскажу, что такое VBA и как с ним работать в Microsoft Excel 2007/2010 (для более старых версий изменяется лишь интерфейс — код, скорее всего, будет таким же) для автоматизации различной рутины.

VBA (Visual Basic for Applications) — это упрощенная версия Visual Basic, встроенная в множество продуктов линейки Microsoft Office. Она позволяет писать программы прямо в файле конкретного документа. Вам не требуется устанавливать различные IDE — всё, включая отладчик, уже есть в Excel.

Еще при помощи Visual Studio Tools for Office можно писать макросы на C# и также встраивать их. Спасибо, FireStorm.

Сразу скажу — писать на других языках (C++/Delphi/PHP) также возможно, но требуется научится читать, изменять и писать файлы офиса — встраивать в документы не получится. А интерфейсы Microsoft работают через COM. Чтобы вы поняли весь ужас, вот Hello World с использованием COM.

Поэтому, увы, будем учить Visual Basic.

Итак, поехали. Открываем Excel.

Для начала давайте добавим в Ribbon панель «Разработчик». В ней находятся кнопки, текстовые поля и пр. элементы для конструирования форм.

Появилась вкладка.

Теперь давайте подумаем, на каком примере мы будем изучать VBA. Недавно мне потребовалось красиво оформить прайс-лист, выглядевший, как таблица. Идём в гугл, набираем «прайс-лист» и качаем любой, который оформлен примерно так (не сочтите за рекламу, пожалуйста):

То есть требуется, чтобы было как минимум две группы, по которым можно объединить товары (в нашем случае это будут Тип и Производитель — в таком порядке). Для того, чтобы предложенный мною алгоритм работал корректно, отсортируйте товары так, чтобы товары из одной группы стояли подряд (сначала по

Типу, потом по Производителю).

Результат, которого хотим добиться, выглядит примерно так:

Разумеется, если смотреть прайс только на компьютере, то можно добавить фильтры и будет гораздо удобнее искать нужный товар. Однако мы хотим научится кодить и задача вполне подходящая, не так ли?

Для начала требуется создать кнопку, при нажатии на которую будет вызываться наша програма. Кнопки находятся в панели «Разработчик» и появляются по кнопке «Вставить». Вам нужен компонент формы «Кнопка». Нажали, поставили на любое место в листе. Далее, если не появилось окно назначения макроса, надо нажать правой кнопкой и выбрать пункт «Назначить макрос». Назовём его FormatPrice. Важно, чтобы перед именем макроса ничего не было — иначе он создастся в отдельном модуле, а не в пространстве имен книги. В этому случае вам будет недоступно быстрое обращение к выделенному листу. Нажимаем кнопку «Новый».

И вот мы в среде разработки VB. Также её можно вызвать из контекстного меню командой «Исходный текст»/«View code».

Перед вами окно с заглушкой процедуры. Можете его развернуть. Код должен выглядеть примерно так:

Sub FormatPrice()

End Sub

Напишем Hello World:

Sub FormatPrice()
    MsgBox "Hello World!"
End Sub

И запустим либо щелкнув по кнопке (предварительно сняв с неё выделение), либо клавишей F5 прямо из редактора.

Тут, пожалуй, следует отвлечься на небольшой ликбез по поводу синтаксиса VB. Кто его знает — может смело пропустить этот раздел до конца. Основное отличие Visual Basic от Pascal/C/Java в том, что команды разделяются не ;, а переносом строки или двоеточием (:), если очень хочется написать несколько команд в одну строку. Чтобы понять основные правила синтаксиса, приведу абстрактный код.

Примеры синтаксиса

' Процедура. Ничего не возвращает
' Перегрузка в VBA отсутствует
Sub foo(a As String, b As String)
    ' Exit Sub ' Это значит "выйти из процедуры"
    MsgBox a + ";" + b
End Sub

' Функция. Вовращает Integer
Function LengthSqr(x As Integer, y As Integer) As Integer
    ' Exit Function
    LengthSqr = x * x + y * y
End Function

Sub FormatPrice()
    Dim s1 As String, s2 As String
    s1 = "str1"
    s2 = "str2"
    If s1 <> s2 Then
        foo "123", "456" ' Скобки при вызове процедур запрещены
    End If

    Dim res As sTRING ' Регистр в VB не важен. Впрочем, редактор Вас поправит
    Dim i As Integer
    ' Цикл всегда состоит из нескольких строк
    For i = 1 To 10
        res = res + CStr(i) ' Конвертация чего угодно в String

        If i = 5 Then Exit For
    Next i

    Dim x As Double
    x = Val("1.234") ' Парсинг чисел
    x = x + 10
    MsgBox x

    On Error Resume Next ' Обработка ошибок - игнорировать все ошибки
    x = 5 / 0
    MsgBox x

    On Error GoTo Err ' При ошибке перейти к метке Err
    x = 5 / 0
    MsgBox "OK!"
    GoTo ne

Err:
    MsgBox "Err!"

ne:
    On Error GoTo 0 ' Отключаем обработку ошибок

    ' Циклы бывает, какие захотите
    Do While True
        Exit Do

    Loop 'While True
    Do 'Until False
        Exit Do
    Loop Until False
    ' А вот при вызове функций, от которых хотим получить значение, скобки нужны.

    ' Val также умеет возвращать Integer
    Select Case LengthSqr(Len("abc"), Val("4"))
    Case 24
        MsgBox "0"
    Case 25
        MsgBox "1"
    Case 26
        MsgBox "2"
    End Select

    ' Двухмерный массив.
    ' Можно также менять размеры командой ReDim (Preserve) - см. google
    Dim arr(1 to 10, 5 to 6) As Integer
    arr(1, 6) = 8

    Dim coll As New Collection
    Dim coll2 As Collection
    coll.Add "item", "key"
    Set coll2 = coll ' Все присваивания объектов должны производится командой Set

    MsgBox coll2("key")
    Set coll2 = New Collection
    MsgBox coll2.Count
End Sub

Грабли-1. При копировании кода из IDE (в английском Excel) есь текст конвертируется в 1252 Latin-1. Поэтому, если хотите сохранить русские комментарии — надо сохранить крокозябры как Latin-1, а потом открыть в 1251.

Грабли-2. Т.к. VB позволяет использовать необъявленные переменные, я всегда в начале кода (перед всеми процедурами) ставлю строчку Option Explicit. Эта директива запрещает интерпретатору заводить переменные самостоятельно.

Грабли-3. Глобальные переменные можно объявлять только до первой функции/процедуры. Локальные — в любом месте процедуры/функции.

Еще немного дополнительных функций, которые могут пригодится: InPos, Mid, Trim, LBound, UBound. Также ответы на все вопросы по поводу работы функций/их параметров можно получить в MSDN.

Надеюсь, что этого Вам хватит, чтобы не пугаться кода и самостоятельно написать какое-нибудь домашнее задание по информатике. По ходу поста я буду ненавязчиво знакомить Вас с новыми конструкциями.

В этой части мы уже начнём кодить нечто, что умеет работать с нашими листами в Excel. Для начала создадим отдельный лист с именем result (лист с данными назовём

data). Теперь, наверное, нужно этот лист очистить от того, что на нём есть. Также мы «выделим» лист с данными, чтобы каждый раз не писать длинное обращение к массиву с листами.

Sub FormatPrice()
    Sheets("result").Cells.Clear
    Sheets("data").Activate
End Sub

Работа с диапазонами ячеек

Вся работа в Excel VBA производится с диапазонами ячеек. Они создаются функцией Range и возвращают объект типа Range. У него есть всё необходимое для работы с данными и/или оформлением. Кстати сказать, свойство Cells листа — это тоже Range.

Примеры работы с Range

Sheets("result").Activate
Dim r As Range
Set r = Range("A1")
r.Value = "123"
Set r = Range("A3,A5")
r.Font.Color = vbRed
r.Value = "456"
Set r = Range("A6:A7")
r.Value = "=A1+A3"

Теперь давайте поймем алгоритм работы нашего кода. Итак, у каждой строчки листа data, начиная со второй, есть некоторые данные, которые нас не интересуют (

ID, название и цена) и есть две вложенные группы, к которым она принадлежит (тип и производитель). Более того, эти строки отсортированы. Пока мы забудем про пропуски перед началом новой группы — так будет проще. Я предлагаю такой алгоритм:

1. Считали группы из очередной строки.
2. Пробегаемся по всем группам в порядке приоритета (вначале более крупные)
   
   1. Если текущая группа не совпадает, вызываем процедуру AddGroup(i, name), где i — номер группы (от номера текущей до максимума), name — её имя. Несколько вызовов необходимы, чтобы создать не только наш заголовок, но и всё более мелкие.
3. После отрисовки всех необходимых заголовков делаем еще одну строку и заполняем её данными.

Для упрощения работы рекомендую определить следующие функции-сокращения:

Function GetCol(Col As Integer) As String
    GetCol = Chr(Asc("A") + Col)
End Function

Function GetCellS(Sheet As String, Col As Integer, Row As Integer) As Range
    Set GetCellS = Sheets(Sheet).Range(GetCol(Col) + CStr(Row))
End Function

Function GetCell(Col As Integer, Row As Integer) As Range
    Set GetCell = Range(GetCol(Col) + CStr(Row))
End Function

Далее определим глобальную переменную «текущая строчка»: Dim CurRow As Integer

. В начале процедуры её следует сделать равной единице. Еще нам потребуется переменная-«текущая строка в data», массив с именами групп текущей предыдущей строк. Потом можно написать цикл «пока первая ячейка в строке непуста».

Глобальные переменные

Option Explicit ' про эту строчку я уже рассказывал
Dim CurRow As Integer
Const GroupsCount As Integer = 2
Const DataCount As Integer = 3

FormatPrice

Sub FormatPrice()
    Dim I As Integer ' строка в data
    CurRow = 1
    Dim Groups(1 To GroupsCount) As String
    Dim PrGroups(1 To GroupsCount) As String

    Sheets("data").Activate
    I = 2
    Do While True
        If GetCell(0, I).Value = "" Then Exit Do
        ' ...
        I = I + 1
    Loop
End Sub

Теперь надо заполнить массив Groups:

На месте многоточия

Dim I2 As Integer
For I2 = 1 To GroupsCount
    Groups(I2) = GetCell(I2, I)
Next I2
' ...
For I2 = 1 To GroupsCount ' VB не умеет копировать массивы
    PrGroups(I2) = Groups(I2)
Next I2
I =  I + 1

И создать заголовки:

На месте многоточия в предыдущем куске

For I2 = 1 To GroupsCount
    If Groups(I2) <> PrGroups(I2) Then
        Dim I3 As Integer
        For I3 = I2 To GroupsCount
            AddHeader I3, Groups(I3)
        Next I3
        Exit For
    End If
Next I2

Не забудем про процедуру AddHeader:

Перед FormatPrice

Sub AddHeader(Ty As Integer, Name As String)
    GetCellS("result", 1, CurRow).Value = Name
    CurRow = CurRow + 1
End Sub

Теперь надо перенести всякую информацию в result

For I2 = 0 To DataCount - 1
    GetCellS("result", I2, CurRow).Value = GetCell(I2, I)
Next I2

Подогнать столбцы по ширине и выбрать лист result для показа результата

После цикла в конце FormatPrice

Sheets("Result").Activate
Columns.AutoFit

Всё. Можно любоваться первой версией.

Некрасиво, но похоже. Давайте разбираться с форматированием. Сначала изменим процедуру AddHeader:

Sub AddHeader(Ty As Integer, Name As String)
    Sheets("result").Range("A" + CStr(CurRow) + ":C" + CStr(CurRow)).Merge
    ' Чтобы не заводить переменную и не писать каждый раз длинный вызов
    ' можно воспользоваться блоком With
    With GetCellS("result", 0, CurRow)
        .Value = Name
        .Font.Italic = True
        .Font.Name = "Cambria"
        Select Case Ty
        Case 1 ' Тип
            .Font.Bold = True
            .Font.Size = 16
        Case 2 ' Производитель
            .Font.Size = 12
        End Select
        .HorizontalAlignment = xlCenter
    End With
    CurRow = CurRow + 1
End Sub

Уже лучше:

Осталось только сделать границы. Тут уже нам требуется работать со всеми объединёнными ячейками, иначе бордюр будет только у одной:

Поэтому чуть-чуть меняем код с добавлением стиля границ:

Sub AddHeader(Ty As Integer, Name As String)
    With Sheets("result").Range("A" + CStr(CurRow) + ":C" + CStr(CurRow))
        .Merge
        .Value = Name
        .Font.Italic = True
        .Font.Name = "Cambria"
        .HorizontalAlignment = xlCenter

        Select Case Ty
        Case 1 ‘ Тип
            .Font.Bold = True
            .Font.Size = 16
            .Borders(xlTop).Weight = xlThick
        Case 2 ‘ Производитель
            .Font.Size = 12
            .Borders(xlTop).Weight = xlMedium
        End Select
        .Borders(xlBottom).Weight = xlMedium ‘ По убыванию: xlThick, xlMedium, xlThin, xlHairline
    End With
    CurRow = CurRow + 1
End Sub

Осталось лишь добится пропусков перед началом новой группы. Это легко:

В начале FormatPrice

Dim I As Integer ' строка в  data
CurRow = 0 ' чтобы не было пропуска в самом начале
Dim Groups(1 To GroupsCount) As String

В цикле расстановки заголовков

If Groups(I2) <> PrGroups(I2) Then
    CurRow = CurRow + 1
    Dim I3 As Integer

В точности то, что и хотели.

Надеюсь, что эта статья помогла вам немного освоится с программированием для Excel на VBA. Домашнее задание — добавить заголовки «ID, Название, Цена» в результат. Подсказка: CurRow = 0 CurRow = 1.

Файл можно скачать тут (min.us) или тут (Dropbox). Не забудьте разрешить исполнение макросов. Если кто-нибудь подскажет человеческих файлохостинг, залью туда.

Спасибо за внимание.

Буду рад конструктивной критике в комментариях.

UPD: Перезалил пример на Dropbox и min.us.

UPD2: На самом деле, при вызове процедуры с одним параметром скобки можно поставить. Либо использовать конструкцию Call Foo(«bar», 1, 2, 3) — тут скобки нужны постоянно.

5 полезных функций Excel для начинающих программистов

Рабо­ту в таб­лич­ном редак­то­ре Excel тоже мож­но авто­ма­ти­зи­ро­вать и запро­грам­ми­ро­вать. Вы смо­же­те здо­ро­во помочь сво­им бух­гал­те­рам с отчё­та­ми, мар­ке­то­ло­гам — с ана­ли­зом рекла­мы, а сами научи­тесь раз­би­рать­ся в алго­рит­мах.

Мы раз­бе­рём 5 полез­ных функ­ций Excel. Если вы дума­е­те, что Excel — это толь­ко про циф­ры и таб­лич­ки, то вот вам видео, где в Excel запро­грам­ми­ро­ва­ли насто­я­щую 3D-игру.

Что­бы было про­ще разо­брать­ся, все фор­му­лы и при­ме­ры мы запи­са­ли в Excel-файл, кото­рый мож­но ска­чать и исполь­зо­вать для экс­пе­ри­мен­тов. Каж­дый при­мер — на отдель­ной вклад­ке сни­зу.

В Excel все функ­ции начи­на­ют­ся с сим­во­ла равен­ства «=». Когда таб­лич­ный редак­тор встре­ча­ет в нача­ле стро­ки этот сим­вол, он пони­ма­ет, что потом пой­дёт какая-то спе­ци­аль­ная коман­да, кото­рую нуж­но обра­бо­тать. Что­бы всё рабо­та­ло пра­виль­но, про­ве­ряй­те каж­дый раз, что вы не забы­ли поста­вить этот знак в самом нача­ле.

ЕСЛИ

Это одна из самых важ­ных функ­ций в Excel. Как и в насто­я­щем язы­ке про­грам­ми­ро­ва­ния, она про­ве­ря­ет какое-то усло­вие, и если оно выпол­ня­ет­ся — пишет в ячей­ку что-то одно, а если нет — пишет что-то дру­гое.

В общем виде она выгля­дит так:

=ЕСЛИ(условие; “значение_если_да”;”значение_если_нет”)

В усло­вии может быть что угод­но: срав­не­ние яче­ек, дру­гие фор­му­лы, срав­не­ния и мате­ма­ти­че­ские коман­ды — всё, что вам нуж­но про­ве­рить.

На прак­ти­ке мож­но сде­лать, напри­мер, так: пусть Excel про­ве­ря­ет воз­раст, и пишет доку­мент, кото­рый в этом воз­расте удо­сто­ве­ря­ет лич­ность. До 14 лет это сви­де­тель­ство о рож­де­нии, а после — пас­порт. Для это­го исполь­зу­ем такую коман­ду в ячей­ке:

=ЕСЛИ(C2<14;»Свидетельство о рождении»;»Паспорт»)

Про­грам­ма про­ве­рит, что сто­ит в преды­ду­щей ячей­ке, и если там чис­ло мень­ше 14, то напи­шет «Сви­де­тель­ство о рож­де­нии», а если уже есть 14 лет, то «Пас­порт»:

СЧЁТЕСЛИ и СЧЁТЕСЛИМН

Часто быва­ет нуж­но посчи­тать коли­че­ство строк или запол­нен­ных яче­ек по каким-то пара­мет­рам, напри­мер узнать коли­че­ство работ­ни­ков стар­ше 40 лет, или посчи­тать все стро­ки, где есть сло­во «пере­мен­ная». Для это­го исполь­зу­ют функ­цию СЧЁТЕСЛИ:

= СЧЁТЕСЛИ(диапазон;”условие”)

Диа­па­зон — это груп­па яче­ек, в кото­рых мы будем что-то искать, а усло­вие — это по како­му пара­мет­ру мы решим, что ячей­ка нам под­хо­дит.

Допу­стим мы ведём семей­ный бюд­жет и нам нуж­но узнать, сколь­ко раз у нас были покуп­ки боль­ше 5000 руб­лей в одном чеке. Для это­го исполь­зу­ем такое:

=СЧЁТЕСЛИ(C2:C10;»>=5000″)

Теперь послож­нее. Если нам нуж­но посчи­тать коли­че­ство яче­ек по несколь­ким усло­ви­ям одно­вре­мен­но, то исполь­зу­ют функ­цию СЧЁТЕСЛИМН:

= СЧЁТЕСЛИМН(диапазон;условие;[диапазон1;условие1;диапазон2;условие2;]…)

То, что в пря­мо­уголь­ных скоб­ках — допол­ни­тель­ные усло­вия, их может быть мак­си­мум 127, а сами скоб­ки при этом ста­вить не нуж­но. Обра­ти­те вни­ма­ние что для каж­до­го ново­го усло­вия мож­но задать свой диа­па­зон. Функ­ция про­ве­рит все усло­вия, каж­дое в сво­ём диа­па­зоне, и если все усло­вия одно­вре­мен­но выпол­ня­ет­ся — посчи­та­ет коли­че­ство таких яче­ек.

Для при­ме­ра най­дём все тра­ты боль­ше 5000 в одном чеке, за кото­рые отве­ча­ет Миша:

=СЧЁТЕСЛИМН(C2:C10;»>=5000″;B2:B10;»=Миша»)

Теперь у нас пер­вый диа­па­зон отве­ча­ет за сум­му в чеке, а вто­рой — за име­на, поэто­му и усло­вия у каж­до­го из них раз­ные.

МАКС

Функ­ция воз­вра­ща­ет мак­си­маль­ное зна­че­ние из выбран­но­го диа­па­зо­на:

=МАКС(диапазон_ячеек)

Давай­те исполь­зу­ем её, что­бы най­ти самую доро­гую покуп­ку в бюд­же­те из преды­ду­ще­го при­ме­ра. Для это­го запи­шем её так:

=МАКС(C2:C10)

ПОИСКПОЗ

Поиск пози­ции часто исполь­зу­ют, когда нуж­но най­ти поло­же­ние опре­де­лён­но­го эле­мен­та в таб­ли­це:

=ПОИСКПОЗ(значение;диапазон_просмотра)

Зна­че­ние — то, что нам нуж­но най­ти в таб­ли­це, диа­па­зон про­смот­ра — где имен­но мы будем искать наше зна­че­ние. Если в каче­стве диа­па­зо­на ука­жем стол­бец с циф­ра­ми, то функ­ция вер­нёт номер в этом столб­це, где встре­ча­ет­ся нуж­ная циф­ра.

Давай­те най­дём номер стро­ки с мак­си­маль­ной покуп­кой:

=ПОИСКПОЗ(9000;C2:C10)

Это чис­ло нам пона­до­бит­ся для сле­ду­ю­щей функ­ции

ИНДЕКС

Эта функ­ция воз­вра­ща­ет зна­че­ние кон­крет­ной ячей­ки в ука­зан­ном диа­па­зоне:

=ИНДЕКС(диапазон; номер_строки; [номер_столбца])

Если в диа­па­зон яче­ек вхо­дит толь­ко один стол­бец, доста­точ­но будет ука­зать толь­ко номер стро­ки. Когда в выбран­ном диа­па­зоне полу­ча­ет­ся два столб­ца и боль­ше, мож­но будет выбрать его номер, что­бы точ­но обо­зна­чить ячей­ку.

Попро­бу­ем в нашем при­ме­ре с бюд­же­том най­ти того, кто сде­лал самую боль­шую покуп­ку. Мы уже зна­ем номер стро­ки, в кото­рой запи­са­на самая боль­шая сум­ма, и на этой же стро­ке запи­сан тот, кто эту сум­му потра­тил. Исполь­зу­ем это:

=ИНДЕКС(B1:B10;E10)

Когда Excel встре­тит такую фор­му­лу, он возь­мёт наш диа­па­зон из вто­ро­го столб­ца, затем возь­мёт номер стро­ки с самой боль­шой покуп­кой (она у нас в ячей­ке Е10, нашли в про­шлом при­ме­ре), и опре­де­лит, что самую круп­ную покуп­ку сде­лал Миша.

Что дальше

Вы уже зна­е­те доста­точ­но для того, что­бы вести в Excel свой семей­ный или лич­ный бюд­жет. Исполь­зуй­те наши фор­му­лы и ана­ли­зи­руй­те полу­чен­ные дан­ные. В сле­ду­ю­щей ста­тье мы рас­ска­жем о том, как менять внеш­ний вид яче­ек и пого­во­рим о новых функ­ци­ях.

Vba excel самоучитель для чайников Excelka.ru

VBA для чайников — Камминг С.

Название: VBA для чайников.

Автор: Камминг С.

Эта книга поможет освоить интегрированную среду разработки VBA и научиться программировать в VBA с использованием объектов, их свойств, методов и событий. Обсуждаемые приемы программирования иллюстрируются примерами, которые можно сразу же опробовать на практике. Полученные знания вы сможете применить как для настройки и усовершенствования популярных офисных приложений, включая приложения Office XP, так и для создания собственных приложений.
Книга рассчитана на тех, кто собирается быстро и без лишних усилий научиться программировать для Windows, используя VBA.

Да, это именно та книга, которая необходима для того, чтобы начать освоение VBA (аббревиатура от Visual Basic for Applications, что означает Visual Basic для приложений). Благодаря этой книге вы узнаете об основных принципах программирования в VBA и получите необходимые навыки для создания полезных программ. А самое главное, вы сможете сделать это без лишних усилий.
В книге использовано много примеров, написана она легко и понятно, поскольку, кто знает, сколько скучных томов уже издано в этом мире! Я попытался изложить все самым обычным языком, по возможности исключив из употребления режущий ухо нормального человека специальный жаргон. И с этого момента я начинаю отпускать (иногда не самые лучшие) шуточки, чтобы вам было на что направить свое раздражение.
С другой стороны, обсуждение рассматриваемых вопросов не слишком упрощено, иначе оно было бы лишено всякой ценности. Если отбросить шутки в сторону, то эта книга — полноценное справочное пособие, охватывающее все основные разделы VBA.

Оглавление
Введение
ЧАСТЬ I. НАЧАЛЬНЫЕ СВЕДЕНИЯ О VBA
Глава 1 .Уважайте теорию, теория — это все!
Глава 2. Не пишите программу, когда можно записать макрос
Глава 3. Основы программирования в VBA
Глава 4. Выполнение VBA-программ
Глава 5. Редактор Visual Basic к вашим услугам
ЧАСТЬ II. КУРС ПРОГРАММИРОВАНИЯ НА VBA
Глава 6. Анатомия выдающейся VBA-программы
Глава 7. Хранение и обработка информации
Глава 8. Управление потоком
Глава 9. «Бронированный» программный код: отладка и устранение ошибок
Глава 10. Создание интерактивных VBA-форм
ЧАСТЬ III. ПРАКТИКУЕМСЯ В ПРОГРАММИРОВАНИИ НА VBA
Глава 11. Инструменты встроенного оркестра VBA
Глава 12. Объектно-ориентированное программирование
Глава 13.Тонкости хранения данных: массивы и коллекции
ЧАСТЬ IV. ПРОФЕССИОНАЛЬНАЯ РАБОТА С VBA
Глава 14. VBA для Office
Глава 15. Программирование на VBA в Word
Глава 16- VBA-программирование в Excel
Глава 17. Программирование баз данных
Глава 18. Работа с файлами на диске
Глава 19. Еще о VBA-формах
ЧАСТЬ V. ВЕЛИКОЛЕПНЫЕ ДЕСЯТКИ
Глава 20. Десятка (без трех) эффектных решений с помощью VBA
Глава 21 .Три десятка ресурсов VBA
Предметный указатель

Бесплатно скачать электронную книгу в удобном формате, смотреть и читать:
Скачать книгу VBA для чайников — Камминг С. — fileskachat.com, быстрое и бесплатное скачивание.

Скачать pdf
Ниже можно купить эту книгу по лучшей цене со скидкой с доставкой по всей России. Купить эту книгу

VBA Excel. Начинаем программировать с нуля

Первое знакомство с редактором VBA Excel, создание процедур (подпрограмм) и написание простейшего кода, работающего с переменными и ячейками рабочего листа.

Эта статья предназначена для тех, кто желает научиться программировать в VBA Excel с нуля. Вы увидите, как это работает, и убедитесь, что не все так сложно, как кажется с первого взгляда. Свою первую программу вы напишите за 7 простых шагов.

Знакомство с редактором VBA

1. Создайте новую книгу Excel и сохраните ее как книгу с поддержкой макросов с расширением .xlsm. В старых версиях Excel по 2003 год – как обычную книгу с расширением .xls.
2. Нажмите сочетание клавиш «левая_клавиша_Alt+F11», которое откроет редактор VBA. С правой клавишей Alt такой фокус не пройдет. Также, в редактор VBA можно перейти по ссылке «Visual Basic» из панели инструментов «Разработчик» на ленте быстрого доступа. Если вкладки «Разработчик» на ленте нет, ее следует добавить в настройках параметров Excel.

В левой части редактора VBA расположен проводник проекта, в котором отображены все открытые книги Excel. Верхней строки, как на изображении, у вас скорее всего не будет, так как это – личная книга макросов. Справа расположен модуль, в который записываются процедуры (подпрограммы) с кодом VBA. На изображении открыт модуль листа, мы же далее создадим стандартный программный модуль.

1. Нажмите кнопку «Module» во вкладке «Insert» главного меню. То же подменю откроется при нажатии на вторую кнопку после значка Excel на панели инструментов.

После нажатия кнопки «Module» вы увидите ссылку на него, появившуюся в проводнике слева.

Первая программа на VBA Excel

Добавляем на стандартный модуль шаблон процедуры – строки ее начала и завершения, между которыми мы и будем писать свою первую программу (процедуру, подпрограмму).

1. Откройте стандартный модуль двойным кликом по его ссылке в проводнике. Поместите в него курсор и нажмите кнопку «Procedure. » во вкладке «Insert» главного меню. Та же ссылка будет доступна при нажатии на вторую кнопку после значка Excel на панели инструментов.

В результате откроется окно добавления шаблона процедуры (Sub).

1. Наберите в поле «Name» имя процедуры: «Primer1», или скопируйте его отсюда и вставьте в поле «Name». Нажмите кнопку «OK», чтобы добавить в модуль первую и последнюю строки процедуры.

Имя процедуры может быть написано как на латинице, так и на кириллице, может содержать цифры и знак подчеркивания. Оно обязательно должно начинаться с буквы и не содержать пробелы, вместо которых следует использовать знак подчеркивания.

1. Вставьте внутрь шаблона процедуры следующую строку: MsgBox «Привет» .

Функция MsgBox выводит информационное сообщение с указанным текстом. В нашем примере – это «Привет».

1. Проверьте, что курсор находится внутри процедуры, и запустите ее, нажав клавишу «F5». А также, запустить процедуру на выполнение можно, нажав на треугольник (на изображении под пунктом меню «Debug») или на кнопку «Run Sub/UserForm» во вкладке «Run» главного меню редактора VBA Excel.

Если вы увидели такое сообщение, как на изображении, то, поздравляю – вы написали свою первую программу!

Работа с переменными

Чтобы использовать в процедуре переменные, их необходимо объявить с помощью ключевого слова «Dim». Если при объявлении переменных не указать типы данных, они смогут принимать любые доступные в VBA Excel значения. Комментарии в тексте процедур начинаются со знака «’» (апостроф).

Пример 2
Присвоение переменным числовых значений:

Пример 3
Присвоение переменным строковых значений:

Скопируйте примеры процедур в стандартный модуль и запустите их на выполнение.

Изменение содержимого ячеек

Для обозначения диапазонов, в том числе и отдельных ячеек, в VBA Excel имеется ключевое слово «Range». Ячейке A1 на рабочем листе будет соответствовать выражение «Range(«A1»)» в коде VBA Excel.

Пример 4

Скопируйте процедуру этого примера в стандартный модуль и запустите на выполнение. Перейдите на активный рабочий лист Excel, чтобы увидеть результат.

VBA для Excel: базовый курс

Создавайте макросы и пишите программы на VBA для Excel. Даже базовые знания значительно расширят Ваши возможности использования электронных таблиц.

• Содержание
• Описание
• Комментарии
• Дополнительно

Главная цель курса:

Освоить основы программирования на VBA для Excel. С помощью полученных навыков Вы сможете расширить функциональные возможности Excel, автоматизировать рутинные действия, а также получить доступ к решению принципиально новых задач.

Этот курс о том:

• Как создавать и редактировать макросы.
• Как создавать несложные программы с нуля.
• Как получить доступ из своей программы к объектам Excel (ячейкам, диапазонам, листам… ).

• 32 урока, более 2 часов материала.
• 10 бесплатных уроков
• Курс создавали эксперты работы в программе.
• Курс сделан очень качественно, его приятно проходить.
• Легко воспринимается.
• Интерактивные тренировки, где Вы можете опробовать изучаемые навыки.
• Вы можете скачать Файлы с данными, с помощью которых мы делали этот курс.

Хотите научиться писать макросы для Microsoft Excel?

Тогда вам нужен курс для начинающих по Visual Basic для Excel от компании skill.im. Специально для тех, кто хочет решать более сложные задачи в Excel, мы подготовили интересный самоучитель, который будет полезен как начинающим, которые никогда не занимались программированием, так и людям, которые знакомы с Visual Basic, но хотят усовершенствовать свои знания.

Чем полезен базовый этап по VBA для Excel?

В целом, наш самоучитель состоит из трех разделов: продвинутый, базовый и мастерский. Базовая часть будет полезна тем людям, которые не знакомы с программирования и никогда не писали программ, то есть – для чайников. Остальные этапы удовлетворять запросы более опытных пользователей.

Базовый этап состоит из пяти разделов.

В первом вы узнаете:

• Параметры безопасности Excel и файлов

• Все о вкладке «Разработчик».

Второй шаг мы посвятили знакомству с макросами:

• Как записать и запустить макрос в Excel

• Редактирования макроса для Excel в The Visual Basic Editor

• Знакомство с Visual Basic Editor на Excel

В третьем разделе мы поговорим о процедурах и функциях

• Что такое модуль

• Создание функции для Excel

В четвертом разделе мы начнем программировать на VBA

А в пятом будем работать с объектами Excel.

Стив Каммингс — VBA для чайников

99 Пожалуйста дождитесь своей очереди, идёт подготовка вашей ссылки для скачивания.

Скачивание начинается. Если скачивание не началось автоматически, пожалуйста нажмите на эту ссылку.

Описание книги «VBA для чайников»

Описание и краткое содержание «VBA для чайников» читать бесплатно онлайн.

Эта книга поможет освоить интегрированную среду разработки VBA и научиться программировать в VBA с использование объектов, их свойств, методов и событий. Обсуждаемые приемы программирования иллюстрируются примерами, которые можно сразу же опробовать на практике. Полученные знания вы сможете применить как для настройки и усовершенствования популярный офисных приложений, включая приложения Office XP, так и для создания собственных прилож

НОУ ИНТУИТ | Лекция | Начала программирования в Excel

Предисловие

Эта книга посвящена Excel — основам программирования в Excel, связи Excel с базами данных и другими источниками данных, анализу офисной деятельности, выполняемыми средствами Excel, созданию интерактивных документов в Excel, приданию Web-страницам функциональности Excel. Отдавая должное этому замечательному приложению, я и решил назвать книгу «Мир объектов Excel 2000».

Почему не просто «Мир Excel 2000»? Видимо, потому что я предпочитаю смотреть на все, что происходит в Excel, с объектной точки зрения, рассматривать все происходящее как действия над объектами. Да и описанию самих объектов в этой книге уделяется достаточно много внимания. При этом в круг рассмотрения входят не только объекты самого Excel. Мир объектов Excel более широк. Так или иначе, в рассмотрении появляются общие объекты Office 2000, объекты других приложений, в книге, например, много внимания уделяется связи Excel и Access. Помимо объектов Excel в книге подробно рассматриваются еще две группы объектов — ADO и OWC. Объекты ADO, позволяющие организовать доступ к базам данных, несомненно, входят в мир Excel, поскольку, чаще всего, Excel является тем приложением, где обрабатываются данные, хранимые в различных источниках. Компоненты OWC, обладающие функциональностью Excel , — их объектная модель — это тоже часть мира объектов Excel. Без этих объектов не обойтись, если Ваше решение переносится в интранет или интернет, и Вы захотите создать Web-узел, страницы которого обладают функциональностью Excel документов.

Что же можно найти в этой книге, и кому она предназначена?

Эта книга предназначена тем, кто создает решения на базе Excel. Конечно, в первую очередь речь идет о тех, кто уже использует программирование на VBA и VBScript при создании своих решений. Но я думаю, что и те, кто давно работает в Excel, но еще не использовал программные способы настройки своих решений, могут начать с этой книги, хотя, предупреждаю, требуется определенная программистская подготовка.

Первые две лекции посвящены началам программирования в Excel. Они, надеюсь, доступны опытным пользователям и начинающим программистам. Думаю, что их с интересом прочтут и те, кто занимается обучением программированию в любой среде программирования. Я высказываю и пытаюсь обосновать здесь «крамольную» мысль о том, что Excel является прекрасной средой начального обучения программированию в школе и в вузах. Надеюсь, что и опытные программисты найдут здесь кое-что полезное для себя, особенно, во второй главе, адресованной математикам.

Глава 3 дает описание объектной модели Excel. Подробно рассмотрены все основные действующие лица этой модели — объекты Application, WorkBook, WorkSheet, Chart, Range и другие. Понятно, что при раскрытии этой темы без технических деталей не обойтись, поэтому чтение главы довольно утомительно, но как справочный материал, она, надеюсь, будет весьма полезной.

Базы данных и другие источники данных — это одна из ведущих тем этой книги. Глава 4 «Excel и базы данных» начинает эту тему. В этой же главе дается знакомство с приложением Access, на примере показано, как строится база данных в Access, изучаются связи Excel и Access.

Пятая и шестая главы книги посвящены объектам ADO. Уже то, что этим объектам посвящены две главы, говорит о степени подробности рассмотрения этой темы. Объекты ADO задают интерфейс между самыми разнообразными источниками данных и клиентским приложением, где эти данные обрабатываются. Таким приложением совсем не обязательно должен быть Excel, сфера применения объектов ADO значительно шире. Но Excel это одна из наиболее подходящих кандидатур на роль такого клиентского приложения. Чтение этих глав требует определенной подготовки и ориентированно на более продвинутых программистов.

Седьмая глава книги посвящена разработке интерактивных документов. Она носит в определенном смысле центральный характер. В ней рассматривается весь процесс создания интерактивного документа Excel. Этот документ можно считать примером создаваемого в Excel типичного решения, в котором программный проект играет определяющую роль. Предлагаемое решение использует базу данных Access и объекты ADO для организации связи с базой данных. Здесь же обсуждается и ряд других вопросов, важных в практической работе, начиная от организации интерфейса пользователя до получения печатной формы документа.

Глава 8 посвящена вопросам, возникающим при анализе офисной деятельности. В ней делается попытка сочетать содержательное рассмотрение, например, рассмотрение того, как проводить анализ типа «Что, если …» с рассмотрением средств Excel, используемых в таком анализе. Здесь рассматриваются сводные таблицы и сводные диаграммы, сценарии, методы прогнозирования и многое другое.

В девятой главе подробно обсуждается создание проекта под названием «MasterF». С содержательной точки зрения рассматривается задача оптимального выбора инвестиционных вложений. В проектном решении используется мощный встроенный инструмент Excel — решатель Solver, позволяющий решать сложные оптимизационные задачи. Данный проект демонстрирует построение надстройки над встроенным инструментарием Excel. Полагаю, что он может иметь не только учебную, но и практическую ценность для тех, кто решает подобные задачи.

В главе 10 в рассмотрение вводятся новые объекты, тесно связанные с Excel. В этой главе рассматриваются Office Web Components — компоненты OWC. По моему мнению, эти компоненты являются лучшим продуктом, созданным Microsoft в области компонентного программирования. Они дают возможность добавить функциональность Excel в свои решения без использования самого Excel и других приложений Office 2000. Основное предназначение этих компонент — обеспечение корпоративной работы с документами в интрасетях, что позволяет создавать интерактивные страницы Web-узлов, обладающие функциональностью Excel. При рассмотрении этих компонент большое внимание уделяется программной работе с этими компонентами, возможности программной настройки Web-документов, использующих эти компоненты.

Глава 11 посвящена введению в Web-программирование. Я подробно рассматриваю процесс создания интерактивного Web-документа. Ключевым словом здесь является «интерактивность», поскольку, по большому счету, вся эта книга о том, как придать нужную интерактивность создаваемым документам. Глава носит вполне самостоятельный характер, но, по сути, является естественным продолжением двух тем, рассматриваемых в главах 7 и 10, поскольку с одной стороны речь идет о создании интерактивного документа, с другой — документа Web, страницы которого могут обладать функциональностью Excel. Замечу, что при рассмотрении вопросов создания интерактивного Web-документа, я ограничиваюсь рассмотрением программных продуктов от Microsoft. Так что роль Web-сервера, выполняющего сценарии на серверной стороне, играет IIS — Internet Information Server, соответствующие страницы узла являются ASP-страницами, а обозреватель, выполняющий код на клиентской стороне, это Internet Explorer.

Главу 12 я решил посвятить описанию проектов, выполненных студентами, которым я читал курс «Офисное программирование». Надеюсь, что часть проектов, о которых идет речь в этой главе, будут на сервере, их программный код открыт и доступен для изучения. Работы студентов расширяют множество примеров, рассмотренных в книге, и являются хорошей иллюстрацией возможностей офисного программирования. Все игры ориентированы, в первую очередь, на школьников, но не только на них. Большинство игр относится к классу «развивающих» игр, направленных на развитие логического мышления. Среди игр есть вариации известных игр — Lines, Tetris, Digger, игры-головоломки, известная карточная игра «Подкидной дурак» и другие игры.

Завершая предисловие, хочу сказать, что предлагаемый курс тесно связан с книгами серии «Офисное программирование», вышедшими в издательстве «Русская редакция». Есть явные и неявные ссылки в тексте многих глав:

1. «VBA в Office 2000. Офисное программирование,
2. «Средства разработки VBA-программиста». Офисное программирование,
3. Мир объектов Excel 2000. Средства разработки VBA-программиста.

Хорошо, если читатели знакомы с этими книгами, или прошли курсы по первым двум книгам, но это не обязательное требование. Пройти данный курс можно вполне самостоятельно. Скажу даже, что я старался, по возможности, сделать каждую главу доступной для независимого чтения.

Просьба к читателям. Поскольку курс построен на основе ранее вышедшей книги и практически не подвергался переделке, то возможны «висячие ссылки», имеющие смысл только для книги. Часть материала уже устарела. Постарайтесь быть снисходительными к этим огрехам. Я искренне верю, что основное содержание не потеряло своей ценности и может быть полезным всем, кто так или иначе связан с офисным программированием.

Связаться с автором можно по адресу: [email protected]

Литература по Excel и VBA

Автор: Берндт Г., Каинка Б.

Качество: хорошее

Автор: Гарбер Геннадий

Качество: хорошее

Автор: Слепцова Л.Д.

Качество: отличное

Автор: Джон Уокенбах

Качество: хорошее

Автор: Джон Уокенбах

Качество: хорошее (с примерами файлов)

Автор: Додж М., Стинсон К.

Качество: отличное

Автор: Плакунов М. К.

Качество: хорошее, текстовый слой

Автор: Bill Jelen, Tracy Syrstad

Качество: хорошее

Автор: Джон Уокенбах

Качество: отличное (на английском языке)

Автор: Джон Уокенбах

Качество: отличное (на английском языке)

Список уроков | Автоматизация в Excel

Мои навыки по автоматизации в Excel перешли на новый уровень

Дмитрий, спасибо за тренинг.

Хочу отметить удобный формат тренинга и хорошее качество изложения материала. Тренинг я еще не окончил, но уже чувствую как мои навыки по автоматизации в Excel перешли на новый уровень.

Иван Касаткин о практическом тренинге

Изучала запоем, узнала очень много нового

Спасибо, Дмитрий!

Классный и полезный тренинг, рекомендую всем!

С VBA уже дело имела(изучала методом научного тыка). Курс очень понравился, иногда не знаешь, какие вопросы нужно задавать и мучаешься долго и нудно над элементарными, как оказалось, вещами.

Мне было не очень трудно, но очень интересно, изучала запоем, узнала очень много нового. После тренинга поняла, что мои макросы нужно все переписывать, так как оказалось, я не знаю некоторых очень нужных, базовых вещей.

Мне очень понравилась подача материала: просто, доходчиво, с пояснением нюансов и подводных камней.

Очень полезно видеть отладку кода, поиск ошибок по горячим следам. Мне лично уже пригодилось, облегчило жизнь.

Дмитрий, спасибо за терпение и развернутые ответы на очень тупые вопросы!

Екатерина Пилипец о практическом тренинге

Мне все понравилось, я ожидала меньшего от курса.

Спасибо, Вам, Дмитрий, что Вы в таком режиме вели курс, а не просто выслали материалы и все, что на свои вопросы мы могли получать ответы, что Вы проверяли домашние задания, и мы переделывали, пока совсем не будет ошибок в коде. Мне, правда, тяжеловато осваивать курс, потому как я вообще полный ноль в программировании.

Но мне все понравилось, я ожидала меньшего от курса.

Еще раз спасибо, особенно за отношение к нам, ученикам!!

С уважением, Нина Сакаева.

г. Домодедово

Нина Сакаева о практическом тренинге

…Их визг радости до сих пор у меня стоит в ушах

Я работаю в гос.учреждении экономистом. В глобальном масштабе информация у нас обрабатывается типовыми проектами на мощной технике, а нам сбрасывается полуфабрикат в виде таблиц и текстовых файлов. Небольшие задачи удалось решить с помощью макросов, которые осваивала на бесплатных уроках. Но есть такие, как например одна задача, с которой девчонки возятся 2 дня и при этом друг друга контролируют. Эту работу довелось и мне выполнить. Пришла в ужас: разве можно в наше время и такая страшная рутина, причем за ошибки достается очень серьезно, потому что результат направляется по краю. Поняла, что с помощью макросов ее не решить и вот, удачно попала на Ваши, Дмитрий, курсы. Осенью мне предстоит идти на пенсию и я решила сделать девчонкам подарок. Но когда я позвала их посмотреть на результат написанной программы, их визг радости до сих пор у меня стоит в ушах. Ведь о такой благодарности даже и не мечтала! До осени хочу еще несколько задач запрограммировать. Как же я могу быть не благодарна Вам за хорошие курсы, которые построены грамотно, умно и лаконично. Может и им захочется поучиться. СПАСИБО!!!

Людмила Овсянникова о практическом тренинге

По ночам я сидел и разбирал все скаченные записи

Спасибо Дмитрию, я не знал, как подойти к макросам, но понимал, что без них никак, после второго занятия я решил свою долгую проблему построения баланса в нужном виде, к которой не знал, как подступиться ооочень долго. Ура. Я, находясь в Флориде, научился этому, в режиме он-лайна я смотрел всего лишь 2 вебинара, так как слишком разные часовые пояса, но по ночам я сидел и разбирал все скаченные записи, все равно все понятно. Еще раз спасибо Дмитрию

Роман Жаринов о практическом тренинге

Excel 2007 All-in-One For Dummies Cheat Sheet

2. Software
3. Microsoft Office
4. Excel
5. Excel 2007 All-in-One For Dummies Cheat Sheet

Грег Харви

Excel 2007 полон сочетаний клавиш или горячих клавиш, вы можете использовать для управления данными в электронных таблицах Excel. В Office 2007, частью которого является Excel 2007, появилась команда Ribbon, которая предлагает вкладки и подменю для более интуитивного доступа к командам Excel.Таким образом, у вас есть как минимум два способа доступа к общим командам для работы с файлами, редактирования и просмотра, а также вы можете получить доступ к функциям формул с помощью различных нажатий клавиш и щелчков мышью.

Как получить доступ к стандартным файловым командам в Excel 2007

Excel 2007 — отличный инструмент для создания электронных таблиц, или рабочих листов, , как их называет Excel. В Excel 2007 есть горячие клавиши, а также команда «Лента», представленная в Office 2007, чтобы помочь вам создать новую электронную таблицу, открыть старую, сохранить изменения в той, с которой вы работаете, или выполнить любую команду, обычно доступную в меню «Файл».В следующей таблице показано, как получить доступ к наиболее распространенным файловым функциям:

..

Горячая клавиша	Команда ленты Excel	Функция
Alt + F, N	Кнопка Microsoft Office | Новый	Отображает диалоговое окно «Новая книга», в котором можно открыть пустую книгу или книгу из шаблона
Alt + F, O	Кнопка Microsoft Office | Открыть	Отображает диалоговое окно Открыть, в котором можно выбрать новую книгу Excel , которую нужно открыть для редактирования или печати. ​​
Alt + F, S	Кнопка Microsoft Office | Сохранить	Сохраняет изменения в книге.Когда вы впервые выбираете эту команду для новой книги, Excel отображает диалоговое окно «Сохранить как»
Alt + F, A	Кнопка Microsoft Office | Сохранить как	Отобразите диалоговое окно «Сохранить как», в котором вы можете изменить имя файла , место сохранения файла и формат, в котором файл сохраняется в
Alt + F, P	Кнопка Microsoft Office | Печать	Отображает диалоговое окно «Печать» для отправки текущего рабочего листа, книги или выбранной ячейки на принтер.
Alt + F, D	Кнопка Microsoft Office | Отправить	Отправляет текущую книгу как вложение к электронному письму или отправляет ее по факсу с помощью Интернет-факса
Alt + F, C	Кнопка Microsoft Office | Закрыть	Закрывает текущую книгу без выхода из Excel
Alt + F, I	Кнопка Microsoft Office | Параметры Excel	Отображает диалоговое окно «Параметры Excel», в котором можно изменить настройки программы по умолчанию и изменить кнопки на панели инструментов быстрого доступа
Alt + F, X	Кнопка Microsoft Office | Закройте Excel	Выходит из программы Excel и закрывает все открытые книги после с предложением сохранить их

Как получить доступ к общим командам редактирования в Excel 2007

Иногда вам нужно внести изменения в информацию, которую вы помещаете в свои рабочие листы и книги Excel 2007 (называемые таблицами в мире за пределами Excel).В этом случае вы можете использовать удобные горячие клавиши, встроенные в Excel 2007, или получить доступ к команде через вкладки ленты Excel, новые в Excel 2007. Чтобы вырезать, вставлять и т. Д., Используйте информацию из следующей таблицы:

Горячие клавиши	Команда ленты Excel	Функция
Alt + H, V, P Ctrl + V	На главную | Вставить | Вставить	Вставляет текущий вырезанный или скопированный выделенный фрагмент ячейки или графические объекты на лист
Alt + H, X Ctrl + X	На главную | Огранка	Вырезает выделенную ячейку или выбранные графические объекты из книги и помещает их в буфер обмена Windows
Alt + H, C Ctrl + C	На главную | Копировать	Копирует выделенную ячейку или выбранные графические объекты в буфер обмена Windows
Alt + H, F, P	На главную | Format Painter	Активирует форматирование по образцу
Alt + H, F, O	На главную | Панель запуска диалогового окна буфера обмена	Отображает и скрывает область задач буфера обмена
Alt + H, I, I	На главную | Вставить | Вставить ячейки	Открывает диалоговое окно «Вставка», в котором можно указать направление в , в котором необходимо сместить существующие ячейки, чтобы освободить место для вставленных
Alt + H, I, R	На главную | Вставить | Вставить строки листа	Вставляет пустые строки, равные количеству строк в ячейке выбор
Alt + H, I, C	На главную | Вставить | Вставить столбцы листа	Вставляет пустые столбцы, равные количеству столбцов в выделенной ячейке
Alt + H, I, S Shift + F11	На главную | Вставить | Вставить лист	Вставляет новый лист в книгу
Alt + H, D, D	На главную | Удалить | Удалить ячейки	Открывает диалоговое окно «Удалить», в котором вы можете указать направление в , в котором нужно сдвинуть существующие ячейки, чтобы заменить те, которые были удалены
Alt + H, D, R	На главную | Удалить | Ряды листов	Удаляет строки, равные количеству строк в ячейке выбор
Alt + H, D, C	На главную | Удалить | Колонны листа	Удаляет столбцы, равные количеству столбцов в ячейке выбор
Alt + H, D, S	На главную | Удалить | Лист	Удаляет текущий рабочий лист после предупреждения о потере данных, если лист содержит записи ячеек
Alt + H, E, A	На главную | Очистить | Очистить все	Удаляет содержимое, форматирование и комментарии из выделенной ячейки
Alt + H, E, F	На главную | Очистить | Очистить форматы	Удаляет форматирование выделенной ячейки без удаления содержимого и комментариев
Alt + H, E, C	На главную | Очистить | Очистить содержание	Очищает содержимое выделенной ячейки без удаления форматирования и комментариев
Alt + H, E, M	На главную | Очистить | Очистить комментарии	Удаляет все комментарии в выделенной ячейке без удаления форматирования и содержимого

Как получить доступ к командам общего представления в Excel 2007

Вы можете просматривать рабочие листы, созданные в Excel 2007, с любого количества точек зрения.Вы можете изменить вид так, чтобы видеть — или не видеть — разрывы страниц, линии сетки и диалоговые окна, а также увеличивать лист и видеть, как он будет выглядеть при печати. В следующей таблице приведены комбинации горячих клавиш и команды ленты Excel, чтобы вы могли видеть именно то, что хотите:

Горячие клавиши	Команда ленты Excel	Функция
Alt + W, L	Посмотреть | Нормальный вид	Возвращает рабочий лист к нормальному виду из макета страницы или страницы Предварительный просмотр разрыва
Alt + W, P	Посмотреть | Макет страницы	Помещает рабочий лист в режим макета страницы, показывая страницы разрывы, поля и линейки
Alt + W, I	Посмотреть | Предварительный просмотр разрыва страницы	Перемещает рабочий лист в режим предварительного просмотра разрывов страницы, показывая разрывы страниц , которые можно настроить
Alt + W, E	Посмотреть | Полный экран	Переводит рабочий лист в полноэкранный режим, который скрывает кнопку Microsoft Office, панель быстрого доступа и ленту — нажмите клавишу Esc, чтобы восстановить предыдущий режим просмотра.
Alt + W, V, G	Посмотреть | Сетки	Скрывает и повторно отображает линии сетки строк и столбцов, которые образуют ячейки в области рабочего листа
Alt + W, Y, G	Посмотреть | Увеличить выделение	Увеличение или уменьшение масштаба рабочей области до процента, необходимого для отображения только выделенных ячеек
Alt + W, N	Посмотреть | Новое окно	Вставляет новое окно в текущую книгу
Alt + W, A	Посмотреть | Упорядочить все	Открывает диалоговое окно «Упорядочить», в котором можно выбрать способ отображения окон книги на экране.
Alt + W, F	Посмотреть | Морозильные панели	Открывает раскрывающееся меню «Закрепить области», в котором вы выбираете, как закрепить строки и столбцы в области рабочего листа.
Alt + W, S	Посмотреть | Сплит	Разделяет рабочий лист на четыре панели, используя верхний и левый край курсора ячейки в качестве вертикальной и горизонтальной разделительных линий — нажмите горячие клавиши еще раз, чтобы удалить все панели
Alt + W, H	Посмотреть | Скрыть	Скрывает текущее окно рабочего листа или книги
Alt + W, U	Посмотреть | Показать	Открывает диалоговое окно Показать, где вы можете выбрать окно или книгу для повторного отображения
Alt + W, B	Посмотреть | Посмотреть бок о бок	Помещает два открытых окна или книги одно над другим для сравнения — нажмите горячие клавиши еще раз, чтобы восстановить исходный полные окна
Alt + W, W	Посмотреть | Переключить Windows	Открывает раскрывающееся меню «Переключить Windows», в котором можно выбрать открытое окно или книгу, чтобы активировать

Как получить доступ к общим командам формул в Excel 2007

Возможно, самая полезная и используемая функция в Excel 2007 — это та, которая помогает вам создавать формулы, чтобы вы добавляли, подсчитывали, усредняли и обычно выполняли математические функции с данными в ваши листы Excel.Информация в следующей таблице показывает, какие горячие клавиши нажимать или на вкладке ленты выбрать для доступа к нужной функции формулы:

Горячие клавиши	Команда ленты Excel	Функция
Alt + M, F	Формулы | Мастер функций	Открывает диалоговое окно «Вставить функцию».
Alt + M, U, S	Формулы | AutoSum | Сумма	Выбирает занятый диапазон над курсором ячейки и вставляет формулу СУММ для суммирования диапазона
Alt + M, U, A	Формулы | AutoSum | В среднем	Выбирает занятый диапазон над курсором ячейки и вставляет формулу СРЕДНЕЕ для вычисления среднего общего в диапазоне
Alt + M, U, C	Формулы | AutoSum | Подсчитать числа	Выбирает занятый диапазон над курсором ячейки и вставляет формулу COUNT для подсчета количества значений в диапазоне
Alt + M, I	Формулы | Финансовый	Открывает раскрывающееся меню со списком всех финансовых функций — щелкните имя, чтобы вставить функцию в текущую ячейку
Alt + M, E	Формулы | Дата и время	Открывает раскрывающееся меню, в котором перечислены все функции даты и времени. — щелкните имя, чтобы вставить функцию в текущую ячейку.
Alt + M, N	Формулы | Менеджер имен	Открывает диалоговое окно «Диспетчер имен», в котором отображаются все имена диапазонов в книге и можно добавлять, редактировать и удалять имена.
Alt + M, M, D	Формулы | Определить имя	Открывает диалоговое окно «Новое имя», в котором можно присвоить имя выделенной ячейке или определить новую константу.
Alt + M, S, 1	Формулы | Использование в формуле	Отображает раскрывающееся меню с именами диапазонов в книге, которые можно вставить в текущую формулу, щелкнув
Alt + M, C, 1	Формулы | Создать из выделения	Открывает диалоговое окно «Создание имен из выделенного», в котором вы указываете , какие строки и столбцы использовать при присвоении имен выделенным ячейкам
Alt + M, H Ctrl + ’	Формулы | Показать формулы	Отображает, а затем скрывает все формулы в ячейках рабочего листа
Alt + M, X, A	Формулы | Опции | Автомат	Включает автоматический пересчет обратно на
Alt + M, X, E	Формулы | Опции | Автоматически, кроме таблиц данных	Снова включает автоматический пересчет для всех частей рабочего листа , кроме диапазонов с таблицами данных
Alt + M, X, M	Формулы | Опции | Руководство	Включает ручной пересчет
Alt + M, B F9	Формулы | Рассчитать сейчас	Пересчет формул по всей книге, если ручной пересчет включен
Alt + M, J Shift + F9	Формулы | Расчет листа	Пересчет формул на текущем листе, когда ручной пересчет включен

Об авторе книги

Грег Харви. является автором множества компьютерных книг, самыми последними из которых являются Excel 2007 для чайников, Краткий справочник по Windows Vista для чайников, и Excel Workbook для чайников. Он начал обучение бизнес-пользователей тому, как использовать персональные компьютеры IBM и сопутствующее им компьютерное программное обеспечение, в тяжелые времена DOS, WordStar и Lotus 1-2-3 в середине 80-х годов прошлого века. После работы в ряде независимых учебных фирм, он продолжил обучение на семестровых курсах по электронным таблицам и программному обеспечению для управления базами данных в университете Golden Gate в Сан-Франциско.
Его любовь к обучению переросла в такую ​​же любовь к письму. Для чайников книг, конечно, его самые любимые книги, потому что они позволяют ему писать для своей любимой аудитории — новичков.Они также позволяют ему использовать юмор (ключевой элемент успеха в тренировочной комнате) и, что самое приятное, выражать одно или два мнения по рассматриваемому предмету.

.

Финансовое моделирование в Excel для чайников Шпаргалка

Даниэль Стейн Фэрхерст

Когда вы строите финансовые модели в Microsoft Excel, функции — это главное. Вам также необходимо проверить свою работу — и еще раз — чтобы убедиться, что ошибки не проскользнут сквозь трещины. Наконец, чтобы упростить и ускорить работу, вам помогут сочетания клавиш.

Основные функции Excel для построения финансовых моделей

Сегодня в Excel доступно более 400 функций, и Microsoft продолжает добавлять их с каждой новой версией программного обеспечения.Многие из этих функций не подходят для использования в финансах, и большинство пользователей Excel используют лишь очень небольшой процент доступных функций. Если вы используете Excel для финансового моделирования, вам нужно как минимум хорошо разбираться в наиболее часто используемых функциях.

Хотя существует множество других функций, которые могут вам пригодиться при построении моделей, вот список самых основных функций, без которых вы не можете обойтись.

Функция	Что он делает
СУММ	Складывает или суммирует диапазон ячеек.
МИН	Вычисляет минимальное значение для диапазона ячеек.
МАКС	Вычисляет максимальное значение диапазона ячеек.
СРЕДНЕЕ	Вычисляет среднее значение диапазона ячеек.
КРУГЛЫЙ	Округляет единичное число до ближайшего указанного значения , обычно до целого числа.
ЗА НЕДЕЛЮ	Округляет до отдельного числа до ближайшего указанного значения, обычно до целого числа.
КРУГЛЫЙ ВНИЗ	Округляет в меньшую сторону отдельное число до ближайшего указанного значения, обычно до целого числа.
IF	Возвращает указанное значение, только если выполнено условие single .
IFS	Возвращает указанное значение, если были выполнены сложные условия .
СЧЁТ	Подсчитывает количество значений в диапазоне, которые соответствуют определенному критерию single .
СЧЁТЕСЛИ	Подсчитывает количество значений в диапазоне, которые соответствуют нескольким критериям .
СУММИФ	Суммирует значения в диапазоне, которые соответствуют определенному критерию single .
СУММЕСЛИМН	Суммирует значения в диапазоне, которые соответствуют множественным критериям .
ВПР	Ищет диапазон и возвращает первое соответствующее значение в вертикальной таблице , которое точно соответствует указанному входу.
HLOOKUP	Ищет диапазон и возвращает первое соответствующее значение в горизонтальной таблице , которое точно соответствует указанному входу. Если не удается найти точное совпадение, возвращается ошибка.
ИНДЕКС	Работает как координаты карты и возвращает одно значение на основе номеров столбцов и строк, которые вы вводите в поля функций.
МАТЧ	Возвращает позицию значения в столбце или строке.Разработчики моделей часто комбинируют ПОИСКПОЗ с функцией ИНДЕКС для создания функции поиска, которая является гораздо более надежной и гибкой и использует меньше памяти, чем ВПР или ГПР.
PMT	Вычисляет итого годового платежа по ссуде.
IPMT	Вычисляет процентный компонент ссуды.
PPMT	Вычисляет основной компонент ссуды.
ЧПС	Принимает во внимание временную стоимость денег, давая чистую приведенную стоимость будущих денежных потоков в сегодняшних долларах на основе суммы инвестиции и ставки дисконтирования.

Хороший разработчик финансовых моделей — это гораздо больше, чем просто знание множества функций Excel. Опытный модельер может выбрать, какую функцию лучше всего использовать в той или иной ситуации. Обычно можно найти несколько разных способов достижения одного и того же результата, но лучший вариант — это всегда функция или решение, которое является наиболее простым, ясным и легким для понимания другими.

На что обращать внимание при проверке или аудите финансовой модели

Если вы какое-то время пользуетесь Excel, вы, вероятно, предпочтете создавать свои собственные электронные таблицы или финансовые модели с нуля.Однако в корпоративной среде люди редко получают такую ​​возможность. Вместо этого ожидается, что они возьмут на себя модель, созданную кем-то другим.

Может быть, вы вступаете в должность, которую передаете кому-то другому, и есть существующая модель финансовой отчетности, которую вам нужно будет обновлять каждый месяц. Или вам сказали рассчитывать комиссию с продаж каждый квартал на основе огромной таблицы из 50 вкладок, которая вам не очень нравится. Вы не только наследуете чужие модели вместе с исходными данными, допущениями и расчетами, которые ввел исходный разработчик моделей, но и наследуете их ошибки.

Если вы собираетесь взять на себя ответственность за чужую модель, вы должны быть готовы взять ее на себя и сделать своей собственной. Вы должны нести ответственность за работу этой модели и быть уверенным, что она работает правильно. Вот контрольный список того, что вам следует проверить, когда вы впервые унаследуете чужую финансовую модель:

• Познакомьтесь с его внешним видом. Просмотрите каждый лист, чтобы увидеть, какие цветовые схемы были использованы. Прочтите любую документацию.Есть ли ключ, который поможет увидеть, какие ячейки какие? Различал ли разработчик моделей формулы и жестко запрограммированные допущения?
• Внимательно посмотрите на формулы. Последовательны ли они? Содержат ли они какие-либо жестко запрограммированные значения, которые не будут обновляться автоматически и, следовательно, будут вызывать ошибки?
• Выполните проверку ошибок. Нажмите кнопку «Проверка ошибок» в разделе «Аудит формул» на вкладке «Формулы» на ленте, чтобы сразу увидеть, есть ли на листе ошибки Excel, которые могут вызвать проблемы.
• Проверить ссылки на внешние файлы. Внешние ссылки могут быть допустимой частью рабочего операционного процесса, но вам необходимо знать, получает ли этот файл какие-либо данные из внешних книг, чтобы убедиться, что никто случайно не изменит лист или имена файлов, что приведет к ошибкам в вашей модели. Найдите внешние ссылки, нажав кнопку «Изменить ссылки» в разделе «Подключения» на вкладке «Данные» на ленте.
• Просмотрите названные диапазоны. Именованные диапазоны могут быть полезны в финансовой модели, но иногда они содержат ошибки из-за избыточных имен, а также внешних ссылок.Просмотрите именованные диапазоны в диспетчере имен, который находится в разделе «Определенные имена» на вкладке «Формулы» на ленте. Удалите все именованные диапазоны, которые содержат ошибки или не используются, и если они содержат ссылки на внешние файлы, обратите внимание и убедитесь, что они необходимы.
• Проверить автоматические расчеты. Формулы должны вычисляться автоматически, но иногда, когда файл очень большой или разработчик модели хочет контролировать изменения вручную, расчет был установлен вручную вместо автоматического.Если вы видите слово «Рассчитать» в строке состояния в нижнем левом углу, это означает, что расчет был установлен вручную, поэтому вам, вероятно, предстоит сложное расследование! Нажмите кнопку «Параметры расчета» в разделе «Расчет» на вкладке «Формулы» на ленте, чтобы переключиться между ручным и автоматическим расчетом книги.

В дополнение к этим шагам, вот несколько удобных инструментов аудита в Excel, которые вы можете использовать для проверки, аудита, проверки и, при необходимости, исправления унаследованной модели, чтобы вы могли быть уверены в результатах своей финансовой модели:

• Проверить книгу. Узнайте о скрытых особенностях вашей модели и определите потенциально проблемные особенности, которые в противном случае было бы очень трудно найти с помощью этого малоизвестного инструмента. Чтобы использовать его, откройте книгу, нажмите кнопку «Файл» на ленте; на вкладке «Информация» нажмите кнопку «Проверить наличие проблем».
• F2: Если исходные ячейки формулы находятся на той же странице, сочетание клавиш F2 переводит ячейку в режим редактирования, поэтому этот ярлык является хорошим способом визуально увидеть, откуда исходят данные.
• Отслеживание прецедентов / зависимых: Инструменты аудита Excel визуально отслеживают взаимосвязи с помощью стрелок трассирующей линии. Вы можете получить доступ к этим инструментам в разделе «Аудит формул» на вкладке «Формулы» на ленте.
• Вычислить формулу: Разберите длинные и сложные формулы с помощью инструмента Вычислить формулу в разделе «Аудит формул» на вкладке «Формулы» на ленте.
• Инструменты проверки ошибок: Если вы допустили ошибку — или то, что Excel сочтет ошибкой, — в верхнем левом углу ячейки появится зеленый треугольник.Это произойдет, если вы опустите соседние ячейки или введете ввод в виде текста, который выглядит так, как будто это должно быть число.
• Watch Window: Если у вас есть выходные ячейки, за которыми вы хотите следить, этот инструмент отобразит результат для указанных ячеек в отдельном окне. Вы можете найти этот инструмент в разделе «Аудит формул» на вкладке «Формулы» на ленте. Это полезно для проверки формул по se

.

Excel 2016 для чайников, шпаргалка

Грег Харви

На первый взгляд, у вас могут возникнуть проблемы с пониманием множества меню, вкладок, столбцов и строк пользовательского интерфейса Excel 2016. Тем не менее, вы можете выяснить, что вы делаете, с помощью нажатия клавиш для перемещения курсора ячейки в новую ячейку, следуя простым правилам этикета ввода данных, обнаруживая общие причины некоторых значений ошибок формул и читая быстрый список лучших Excel Особенности 2016.

Перемещение курсора ячейки в электронных таблицах Excel 2016

Excel 2016 предлагает широкий набор клавиш для перемещения курсора ячейки в новую ячейку. Когда вы используете одно из этих нажатий клавиш, программа автоматически прокручивает новую часть рабочего листа в поле зрения, если это требуется для перемещения указателя ячейки.

В следующей таблице приведены эти нажатия клавиш, включая то, как далеко каждое из них перемещает указатель ячейки от его начальной позиции.

Нажатие клавиши	Куда движется курсор ячейки
Стрелка вправо или вкладка	Ячейка справа.
Стрелка влево или Shift + Tab	Ячейка слева.
Стрелка вверх	Ячейка на одну строку вверх.
Стрелка вниз	Ячейка на одну строку ниже.
Дом	Ячейка в столбце A текущей строки.
Ctrl + Дом	Первая ячейка (A1) рабочего листа.
Ctrl + End или End, Home	Ячейка на листе на пересечении последнего столбца , в котором есть данные, и последней строки, содержащей данные (то есть , последняя ячейка так называемой активной области рабочего листа).
На страницу вверх	Ячейка на один полный экран вверх в том же столбце.
Страница вниз	Ячейка на один полный экран вниз в том же столбце.
Ctrl + стрелка вправо или Конец, стрелка вправо	Первая занятая ячейка справа в той же строке, которой предшествует или за которой следует пустая ячейка .Если ячейка не занята, указатель переходит в ячейку в самом конце строки.
Ctrl + стрелка влево или конец, стрелка влево	Первая занятая ячейка слева в той же строке, которой предшествует или за которой следует пустая ячейка . Если ни одна ячейка не занята, указатель переходит в ячейку в самом начале строки.
Ctrl + стрелка вверх или конец, стрелка вверх	Первая занятая ячейка выше в том же столбце, которому предшествует или за которым следует пустая ячейка .Если ни одна ячейка не занята, указатель переходит в ячейку в самом верху столбца.
Ctrl + стрелка вниз или конец, стрелка вниз	Первая занятая ячейка ниже в том же столбце, перед которой или после нее идет пустая ячейка . Если ни одна ячейка не занята, указатель переходит в ячейку в самом низу столбца.
Ctrl + Page Down	Положение указателя ячейки на следующем листе книги .
Ctrl + Page Up	Положение указателя ячейки на предыдущем листе этой книги.

При перемещении курсора ячейки с помощью клавиш, перечисленных в таблице, помните следующие полезные советы:

• В случае тех нажатий клавиш, которые используют клавиши со стрелками, вы должны либо использовать стрелки на клавиатуре курсора, либо отключить Num Lock на цифровой клавиатуре вашей физической клавиатуры.

• Нажатия клавиш, которые объединяют клавишу Ctrl или End со стрелкой, являются одними из самых полезных для быстрого перехода от одного края к другому в больших таблицах записей ячеек или для перехода от таблицы к таблице в разделе рабочего листа с множеством блоков. ячеек.

• Когда вы используете Ctrl и клавишу со стрелкой на физической клавиатуре для перемещения от края к краю в таблице или между таблицами на листе, вы удерживаете Ctrl при нажатии одной из четырех клавиш со стрелками. Когда вы делаете это с помощью сенсорной клавиатуры на устройстве с сенсорным экраном, вы последовательно нажимаете клавишу Ctrl, а затем клавишу со стрелкой.

• Когда вы используете End и альтернативу клавишам со стрелками, вы должны нажать и затем отпустить клавишу End , прежде чем вы нажмете клавишу со стрелкой.При нажатии и отпускании клавиши End в строке состояния появляется индикатор конечного режима. Это ваш знак того, что Excel готов для вас нажать одну из четырех клавиш со стрелками.

Этикет ввода данных в Excel 2016

Чтобы начать работу с новой электронной таблицей Excel 2016, вы просто начинаете вводить информацию на первом листе окна книги Book1. Вот несколько простых рекомендаций (что-то вроде этикета ввода данных), которые следует учитывать при создании электронной таблицы Excel на Sheet1 новой книги:

• По возможности организуйте информацию в таблицах данных, в которых используются соседние (соседние) столбцы и строки.Начинайте таблицы в верхнем левом углу листа и двигайтесь вниз по листу, а не по листу, когда это возможно. По возможности разделяйте каждую таблицу не более чем одним столбцом или строкой.

• При настройке этих таблиц не пропускайте столбцы и строки только для того, чтобы «разложить» информацию. (Чтобы разместить пробелы между информацией в соседних столбцах и строках, вы можете расширить столбцы, увеличить строки и изменить выравнивание.)

• Зарезервируйте один столбец у левого края таблицы для заголовков строк таблицы.

• Зарезервируйте одну строку вверху таблицы для заголовков столбцов таблицы.

• Если ваша таблица требует заголовка, поместите заголовок в строку над заголовками столбцов. Поместите заголовок в тот же столбец, что и заголовки строк.

Значения ошибок расшифровки в формулах Excel 2016

Вы можете сразу сказать, что формула Excel 2016 вышла из строя, потому что вместо правильного вычисленного значения вы получаете странное, непонятное сообщение.Эта странность, на языке таблиц Excel 2016, представляет собой значение ошибки . Его цель — сообщить вам, что какой-то элемент — либо в самой формуле, либо в ячейке, на которую ссылается формула, — не позволяет Excel вернуть ожидаемое вычисленное значение.

В следующей таблице перечислены некоторые значения ошибок Excel 2016 и их наиболее распространенные причины.

Что обнаруживается в камере	Что здесь происходит?
# DIV / 0!	Появляется, когда формула требует деления на ячейку, в которой либо содержит значение 0, либо, как это чаще бывает, пусто.Деление на ноль — запрет в математике.
# ИМЯ?	Появляется, когда формула ссылается на имя диапазона , которого не существует на листе. Это значение ошибки появляется, когда вы вводите неправильное имя диапазона или не можете заключить в кавычки какой-либо текст, используемый в формуле, из-за чего Excel считает, что текст относится к имени диапазона.
# ПУСТО!	Чаще всего появляется, когда вы вставляете пробел (где следует использовать запятую) для разделения ссылок на ячейки, используемых в качестве аргументов для функций.
# ЧИСЛО!	Появляется, когда Excel обнаруживает проблему с числом в формуле , например неправильный тип аргумента в функции Excel или вычисление, которое производит слишком большое или слишком маленькое число для представления на листе.
# ССЫЛКА!	Отображается, когда Excel обнаруживает недопустимую ссылку на ячейку, например , например, когда вы удаляете ячейку, указанную в формуле, или вставляете ячейки поверх ячеек, указанных в формуле.
#VALUE!	Появляется, когда вы используете неверный тип аргумента или оператора в функции или когда вы вызываете математическую операцию, которая относится к ячейкам, содержащим текстовые записи.

10 лучших функций Excel 2016

Если вы ищете краткое изложение того, что есть в Excel 2016, не ищите дальше! Беглый взгляд на первые несколько пунктов в этом списке говорит о том, что основная суть функций заключается в том, чтобы работать с Excel 2016 продуктивно в любое время и в любом месте!

• Полная поддержка облачных файлов: Новые экраны Excel Сохранить (Файл → Сохранить) и Открыть (Файл → Открыть) позволяют легко добавить OneDrive или корпоративный сайт группы SharePoint в качестве места для хранения и редактирования ваших любимых книг.Сохраняя свои книги Excel в одном из этих мест в облаке, вы получаете гарантированный доступ к ним на любом устройстве, на котором работает Excel 2016 (включая планшет и смартфон с Windows, а также настольный и портативный компьютер).

  Более того, если вы окажетесь без вычислительного устройства с Excel 2016, в рамках подписки на Office 365 вы все равно сможете просматривать и редактировать свои книги с помощью Excel Online практически в любом крупном веб-браузере.

• Безболезненные параметры обмена файлами: Обмен файлами в Excel стал еще лучше и проще, чем когда-либо.Экран «Поделиться» в Excel Backstage упрощает общий доступ к книгам Excel в Интернете. Вы можете не только легко приглашать людей для просмотра и редактирования книг, сохраненных на вашем OneDrive в облаке, вы также можете представлять их на онлайн-собраниях Skype и публиковать их на своих любимых сайтах в социальных сетях.

• Полная поддержка сенсорного экрана: Excel 2016 — это не только лучшая программа для работы с электронными таблицами для настольных компьютеров и ноутбуков с Windows, но и для планшетов и смартфонов с Windows.Чтобы сенсорный экран Excel 2016 был таким же богатым и полезным, как с физической клавиатурой и мышью, Excel 2016 поддерживает специальный сенсорный режим, который увеличивает пространство между командными кнопками на ленте, что упрощает их выбор пальцем или стилусом. со всеми основными жестами сенсорного экрана.

• Интегрированная поддержка модели данных : Excel 2016 поддерживает истинные отношения «один к одному» и «один ко многим» между таблицами данных, которые вы импортируете в Excel из автономных программ управления базами данных, а также между списками данных, которые вы создаете. в Excel.Связи между таблицами данных и списками в модели данных затем позволяют использовать данные из любых их столбцов в создаваемых вами сводных таблицах и диаграммах Excel.

• Фильтрация сводной таблицы с помощью срезов и временных шкал: срезы Excel 2016 позволяют быстро фильтровать данные в сводных таблицах по нескольким столбцам с помощью экранных графических объектов. Временные шкалы позволяют графически фильтровать данные сводной таблицы с помощью временной шкалы на основе любого столбца типа даты, включенного в модель данных сводной таблицы.

• Рекомендуемые диаграммы: Не уверены, какой тип диаграммы лучше всего покажет ваши данные? Просто поместите указатель ячейки в любом месте таблицы данных и выберите Вставить → Рекомендуемые диаграммы на ленте. Затем Excel отображает диалоговое окно «Вставить диаграмму», в котором предварительный просмотр в реальном времени показывает, как данные таблицы будут выглядеть в различных типах диаграмм. Как только вы обнаружите, что диаграмма лучше всего представляет данные, вы просто нажимаете кнопку OK, чтобы вставить ее в рабочий лист таблицы

  .

• Рекомендуемые сводные таблицы: Если вы новичок в создании сводных таблиц для списков данных Excel, которые вы создаете, а также таблиц данных, которые вы импортируете из автономных программ управления базами данных, теперь вы можете заставить Excel рекомендовать и создавать один для тебя.Все, что вам нужно сделать, это поместить курсор ячейки в одну из ячеек списка данных и выбрать Вставить → Таблица → Рекомендуемые сводные таблицы на ленте. Затем Excel 2016 открывает диалоговое окно «Рекомендуемые сводные таблицы», в котором отображается весь список различных сводных таблиц, которые можно создать на новом листе в текущей книге Excel, просто нажав кнопку «ОК».

• Надстройки Office: Надстройки Office позволяют расширить возможности Excel 2016, устанавливая всевозможные специализированные небольшие программы (также известные как приложения), которые доступны из Магазина Office прямо в программе.Чтобы установить и использовать надстройку, выберите Insert @@ → My Add-ins @@ → See All on the Ribbon, а затем выберите опцию STORE в диалоговом окне Office Add-ins.

  Бесплатные приложения для Excel 2016 inc

.

Как использовать функцию XLOOKUP в Excel 2016

2. Программное обеспечение
3. Microsoft Office
4. Excel
5. Как использовать функцию XLOOKUP в Excel 2016

Грег Харви

Excel 2016 для подписчиков Office 365 в Windows и Mac теперь поддерживает новую функцию XLOOKUP, которая рекламируется как значительно более простая и универсальная замена очень популярной (но часто критикуемой) функции вертикального поиска, VLOOKUP (не знаю, что означает X в XLOOKUP; возможно, расширенная?) .

Для тех из вас, кто еще не знаком с ВПР (считается третьей по частоте использования функцией сразу после СУММ и СРЕДНЕЕ), эта функция выполняет поиск по строкам по вертикали в крайнем левом столбце назначенной таблицы поиска сверху вниз, пока не найдет значение в столбец подстановки, обозначенный числом смещения, которое соответствует или превышает тот, который вы ищете. Хотя функция VLOOKUP чрезвычайно полезна для поиска определенных элементов в длинном списке или столбце таблицы данных на вашем листе, она имеет несколько ограничений, которые не разделяются этой новой функцией поиска, например XLOOKUP:

• По умолчанию поиск точных совпадений для вашего значения поиска в диапазоне поиска
• Может выполнять поиск как по вертикали (по строкам), так и по горизонтали (по столбцам) в таблице, тем самым устраняя необходимость использования функции HLOOKUP при поиске по горизонтали по столбцу
• Может выполнять поиск влево или вправо, так что диапазон поиска в таблице поиска не должен располагаться в столбце слева от столбца, обозначенного как диапазон возврата, чтобы функция работала
• Когда используется точное соответствие по умолчанию, работает, даже если значения в диапазоне поиска не отсортированы в определенном порядке
• Может выполнять поиск от нижней строки до верхней в диапазоне массива поиска, используя необязательный аргумент режима поиска

Функция XLOOKUP имеет пять возможных аргументов, первые три из которых являются обязательными, а последние два — необязательными, с использованием следующего синтаксиса:

XLOOKUP ( lookup_value , lookup_array , return_array , [ match_mode ], [ search_mode ])

Обязательный аргумент lookup_value обозначает значение или элемент, для которого вы ищете

.