§3.1 Электронные таблицы
Сотни лет в деловой сфере при выполнении громоздких однотипных расчётов используются таблицы. С их помощью рассчитывается заработная плата, ведутся различные системы учёта материальных ценностей, просчитывается стоимость новых товаров и услуг, прогнозируется размер прибыли и т. д. Такие расчёты многие специалисты до конца прошлого века выполняли с помощью калькуляторов, вручную занося полученные результаты в соответствующие графы таблиц. Такая работа требовала больших временных затрат; на исправление незначительной ошибки, допущенной расчётчиком, уходили недели и даже месяцы.
Ситуация кардинально изменилась с появлением электронных таблиц, позволивших за счёт изменения исходных данных быстро решать большое количество типовых расчётных задач.
Электронные таблицы (табличный процессор) — это прикладная программа, предназначенная для организации табличных вычислений на компьютере. Электронными также называют и таблицы, созданные с помощью одноименных программ в памяти компьютера.
В наши дни электронные таблицы являются одним из программных продуктов, наиболее широко используемых на практике. С их помощью пользователи, не обладая специальными знаниями в области программирования, имеют возможность определять последовательность вычислительных операций, выполнять различные преобразования исходных данных, представлять полученные результаты в графической форме.
3.1.1. Интерфейс электронных таблиц
Наиболее распространёнными табличными процессорами являются Microsoft Excel и OpenOffice.org Calc.
При запуске любого из них на экран выводится окно, многие элементы которого вам хорошо известны по опыту работы с другими программами.
Строка заголовка содержит название документа, название программы и кнопки управления окном.
Строка меню содержит названия групп команд управления электронной таблицей, объединённых по функциональному признаку.
Панели инструментов содержат пиктограммы для вызова наиболее часто выполняемых команд.
Рабочей областью табличного процессора является прямоугольное пространство, разделённое на столбцы и строки. Каждый столбец и каждая строка имеют обозначения (заголовки, имена). Столбцы обозначаются слева направо латинскими буквами в алфавитном порядке; могут использоваться однобуквенные, двухбуквенные и трёхбуквенные имена (А, В, С и т. д.; после 26-го столбца начинаются двухбуквенные сочетания АА, АВ и т. д.). Строки нумеруются сверху вниз. Число строк и столбцов у разных табличных процессоров различно.
На пересечении столбцов и строк образуются ячейки (клетки), в которые могут быть записаны данные или выполняемые над ними операции.
Ячейка — наименьшая структурная единица электронной таблицы. Каждая ячейка электронной таблицы имеет имя, составленное из буквенного имени столбца и номера строки, на пересечении которых она располагается. Возможны следующие имена ячеек: El, К12, АВ1251. Таким образом, имя ячейки определяет её адрес в таблице.
Ячейка — наименьшая структурная единица электронной таблицы, образуемая на пересечении столбца и строки.
Табличный курсор — выделенный прямоугольник, который можно поместить в любую ячейку. Ячейка таблицы, которую в данный момент занимает курсор, называется текущей ячейкой. Вводить или редактировать данные можно только в текущей ячейке.
Адрес текущей ячейки и вводимые в неё данные отражаются в строке ввода. В строке ввода можно редактировать информацию, хранящуюся в текущей ячейке.
Идущие подряд ячейки в строке, столбце или прямоугольнике образуют диапазон. При задании диапазона указывают его начальную и конечную ячейки, в прямоугольном диапазоне — ячейки левого верхнего и правого нижнего углов. Наибольший диапазон представляет вся таблица, наименьший — одна ячейка. Примеры диапазонов: А1:А10, В2:С2, B2:D10.
Рабочая область табличного процессора иначе называется листом. Создаваемый и сохраняемый в табличном процессоре документ называется книгой; он может состоять из нескольких листов. Аналогично листам бухгалтерской книги, их можно перелистывать, щёлкая на ярлыках, расположенных внизу окна. Каждому листу книги пользователь может задать имя, исходя из содержимого этого листа.
Лист — рабочая область, состоящая из ячеек.
Книга — документ электронной таблицы, состоящий из листов, объединенных одним именем, и являющийся файлом.
В строке состояния выводятся сообщения о текущем режиме работы таблицы и возможных действиях пользователя.
3.1.2. Данные в ячейках таблицы
Содержимым ячейки может быть:
Текст — это последовательность любых символов из компьютерного алфавита.
Тексты (надписи, заголовки, пояснения) нужны для оформления таблицы, в текстовой форме могут быть представлены характеристики рассматриваемых объектов. Изменить содержимое ячейки с текстом можно только путём редактирования ячейки. По умолчанию текст выравнивается в ячейке по левому краю — по аналогии со способом письма слева направо.
С помощью чисел задаются количественные характеристики рассматриваемых объектов. При этом используются различные числовые форматы. По умолчанию используется числовой формат с двумя десятичными знаками после запятой. Для записи чисел, содержащих большое количество разрядов, не умещающихся в ячейке, применяется экспоненциальный (научный) формат. Числовые данные, введённые в ячейки таблицы, являются исходными данными для проведения вычислений. Изменить числовые данные можно путём их редактирования. По умолчанию числа выравниваются в ячейке по правому краю, что обеспечивает выравнивание всех чисел столбца по разрядам (единицы размещаются под единицами, десятки — под десятками и т. д.).
5 примеров использования новой функции IMAGE в Excel
Уже несколько лет в Google Sheets существует функция IMAGE, позволяющая вставлять в ячейки листа картинки по ссылке из интернета. Что, впрочем, вполне естественно, поскольку Google-таблицы изначально заточены под работу онлайн — им сам бог велел такую возможность использовать.
У Excel же ничего подобного долго не было. И вот, наконец, осенью 2022 года Microsoft начала среди добровольцев-тестировщиков из программы Office Insider обкатку своего аналога — новой функции ИЗОБРАЖЕНИЕ (IMAGE), также позволяющей вставлять по веб-ссылке картинки из интернета прямо в ячейки листа Microsoft Excel. В начале 2023 года эту функцию уже стали потихонечку разливать с обновлениями подписчикам Office 365 и недавно она, наконец, добралась и до меня. А значит я могу вам о ней рассказать на нескольких практических примерах.
Если же последней версии Office у вас пока (или уже) не предвидится, то не расстраивайтесь — в конце этой статьи я покажу как при помощи короткого макроса можно сделать упрощённый аналог этой функции уже сегодня.
Синтаксис функции ИЗОБРАЖЕНИЕ (IMAGE)
=ИЗОБРАЖЕНИЕ(источник; [замещающий_текст]; [изменение_размера]; [высота]; [ширина])
Первым и единственным обязательным аргументом новой функции должна быть текстовая ссылка на файл изображения из интернета. Причем это должна быть именно веб, а не локальная ссылка на файл на жестком или сетевом диске. Если доступа в интернет сейчас нет, то вместо картинки будет ошибка #СОЕДИНЕНИЕ! (если нет интернета) или #ЗАБЛОКИР! (если ваши настройки безопасности не дают загрузить картинку). Если вы хотите подстраховаться на такой случай, то вторым аргументом как раз можно ввести замещающий текст, который будет отображен вместо отсутствующей картинки.
Третий аргумент отвечает за размеры и пропорции изображения и может принимать значения:
- 0 — вписать изображение в ячейку с сохранением пропорций
- 1 — заполнить всю площадь ячейки не сохраняя пропорции картинки
- 2 — сохранить исходный размер изображения (тут оно запросто может вылезти за пределы ячейки)
- 3 — задать высоту и ширину изображения принудительно (тогда их надо обязательно прописать в четвертом и пятом аргументе функции)
Большим преимуществом новой функции является то, что картинка будет не просто вставлена на лист, а именно вписана в конкретную ячейку, т.е. будет вместе с ней двигаться, сжиматься и растягиваться. Это принципиально отличается от классической вставки картинок командой Вставка — Рисунки (Insert — Pictures) , когда изображение и лист находились, по сути, в разных слоях и вы могли запросто случайно сдвинуть или даже удалить картинку после вставки. Здесь же всё чётко.
Также если щёлкнуть по вставленному функцией изображению правой кнопкой мыши и выбрать команду Показать сведения об изображении (Show image properties) , то во всплывающем окне можно будет увидеть картинку в оригинальном разрешении.
Теперь давайте рассмотрим несколько вкусных примеров использования новой функции ИЗОБРАЖЕНИЕ на практике.
Пример 1. Создание штрих-кодов и QR-кодов
Этикетки для маркировки товаров, ценники, бланки, наклейки — всё это требует формирования штрих-кодов различного типа. Теперь для этого можно не использовать специальные программы, а реализовать всё в Excel.
Идём на сайт австрийской компании TEC-IT, где можно легко создать штрих-код любого стандарта с заданным содержимым:
Ссылка, по которой формируется изображение штрих-кода висит на кнопке Download и выглядит так:
_ttps://barcode.tec-it.com/en/ Тип_штрихкода ?data= Наше_содержимое_кода
Например, для распространенного типа Code-128, который кодирует строку «ABC-abc-1234» ссылка будет выглядеть как:
_ttps://barcode.tec-it.com/en/ Code128 ?data= ABC-abc-1234
Так что просто подклеиваем справа от последнего знака «равно» наши артикулы, заворачиваем всё в функцию ИЗОБРАЖЕНИЕ и получаем автоматизированное создание штрих-кодов для любого количества товаров:
Если нужен не линейный, а квадратный QR-код, то можно использовать другой бесплатный сайт — QR Code Generator:
Здесь ссылка для формирования QR-кода выглядит следующим образом:
ttps://api.qrserver.com/v1/create-qr-code/?data= Наши_данные &size=100×100
Опять же, подставляем туда адреса ячеек с исходными данными и получаем возможность массово клепать QR-коды в любом количестве:
Пример 2. Логотипы компаний
Ещё одна любопытная возможность — это автоматизированная загрузка логотипов известных компаний с помощью веб-сервиса https://logo.clearbit.com. Идея та же — подклеиваем адрес сайта нужной нам компании в конец ссылки — и получаем от сервиса изображение логотипа заданной компании, которое выводит на лист наша функция ИЗОБРАЖЕНИЕ:
Можно использовать в дашбордах по анализу рынка, для визуализации инвестиционного портфеля, в прайс-листах и т.д. — у кого на сколько фантазии хватит.
Пример 3. Нестандартные диаграммы
На сайте QuickChart.io можно в режиме онлайн-конструктора создавать разнообразные диаграммы по вашим данным и использовать их затем в ваших проектах. Нюанс в том, что помимо стандартных диаграмм (которые Excel и сам неплохо умеет строить), здесь есть несколько весьма симпатичных нестандартных, например, спидометры и прогресс-бары, диаграммы Санкей для визуализации связей и т.д.
Для получения, например, круговой диаграммы-шкалы прогресса, нам потребуется сформировать вот такую веб-ссылку:
И визуализировать её затем с помощью нашей новой функции ИЗОБРАЖЕНИЕ (IMAGE) :
Или можно, например, сформировать и вывести изображение вот такого нарядного спидометра со стрелкой:
Здесь ссылка чуть похитрее, т.к. в неё уже зашиты размеры трёх диапазонов шкалы и их цвета, а также положение стрелки:
Названия цветов для заливки сегментов диаграммы можно взять из справочника.
Пример 4. Скриншоты сайтов
Если вы работаете с интернет-ресурсами или в области SEO-SMM, то вам пригодится возможность быстро формировать скриншоты заданных веб-страниц и вставлять их на лист Excel в виде изображений. Это можно сделать с помощью веб-сервиса https://mini.s-shot.ru, добавив к его адресу ссылку на интересующую страницу.
Подставляем её в нашу функцию ИЗОБРАЖЕНИЕ и получаем:
При большом количестве ссылок и скриншотов вся эта история может первый раз ощутимо подтормаживать — не пугайтесь.
Пример 5. Географические карты
Опять же, вставлять на лист интерактивные географические карты Excel уже давно умеет (через Вставка — Карты Bing, например), но можно это сделать и с помощью новой функции ИЗОБРАЖЕНИЕ, если найти веб-сервис, который по заданным координатам или названию населенного пункта будет формировать для нас скриншот карты в нужном месте. Обычно такие сервисы платные и используют API — специальный механизм запросов для общения с ними.
Например, на одном из таких сайтов Geoapify.com ссылка для получения нужного фрагмента карты случае формируется из:
- долготы (longtitude)
- широты (latitude)
- коэффициента масштаба (zoom)
- ключа API — длинного буквенно-цифрового кода, который вы получаете в личном кабинете в качестве вашего личного идентификатора:
Обратите внимание, что в качестве разделителя между целой и дробной частью должна быть использована точка, поэтому дополнительно придётся использовать функцию ПОДСТАВИТЬ (SUBSTITUTE), чтобы её подменить.
Аналог функции IMAGE на VBA
Если приведенные выше примеры вас заинтриговали, но последней версии Excel у вас пока нет, то можно относительно легко склепать на коленке упрощенный аналог этой функции на Visual Basic. Только вставлять картинки он будет не по ссылке из интернета, а по пути к файлу с жесткого диска вашего ПК (что в некоторых случаях может быть даже предпочтительнее — интернет у нас не везде бывает).
Откройте редактор макросов сочетанием клавиш Alt + F11 или кнопкой Visual Basic на вкладке Разработчик (Developer) . Вставьте новый пустой модуль командой меню Insert — Module и скопируйте туда текст следующего макроса:
Теперь останется ввести в ячейки листа пути к файлам картинок, а затем выделить эти ячейки и запустить наш макрос командой Разработчик — Макросы (Developer — Macros) или сочетанием клавиш Alt + F8 . Макрос пробежит по всем выделенным ячейкам и вставит в каждую картинку из указанного файла, вписав её в по размерам в каждую ячейку.
Картинки, правда, будут не внутри ячеек, как в случае с функцией IMAGE, а — как обычно — в отдельном слое, так что изменение высоты строк не приведёт к автоматической подгонке размеров изображений.
Если нужно будет подгонять размер картинок не по высоте, а по ширине ячеек, то замените в коде свойство Height на Width.
Форматы данных в электронных таблицах. Форматирование ячеек
В табличных процессах предусматриваются разные форматы представления данных. Форматы определяют типы данных электронной таблицы:
- ? Общий — это ячейки могут содержать как текстовую, так и цифровую информацию.
- ? Числовой — для цифровой информации.
- ? Денежный — для отображения денежных величин в заранее заданной пользователем валюте.
- ? Финансовый — для отображения денежных величин с выравниванием по разделителю и дробной части.
- ? Дата.
- ? Время.
- ? Процентный.
- ? Дробный
- ? Экспоненциальный.
- ? Текстовый.
Форматирование в Excel применяется для облегчения восприятия данных, что играет немаловажную роль в производительности труда.
Форматирование в Exсle осуществляеться с помощью формул.
Можно изменить формат текста, шрифт, размер шрифта,цвет,видооизменение
Графическое отображение данных в электронных таблицах. Графически электронные данные представляют в виде диаграмм и графиков
Диаграмма — изображение, рисунок, чертёж) — графическое представление данных, позволяющее быстро оценить соотношение нескольких величин. Представляет собой геометрическое символьное изображение информации с применением различных приёмов техники визуализации
Типы диаграмм
Диаграммы в основном состоят из геометрических объектов (точек, линий, фигур различной формы и цвета) и вспомогательных элементов (осей координат, условных обозначений, заголовков и т. п.). Также диаграммы делятся на плоскостные (двумерные) и пространственные (трёхмерные или объёмные). Сравнение и сопоставление геометрических объектов на диаграммах может происходить по различным измерениям: по площади фигуры или её высоте, по местонахождению точек, по их густоте, по интенсивности цвета и т. д. Кроме того, данные могут быть представлены в прямоугольной или полярной системе координат.
Графики — это метод условных изображений при помощи линий, точек, геометрических фигур и других символов.
График и график с маркерами
График с накоплением и график с накоплением с маркерами
Нормированный график с накоплением и нормированный график с накоплением с маркерами
Типы данных в редакторе электронных таблиц MS Excel
При всем многообразии информации, которая может быть введена в ячейки электронных таблиц создаваемых в MS Excel, их, тем не менее, можно разделить на три группы, три основных типа данных excel:
- Формулы
- Текстовые значения
- Числовые значения
Программа сама определяет какой тип данных содержится в конкретной ячейке.
Тип данных: Текстовые значения MS Excel
Текстовые значения — самый общий тип данных в экселе. По большому счету этим типом данных является всё, что программа не может определить как формулу или число. Любое значение помеченное как текстовое, используется excel «как есть» — его нельзя умножить, сложить или поделить как число, а также как-то изменить с помощью вычислений.
Все текстовые ячейки выравниваются по левому краю, все числовые по правому
Текстовые значения после их ввода в пустую ячейку автоматически выравниваются по левой границе ячейки. Именно так можно проще всего определить, что введенные вами данные воспринимаются MS Excel как текстовое значение.
Если ваше текстовое значение (к примеру фраза) не поместилось в ячейку, то оно размещается поверх соседних, расположенных справа ячеек при условии, что они свободны. Если в соседние ячейки введены данные, то длинная строка текста будет обрываться на границе занятой ячейки. Текст от этого, конечно же, никуда не денется и станет виден целиком, если вы поставите курсор на эту ячейку.
Если текст вылезает за пределы ячейки, имеет смысл «упаковать» его с помощью инструмента Перенос текста
Впрочем, вы можете воспользоваться инструментом «Перенести текст» (на вкладке «Главная» в группе «Выравнивание») щелкнув его, когда выделена ячейка с длинным текстом, и тогда он поместится в неё целиком, просто «не влезающая» его часть будет перенесена на следующую строку.
Тип данных: Числовые значения MS Excel
Числовые значения, в отличие от текстовых, можно и складывать и умножать и вообще, применять к ним весь богатый арсенал экселевских средств по обработке данных. После ввода в пустую ячейку MS Excel, числовые значения выравнивается по правой границе ячейки.
Фактически, к числовым типам данных относятся:
- сами числа (и целые и дробные и отрицательные и даже записанные в виде процентов)
- дата и время
Несколько особенностей числовых типов данных
Если введенное число не помещается в ячейку, то оно будет представлено в экспоненциальной форму представления, здорово пугающей неподготовленных пользователей. Например, гигантское число 4353453453453450 х 54545 в ячейку будет записано в виде 2,37459Е+20. Но, как правило в «жизни» появление «странных чисел» в ячейках excel свидетельствует о простой ошибке.
Если число или дата не помещается в ячейку целиком, вместо цифр в ней появляются символы ###. В этом случае «лечение» ещё более простое — нужно просто увеличить ширину столбца таблицы.
Иногда есть необходимостью записать число как текст, например в случае записи всевозможных артикулов товаров и т.п. дело в том, что если вы запишите 000335 в ячейку, Excel посчитав это значение числом, сразу же удалит нули, превратив артикул в 335. Чтобы этого не произошло, просто поместите число в кавычки — это будет сигналом для Excel, что содержимое ячейки надо воспринимать как текст, то есть выводить также, как его ввел пользователь. Естественно, производить с таким числом математических операций нельзя.
Что представляет собой дата в MS Excel?
Если с числами все более-менее понятно, то даты имеют несколько особенностей, о которых стоит упомянуть. Для начала, что такое «дата» с точки зрения MS Excel? На самом деле все не так уж и просто.
Дата в Excel — это число дней, отсчитанных до сегодняшнего дня, от некой начальной даты. По умолчанию этой начальной датой считается 1 января 1900 года.
А что же текущее время? Ещё интереснее — за точку отсчета каждых суток берется 00:00:00, которое представляется как 1. А дальше, эта единичка уменьшается, по мере того как уменьшается оставшееся в сутках время. Например 12.00 дня это с точки зрения MS Excel 0,5 (прошла половина суток), а 18.00 — 0,25 (прошли 3 четверти суток).
В итоге, дата 17 июня 2019 года, 12:30, «языком экселя» выглядит как 43633 (17.06.19) + 0,52 (12:30), то есть число 43633,52.
Как превратить число в текст? Поместите его в кавычки!
Тип данных: Формулы MS Excel
Если содержимое ячейки начинается со знака «=» (впрочем, также можно начинать со знака «+» или «-«), то значение этой ячейки MS Excel воспринимается как формула. Формула представляет собой не итоговое значение введенное в ячейку, а результат выполнения каких-то действий над значениями других ячеек.
Это важно понимать — в ячейке с формулой содержаться не сами данные, а только результат вычислений. Иными словами, если мы складываем значения ячеек А (5) и Б (7), то в ячейке В с формулой, будет содержатся не итоговое число 13 (хотя именно результат 13 и будет выводится), а сумма значений А и Б (5+7).
Работают формулы просто: сперва вы инициируете формулу («=»), затем указываете ячейку из которой берутся данные для вычислений (щелкаете в неё левой кнопкой мыши), действие которое нужно выполнить (вписываете знак сложения, умножения, вычитания, степени и т.п.), затем ещё одну ячейку с данными и нажимаете Enter.
Простейший пример формулы в Excel. Формулы — один из трех основных типов данных в Excel
В итоге, получится простейшая формула, мгновенно производящая нужное вычисление. Порядок выполнения действий в формуле соответствует
принятым в математике правилам: сначала умножение и деление, затем сложение и вычитание. Действия в скобках выполняются вперед всех других.
Впрочем, даже указывать ячейки с источниками данных не обязательно, если инициировать формулу («=»), а затем ввести в ту же ячейку данные «вручную», например 2+2, то после нажатия Enter в ячейке окажется результат вычисления, т.е. число 4.
Именно за формулы мы и любим редактор Excel. Нам нужно только ввести исходные данные и указать что с чем нужно вычитать и складывать, а уж программа сделает все автоматически.