Один из аналогов, который позволит преодолеть некоторые из описанных выше ограничений – именованный диапазон. Конечно, это уже несколько другая тема, поскольку таблицы и именованные диапазоны пересекаются в определенной части функционала, но не дублируют его.
На него также можно ссылаться в формуле, а также обновлять информацию в нем. Правда, последнее придется делать вручную через Диспетчер имен.
Именованные диапазоны могут использоваться как в простых, так и сложных формулах. И в некоторых аспектах могут повторять функционал таблиц. Например, у нас есть такая формула.
= СУММ(E2:E8)+СРЗНАЧ(E2:E8)/5+10/СУММ(E2:E8)
Видим, что здесь один и тот же диапазон (не таблица и не именованный) используется сразу несколько раз. Предположим, нам нужно этот диапазон поменять на какой-то другой. В таком случае изменения придется вносить сразу в трех местах.
Если же этому диапазону присвоить имя или превратить его в таблицу, достаточно просто указать его название один раз, а потом в случае чего просто поменять привязку к определенному диапазону также один раз.
Microsoft Excel.Типы данных в Microsoft Excel
Начало работы
Практика – лучший способ получить базовые навыки в любой профессии. Табличный процессор от Microsoft не является исключением. Это весьма полезное приложение, применимое в самых разных областях деятельности, позволяющее организовать быстрые вычисления вне зависимости от количества исходных данных.
Освоив Excel, вы вряд ли станете экспертом по реляционным базам данных, но полученных навыков окажется вполне достаточно для получения статуса «уверенный пользователь». А это не только моральное удовлетворение и способ похвастаться перед друзьями, но и небольшой плюсик к вашему резюме.
Итак, для начала давайте ознакомимся с основными терминами, касающимися Excel:
- таблица – это двумерное представление наборов чисел или иных значений, размещённых в строках и столбцах. Нумерация строк – числовая, от 1 и далее до бесконечности. Для столбцов принято использовать буквы латинского алфавита, причём, если нужно больше 26 столбцов, то после Z будут идти индексы АА, АВ и так далее;
- таким образом, каждая ячейка, расположенная на пересечении столбца и строки, будет иметь уникальный адрес типа А1 или С10. Когда мы будем работать с табличными данными, обращение к ячейкам будет производиться по их адресам, вернее – по диапазонам адресов (например, А1:А65, разделителем здесь является двоеточие). В Excel табличный курсор привязывается не к отдельным символам, а к ячейке в целом – это упрощает манипулирование данными. Это означает, что с помощью курсора вы можете перемещаться по таблице, но не внутри ячейки – для этого имеются другие инструменты;
- под рабочим листом в Excel понимают конкретную таблицу с набором данных и формулами для вычислений;
- рабочая книга – это файл с расширением xls, в котором может содержаться один или несколько рабочих листов, то есть это может быть набор связанных таблиц;
- работать можно не только с отдельными ячейками или диапазонами, но и с их совокупностью. Отдельные элементы списка разделяются точкой с запятой (В2;В5:В12);
- с помощью такой индексации можно выделять отдельные строки, столбцы или прямоугольные области;
- с объектами таблицы можно производить различные манипуляции (копирование, перемещение, форматирование, удаление).
Но не все так просто…
Дело в том, что есть ряд недостатков у таблиц Excel, которые накладывают определенные ограничения на их работу:
СамоУчка Pro Excel Урок№2. Типы данных в Excel.
Нет возможности использовать представления. Простыми словами, нельзя запомнить ряд настроек листа, таких как фильтр, свернутые строки или столбцы и так далее.
Нельзя использовать эту книгу одновременно с другим человеком через функцию совместного использования.
Возможна лишь вставка окончательных итогов.
В таблице нельзя использовать формулы массивов, что накладывает серьезные ограничения при работе с большими объемами данных.
Ячейки в таблице не могут быть объединены
Впрочем, даже в обычном диапазоне эту функцию рекомендуют использовать с осторожностью.
Невозможно транспонировать таблицу так, чтобы заголовки располагались в строках. Чтобы это сделать, ее нужно переконвертировать в формат обычного диапазона.
Настройки Таблицы
В контекстной вкладке Конструктор находятся дополнительные инструменты анализа и настроек.
С помощью галочек в группе Параметры стилей таблиц
можно внести следующие изменения.
— Удалить или добавить строку заголовков
— Добавить или удалить строку с итогами
— Сделать формат строк чередующимися
— Выделить жирным первый столбец
— Выделить жирным последний столбец
— Сделать чередующуюся заливку строк
— Убрать автофильтр, установленный по умолчанию
В видеоуроке ниже показано, как это работает в действии.
В группе Стили таблиц можно выбрать другой формат. По умолчанию он такой как на картинках выше, но это легко изменить, если надо.
В группе Инструменты можно создать сводную таблицу, удалить дубликаты, а также преобразовать в обычный диапазон.
Однако самое интересное – это создание срезов.
Срез – это фильтр, вынесенный в отдельный графический элемент. Нажимаем на кнопку Вставить срез, выбираем столбец (столбцы), по которому будем фильтровать,
и срез готов. В нем показаны все уникальные значения выбранного столбца.
Для фильтрации Таблицы следует выбрать интересующую категорию.
Если нужно выбрать несколько категорий, то удерживаем Ctrl или предварительно нажимаем кнопку в верхнем правом углу, слева от снятия фильтра.
Попробуйте сами, как здорово фильтровать срезами (кликается мышью).
Для настройки самого среза на ленте также появляется контекстная вкладка Параметры. В ней можно изменить стиль, размеры кнопок, количество колонок и т.д. Там все понятно.
Список с автозаполнением
Способ 2
- Выделить имеющийся перечень наименований и присвоить ему имя в левой строке формул.
- Щелкнуть по сторонней ячейке. Зайти в «Данные» – «Проверка данных». В качестве источника указать присвоенное таблице имя из п.1 через знак «=».
- Чтобы добавить еще одно значение, нужно воспользоваться вставкой пустой строки. Выделить ячейку с любым наименованием, на «Главной» клацнуть по «Вставить» — «Вставить строки на лист». На листе отобразится пустая ячейка, вписать туда название.
Способ 3
Если в нижние строки вписать новые значения, они тут же отобразятся в готовом перечне.
Функции для работы с текстом
Основные функции для работы с текстом в VBA Excel:
| Asc(строка) | Возвращает числовой код символа, соответствующий первому символу строки. Например: MsgBox Asc(«/Stop»). Ответ: 47, что соответствует символу «/». |
| Chr(код символа) | Возвращает строковый символ по указанному коду. Например: MsgBox Chr(47). Ответ: «/». |
| Format(Expression, , , ) | Преобразует число, дату, время в строку (тип данных Variant (String)), отформатированную в соответствии с инструкциями, включенными в выражение формата. Подробнее… |
| InStr(, строка1, строка2, ) | Возвращает порядковый номер символа, соответствующий первому вхождению одной строки (строка2) в другую (строка1) с начала строки. Подробнее… |
| InstrRev(строка1, строка2, ]) | Возвращает порядковый номер символа, соответствующий первому вхождению одной строки (строка2) в другую (строка1) с конца строки. |
| Join(SourceArray,) | Возвращает строку, созданную путем объединения нескольких подстрок из массива. Подробнее… |
| LCase(строка) | Преобразует буквенные символы строки в нижний регистр. |
| Left(строка, длина) | Возвращает левую часть строки с заданным количеством символов. Подробнее… |
| Len(строка) | Возвращает число символов, содержащихся в строке. |
| LTrim(строка) | Возвращает строку без начальных пробелов (слева). Подробнее… |
| Mid(строка, начало, ) | Возвращает часть строки с заданным количеством символов, начиная с указанного символа (по номеру). Подробнее… |
| Replace(expression, find, replace, , , ) | Возвращает строку, полученную в результате замены одной подстроки в исходном строковом выражении другой подстрокой указанное количество раз. Подробнее… |
| Right(строка, длина) | Возвращает правую часть строки с заданным количеством символов. Подробнее… |
| RTrim(строка) | Возвращает строку без конечных пробелов (справа). Подробнее… |
| Space(число) | Возвращает строку, состоящую из указанного числа пробелов. Подробнее… |
| Split(Expression. ) | Возвращает одномерный массив подстрок, извлеченных из указанной строки с разделителями. Подробнее… |
| StrComp(строка1, строка2, ) | Возвращает числовое значение Variant (Integer), показывающее результат сравнения двух строк. Подробнее… |
| StrConv(string, conversion) | Изменяет регистр символов исходной строки в соответствии с заданным параметром «conversion». Подробнее… |
| String(число, символ) | Возвращает строку, состоящую из указанного числа символов. В выражении «символ» может быть указан кодом символа или строкой, первый символ которой будет использован в качестве параметра «символ». Подробнее… |
| StrReverse(строка) | Возвращает строку с обратным порядком следования знаков по сравнению с исходной строкой. Подробнее… |
| Trim(строка) | Возвращает строку без начальных (слева) и конечных (справа) пробелов. Подробнее… |
| UCase(строка) | Преобразует буквенные символы строки в верхний регистр. |
| WorksheetFunction.Trim(строка) | Функция рабочего листа, которая удаляет все лишние пробелы (начальные, конечные и внутренние), оставляя внутри строки одиночные пробелы. |
В таблице перечислены основные функции VBA Excel для работы с текстом. С полным списком всевозможных функций вы можете ознакомиться на сайте разработчика.
Воспользуйтесь рекомендациями программы.
Как вы только что видели, создание сводных таблиц — довольно простое дело, даже для «чайников». Однако Microsoft делает еще один шаг вперед и предлагает автоматически сгенерировать отчет, наиболее подходящий для ваших исходных данных. Все, что вам нужно, это 4 щелчка мыши:
- Нажмите любую ячейку в исходном диапазоне ячеек или таблицы.
- На вкладке «Вставка» выберите «Рекомендуемые сводные таблицы». Программа немедленно отобразит несколько макетов, основанных на ваших данных.
- Щелкните на любом макете, чтобы увидеть его предварительный просмотр.
- Если вас устраивает предложение, нажмите кнопку «ОК» и добавьте понравившийся вариант на новый лист.
Как вы видите на скриншоте выше, Эксель смог предложить несколько базовых макетов для моих исходных данных, которые значительно уступают сводным таблицам, которые мы создали вручную несколько минут назад. Конечно, это только мое мнение
Но при всем при этом, использование рекомендаций — это быстрый способ начать работу, особенно когда у вас много данных и вы не знаете, с чего начать. А затем этот вариант можно легко изменить по вашему вкусу.
Работа с переменными
Чтобы использовать в процедуре переменные, их необходимо объявить с помощью ключевого слова «Dim». Если при объявлении переменных не указать типы данных, они смогут принимать любые доступные в VBA Excel значения. Комментарии в тексте процедур начинаются со знака «’» (апостроф).
Пример 2
Присвоение переменным числовых значений:
‘Объявляем переменные x, y, z
Источник: myeditor.ru
Что такое типы данных в Excel
Когда вы вводите что-либо в ячейку, Excel определяет, данные какого типа вы ввели: значение; дату или время; текст; формулу. Если вы будете знать, как работает механизм интерпретации данных, вводимых в ячейку, то сможете понять, почему Excel неверно распознал введенные вами данные.
Ввод значений
Любое число, записанное в ячейку цифрами, считается значением. Кроме того, значения могут включать несколько специальных символов.
- Знак минуса (-). Если он предшествует значению, Excel интерпретирует его как отрицательное число.
- Знак плюса (+). Если данный знак стоит перед значением, Excel интерпретирует его как положительное число (и при этом не отображает этот знак).
- Круглые скобки. Если положительное число заключено в круглые скобки, Excel интерпретирует его как отрицательное.
- Знак процентов (%). Если этот знак следует после чисел, Excel интерпретирует значение как проценты и автоматически применяет соответствующее форматирование.
- Символ валюты. Если данный символ (например, знак доллара) предшествует числу, Excel интерпретирует введенное значение как денежное выражение и автоматически форматирует его как валюту.
- Разделитель тысяч. Значение, включающее в себя один или несколько разделителей тысяч, определенных в вашей системе (например, это может быть запятая), Excel интерпретирует как число, а также применяет форматирование для отображения символа разделителя. Обратите внимание, что разделитель тысяч должен находиться в соответствующей позиции. Например, если в вашей системе исчисления в качестве разделителя тысяч используется запятая, то число 4,500 Excel интерпретирует как значение, а вот число 45,00 он таковым считать не будет.
- Экспоненциальное обозначение. Значение, содержащее букву «Е», Excel попытается распознать как экспоненциальное обозначение. Например, 3.2Е5 будет интерпретировано как 3.2 х 105.
Ввод даты и времени
В Excel дата и время считаются числовыми значениями, которые форматируются специальным образом, потому что люди гораздо легче понимают такое представление. С датами Excel оперирует, используя последовательную нумерацию дат, так называемые порядковые номера. Самая ранняя дата, которую способен распознать Excel, — 1 января 1900 года. Этой дате присвоен порядковый номер 1. Дата 2 января 1900 года имеет порядковый номер 2 и т. д. Такая система представления значительно облегчает использование дат в формулах. Например, можно ввести формулу, по которой вычисляется количество дней между двумя датами.
Примеры дат в этой книге используют английскую (США) систему. В зависимости от ваших региональных настроек ввод даты в таком формате, как Junel, 2010, может быть интерпретирован как текст, а не как дата. В этом случае вам необходимо ввести дату в формате, который соответствует вашим региональным настройкам, — 1-июн-2010.
После того как вы введете дату, можете форматировать ее, чтобы она отображалась в нужном формате. Для этого используйте вкладку Число диалогового окна Формат ячеек. Что касается распознавания вводимых вами дат, Excel хоть и умен, но все же не идеален. Например, редактор не распознает любую из следующих записей в качестве даты: June 1 1010, Jun-l 2010 и Jun-1/2010. Он интерпретирует их как текст.
Если вы планируете ссылаться на даты в формулах, убедитесь, что даты, которые вы вводите, распознаются именно как даты. В противном случае ваши формулы будут выдавать неправильные результаты. Распространенной проблемой является то, что Excel интерпретирует записи как даты, когда вы намерены ввести дроби. Например, если вы введете значение 1/5, Excel интерпретирует его как 5 января текущего года. Решение состоит в том, чтобы указывать знак равенства перед дробью.
При работе со временем Excel просто расширяет свою систему серийных номеров для дат, и они теперь включают знаки после запятой. Другими словами, Excel работает со значениями времени с помощью дробных дней. Например, серийный номер для даты 1 июня 2010 года равен 40 330. Полдень 1 июня 2010 года имеет следующий внутренний формат представления: 40 330.5, поскольку дробная часть, определяющая время, добавлена к серийному номеру даты.
Обычно вам не нужно беспокоиться об этих серийных номерах (в том числе дробных — для времени). Просто введите время в ячейку в корректном формате. В табл. 29.1 приведены некоторые примеры представления времени в форматах, которые распознает Excel.
Таблица 29.1. Примеры представления времени
| 11:30:00 am* | 11:30 AM |
| 11:30:00 AM | 11:30 AM |
| 11:30 pm | 11:30 PM |
| 11:30 | 11:30 AM |
| 13:30 | 1:30 PM |
a.m. — до полудня, p.m. — после полудня.
В этих примерах нет привязок к дням, поэтому они внутренне представляются как значения меньше 1. Другими словами, Excel использует несуществующую дату 0 января 1900 года. Вы также можете объединять даты и время, как показано в табл. 29.2.
Таблица 29.2. Примеры объединения даты и времени
| 26.06.10 11:30 | 11:30 до полудня, 26 июня 2010 г. |
| 26.06.10 12:00 | Полдень 26 июня 2010 г. |
| 26.06.10 0:00 | Полночь 26 июня 2010 г. |
Если вы введете время, превышающее 24 часа, связанная с ним дата увеличится соответственно. Например, когда вы введете следующее время в ячейку, оно будет интерпретироваться как 1:00 ночи, 1 января 1900 года: 25:00:00. Значение дня в этой записи увеличивается, потому что время превышает 24 часа. Имейте в виду, что значение времени без даты использует 0 января 1900 года в качестве даты.
Ввод текста
Если Excel не может интерпретировать введенную в ячейку запись в качестве значения, даты, времени или формулы, то в таком случае запись переходит к всеобъемлющей категории текста. В одну ячейку можно поместить около 32 000 символов. Вы можете ввести в ячейку огромное количество символов, но отобразить их все на экране одновременно отнюдь не просто. Если вам нужно показать большой объем текста на листе, рассмотрите вариант его размещения в текстовом поле (Вставка ► Текст ► Надпись).
Ввод формул
- математические операторы, такие как + (для сложения) и * (для умножения);
- круглые скобки;
- ссылки на ячейки (в том числе именованные ячейки и диапазоны);
- значения или текст;
- функции листа (например, СУММ или СРЗНАЧ).
Если вы введете синтаксически некорректную формулу, Excel может предложить поправки. Имейте в виду, что эти поправки могут быть правильными, но в результате вы можете получить не ту формулу, которую хотели ввести изначально.
Источник: excelexpert.ru
Типы данных, используемых в Excel
В ячейки может вводиться разнообразная по типу информация: числа, символы, время, дата, логические константы (истина или ложь), формулы, осуществляющие вычисление значений. Для изменения внешнего представления данных в ячейках таблицы осуществляется форматирование – панель инструментов Главная (рис. 12.7).
Рис. 12.7. Панель инструментов Главная
Вкладка Число обеспечивает задание типа числового формата, определение числа десятичных знаков (рис. 12.8).
Вкладка Выравнивание обеспечивает задание порядка выравнивания содержимого ячеек по горизонтали и вертикали, ориентации текста (поворот от +90 до -90°), разбивки текста на строки и переноса по словам, а также объединение ячеек и автоподбор ширины столбца (рис. 12.9).
Вкладка Шрифт обеспечивает выбор типа и размера шрифта, стиля начертания и дополнительных текстовых эффектов; Граница – выбор
Рис. 12.8. Примеры форматов чисел, дат и времени
Рис. 12.9. Примеры выравнивания содержимого ячеек
цвета и типа линии, способа обрамления ячеек; Вид – выбор цвета и узора заливки ячеек; Защита – установку защиты ячеек, скрытие формул.
Формулы и функции
Ввод формулы всегда начинают со знака «равно» ( = ) или «плюс» ( + )•
Например: = 46 + 55; = 200 * В5; = А7/В4.
- 1. В ячейке, содержащей формулу (по умолчанию), виден только результат вычислений. Саму формулу можно увидеть в строке формул, когда данная ячейка станет активной.
- 2. Excel вычисляет формулу каждый раз, когда изменяется содержимое таблицы, ссылающееся на данную формулу.
- 3. Если результат вычислений не помещается в ячейке таблицы, Excel может вывести на экран последовательность символов «#######», говорящую о том, что необходимо увеличить ширину столбца.
- 4. При вводе десятичных чисел используется запятая (,) в качестве десятичного разделителя.
Функции Excel – это заранее определенные формулы, позволяющие производить расчеты в финансовой, статистической, математической, логической и других областях деятельности.
Они сгруппированы по категориям и имеют одинаковый синтаксис: имя функции и ее аргументы.
Функции задаются с помощью математических и других формул, в соответствии с которыми выполняются вычисления по заданным величинам, называемым аргументами, и в указанном порядке, определяемом синтаксисом. Синтаксис встроенных функций достаточно прост:
Имя функции показывает, о каких вычислениях идет речь. Примеры имен функции – СУММ, СРЗНАЧ и др.
Аргументы – значения, которые функция использует, вычисляя результат.
Они перечисляются в скобках следом за именем функции. В качестве аргументов могут выступать числовые значения, текст, логические значения, массивы, значения ошибок или ссылки, дата/время, а также другие функции и формулы. В Excel различают обязательные (которые всегда нужно задавать) и необязательные аргументы. Отдельные аргументы разделяются символом «точка с запятой» (;).
Результат – значение, полученное при вычислении функции.
Многие математические операции в Excel производятся с использованием встроенных функций.
Функции могут быть выбраны из списка функций с помощью меню Вставка/Функция или путем нажатия пиктограммы fx на стандартной панели инструментов с соответствующим набором необходимых функций.
После выбора требуемой функции подключается Мастер функций, позволяющий пользователю пошагово заполнять ее аргументы.
Функции в Excel подразделяются на следующие основные группы: математические, арифметические и тригонометрические; для работы с датами и временем; финансовые; логические; для работы со ссылками и массивами; для работы с БД; статистические; текстовые и др.
Источник: studme.org