Создать программу в excel
VBA Excel. Начинаем программировать с нуля
Первое знакомство с редактором VBA Excel, создание процедур (подпрограмм) и написание простейшего кода, работающего с переменными и ячейками рабочего листа.
Знакомство с редактором VBA
- Создайте новую книгу Excel и сохраните ее как книгу с поддержкой макросов с расширением .xlsm. В старых версиях Excel по 2003 год – как обычную книгу с расширением .xls.
- Нажмите сочетание клавиш «левая_клавиша_Alt+F11», которое откроет редактор VBA. С правой клавишей Alt такой фокус не пройдет. Также, в редактор VBA можно перейти по ссылке «Visual Basic» из панели инструментов «Разработчик» на ленте быстрого доступа. Если вкладки «Разработчик» на ленте нет, ее следует добавить в настройках параметров Excel.
В левой части редактора VBA расположен проводник проекта, в котором отображены все открытые книги Excel. Верхней строки, как на изображении, у вас скорее всего не будет, так как это – личная книга макросов. Справа расположен модуль, в который записываются процедуры (подпрограммы) с кодом VBA. На изображении открыт модуль листа, мы же далее создадим стандартный программный модуль.
- Нажмите кнопку «Module» во вкладке «Insert» главного меню. То же подменю откроется при нажатии на вторую кнопку после значка Excel на панели инструментов.
После нажатия кнопки «Module» вы увидите ссылку на него, появившуюся в проводнике слева.
Первая программа на VBA Excel
Добавляем на стандартный модуль шаблон процедуры – строки ее начала и завершения, между которыми мы и будем писать свою первую программу (процедуру, подпрограмму).
- Откройте стандартный модуль двойным кликом по его ссылке в проводнике. Поместите в него курсор и нажмите кнопку «Procedure…» во вкладке «Insert» главного меню. Та же ссылка будет доступна при нажатии на вторую кнопку после значка Excel на панели инструментов.
В результате откроется окно добавления шаблона процедуры (Sub).
- Наберите в поле «Name» имя процедуры: «Primer1», или скопируйте его отсюда и вставьте в поле «Name». Нажмите кнопку «OK», чтобы добавить в модуль первую и последнюю строки процедуры.
Имя процедуры может быть написано как на латинице, так и на кириллице, может содержать цифры и знак подчеркивания. Оно обязательно должно начинаться с буквы и не содержать пробелы, вместо которых следует использовать знак подчеркивания.
- Вставьте внутрь шаблона процедуры следующую строку: MsgBox “Привет” .
Функция MsgBox выводит информационное сообщение с указанным текстом. В нашем примере – это «Привет».
- Проверьте, что курсор находится внутри процедуры, и запустите ее, нажав клавишу «F5». А также, запустить процедуру на выполнение можно, нажав на треугольник (на изображении под пунктом меню «Debug») или на кнопку «Run Sub/UserForm» во вкладке «Run» главного меню редактора VBA Excel.
Если вы увидели такое сообщение, как на изображении, то, поздравляю – вы написали свою первую программу!
Работа с переменными
Чтобы использовать в процедуре переменные, их необходимо объявить с помощью ключевого слова «Dim». Если при объявлении переменных не указать типы данных, они смогут принимать любые доступные в VBA Excel значения. Комментарии в тексте процедур начинаются со знака «’» (апостроф).
Пример 2
Присвоение переменным числовых значений:
Источник: vremya-ne-zhdet.ru
Создание приложения Canvas из Excel в Power Apps Create a canvas app from Excel in Power Apps
В этом разделе вы создадите первое приложение Canvas в Power Apps, используя данные из таблицы Excel. In this topic, you’ll create your first canvas app in Power Apps using data from an Excel table. Выберите файл Excel, создайте приложение, а затем запустите созданное приложение. You’ll select an Excel file, create an app, and then run the app that you create. Каждое созданное приложение включает экраны для просмотра записей, отображения сведений о записи и создания или обновления записей. Every created app includes screens to browse records, show record details, and create or update records. Вы можете быстро создать приложение, использующее данные Excel, а затем настроить его так, как вам требуется. By generating an app, you can quickly get a working app using Excel data, and then you can customize the app to better suit your needs.
Файл Excel должен находиться в облачной учетной записи хранения, например OneDrive, Dropbox или Google Диск. The Excel file must be in a cloud-storage account, such as OneDrive, Google Drive, or Dropbox. В этой статье используется OneDrive для бизнеса. This topic uses OneDrive for Business.
Если у вас нет лицензии на Power Apps, вы можете зарегистрироваться бесплатно. If you don’t have a license for Power Apps, you can sign up for free.
Технические условия Prerequisites
Для точного выполнения инструкций в этой статье скачайте файл FlooringEstimates в Excel и сохраните его в облачной учетной записи хранения. To follow this topic exactly, download the Flooring Estimates file in Excel, and save it in your cloud storage account.
Вы можете использовать собственный файл Excel, однако данные в нем должны быть отформатированы в виде таблицы. You can use your own Excel file, but the data must be formatted as a table. Дополнительные сведения см. в разделе Форматирование таблицы. For more information, see Format a table.
Создание приложения Create the app
В области Создавайте собственные приложения наведите указатель на плитку Начать с данных и выберите команду Создать это приложение. Under Make your own app, hover over Start from data, and then select Make this app.
В области Создать на основе своих данных выберите Макет телефона на плитке своей облачной учетной записи хранения. Under Start with your data, click or tap Phone layout on the tile for your cloud-storage account.
Если появится запрос, нажмите Подключиться и предоставьте данные этой учетной записи. If prompted, click or tap Connect, and provide your credentials for that account.
В разделе Choose an Excel file (Выбор файла Excel) найдите файл FlooringEstimates.xlsx и выберите его. Under Choose an Excel file, browse to FlooringEstimates.xlsx, and then click or tap it.
В разделе Choose a table (Выбор таблицы) щелкните FlooringEstimates, а затем нажмите кнопку Подключить. Under Choose a table, click or tap FlooringEstimates, and then click or tap Connect.
Запуск приложения Run the app
Откройте режим предварительного просмотра, нажав клавишу F5 (либо нажав значок воспроизведения в правом верхнем углу). Open Preview by pressing F5 (or by clicking or tapping the play icon near the upper-right corner).
Измените порядок сортировки, нажав значок сортировки в правом верхнем углу. Toggle the sort order by clicking or tapping the sort icon near the upper-right corner.
Отфильтруйте список, введя или вставив один или несколько символов в поле поиска. Filter the list by typing or pasting one or more characters in the search box.
Например, введите или вставьте honeytoken , чтобы отобразить единственную запись, для которой эта строка отображается в названии продукта, категории или обзоре. For example, type or paste Honey to show the only record for which that string appears in the product’s name, category, or overview.
Добавить запись: Add a record:
Выберите значок “плюс”. Select the plus icon.
Добавьте необходимые данные, а затем щелкните значок флажка, чтобы сохранить изменения. Add whatever data you want, and then select the checkmark icon to save your changes.
Изменить запись: Edit a record:
Выберите стрелку для записи, которую требуется изменить. Select the arrow for the record that you want to edit.
Выберите значок карандаша. Select the pencil icon.
Обновите одно или несколько полей, а затем щелкните значок флажка, чтобы сохранить изменения. Update one or more fields, and then select the checkmark icon to save your changes.
В качестве альтернативы щелкните значок Отмена, чтобы отменить изменения. As an alternative, select the cancel icon to discard your changes.
Удаление записи. Delete a record:
Щелкните стрелку “Далее” для записи, которую необходимо удалить. Select the next arrow for the record that you want to delete.
Щелкните значок корзины. Select the trash icon.
Дальнейшие действия Next steps
Настройте экрана обзора по умолчанию в соответствии со своими потребностями. Customize the default browse screen to better suit your needs. Например, можно сортировать и фильтровать список только по имени продукта, а не по категории или обзору. For example, you can sort and filter the list by product name only, not category or overview.
Источник: docs.microsoft.com
Создание расчетных программ в Excel
1. Создаем сам алгорим вычисления и необходимые формулы в Excel
2. Проверяем работу программы – т.е. выполняем тестирование программы в Exсel
3. Подбираем картинку для фона – редактируем ее в Photoshop
4. Вставляем картинку фона в Excel
5. Тестируем и подправляем полученную программу – если в этом есть необходимость
1. Создаем сам алгорим вычисления и необходимые формулы в Exсel
1.1 Создадим программу для вычисления необходимого количества штучных материалов – для покрытия кровли
Исходные данные:
– обычно известно размер здания – по крайней мере, его можно измерить
– форма кровли выбирается исходя из визуальных предпочтений
– материал кровли выбирается исходя из имеющийся информации и толщины кошелька
– выбирается окончательный вид кровли – односкатная, двухскатная, четырехскатная и т.д.-
в любом случае кровля состоит из участков имеющих форму: треугольник, квадрат, параллелограмм
– количество материала необходимое для покрытия отдельного участка кровли зависит от:
размер отдельного участка в плане здания;
геометрический вид участка кровли;
уклон кровли;
размер штучного материала;
площадь эффективного перекрытия единицы штучного материала;
– суммарная площадь всех участков кровли и общая потребность в материалах верхнего покрытия с учетом отходов.
1.2 Предположим размер здания в плане 12 х 9 м (исходя из модуля 3 м)
1.3 Форма кровли – четырехскатная (вальмовая) – т.е. все четыре ската имеют наклон щипцы расположены в 4,5 м от торцов здания, карнизные свесы 250 мм, выход крайнего листа за лобовую доску 150мм, кровля без мансардная, высота от конька до чердачного перекрытия 4,5 м
1.4 Материал кровли – волнистые асбестоцементные листы – размеров 1750 х 1125, площадь эффективного перекрытия 1,5 м2
смежное перекрытие по длине листа 250 мм (сверху и по низу листа) и боковое перекрытие 125 мм ( с двух сторон)
1.5 Уклон скатов кровли определим расчетно – исходя из проекции скатов на чердачное перекрытие
Для того чтобы немного усложнить задачу – предположим что конек кровли смещен в сторону главного фасада –
то есть находится на расстоянии 3+6 (если смотреть в плане)
1.6 Создаем формулы:
A – ширина здания в плане
L – длина здания в плане
H – заданная высота кровли
C – длина ската
K – величина карнизных свесов
S – площадь скатов здания
B1 – расстояние от стены до конька по скату 1 (проекция)
B2 – расстояние от стены до конька по скату 2 (проекция)
F1 – расстояние от стены до конька по боковому скату 1 (проекция)
F2 – расстояние от стены до конька по боковому скату 2 (проекция)
1.7 Выводим все это на листе – вводим формулы.
2. Проверяем все работает
3. Через поиск Яндекса находим нужную по теме картинку – вставляем ее в Photoshop на новый слой – прозрачность слоя задаем от 10 до 20%
сохраняем картинку на жесткий диск компьютера.
4. В Excel, через “Разметка страницы” -> “Вставить фон” = картинку вставляем на выбранный лист с программой
5. Проверяем и при необходимости правим созданную программу.
6. По образу и подобию – есть возможность создавать не сложные программы для повседневных вычислений
7. Есть возможность немного усложнить программу – тем самым расширив функционал. Делается это через создание полей с выпадающими списками – в них будет возможность выбрать заранее заданные значения. И еще как вариант – можно задать выбор данных, через применение функции “ЕСЛИ”
8. Программу лучше сохранить в двух вариантах – один “только для чтения” и второй “рабочий вариант” – так больше вероятность, что вы ее
не измените до не рабочего состояния, после первого же запуска. Как сделать только для чтения? В проводнике на файле программы – кликаем правой кнопкой мыши и в контекстном меню выбираем “Свойства”. В атрибутах файла ставим галочку в поле “Только чтение”
и нажимаем “Применить”. Работать лучше на копии – но там глочку только чтение необходимо снимать.
9. Для работы – необходимо подставить свои данные в не выделенные поля и получить результат.
Источник: pc.krognet.com
Создание расчетных программ в Excel
1. Создаем сам алгорим вычисления и необходимые формулы в Excel
2. Проверяем работу программы – т.е. выполняем тестирование программы в Exсel
3. Подбираем картинку для фона – редактируем ее в Photoshop
4. Вставляем картинку фона в Excel
5. Тестируем и подправляем полученную программу – если в этом есть необходимость
1. Создаем сам алгорим вычисления и необходимые формулы в Exсel
1.1 Создадим программу для вычисления необходимого количества штучных материалов – для покрытия кровли
Исходные данные:
– обычно известно размер здания – по крайней мере, его можно измерить
– форма кровли выбирается исходя из визуальных предпочтений
– материал кровли выбирается исходя из имеющийся информации и толщины кошелька
– выбирается окончательный вид кровли – односкатная, двухскатная, четырехскатная и т.д.-
в любом случае кровля состоит из участков имеющих форму: треугольник, квадрат, параллелограмм
– количество материала необходимое для покрытия отдельного участка кровли зависит от:
размер отдельного участка в плане здания;
геометрический вид участка кровли;
уклон кровли;
размер штучного материала;
площадь эффективного перекрытия единицы штучного материала;
– суммарная площадь всех участков кровли и общая потребность в материалах верхнего покрытия с учетом отходов.
1.2 Предположим размер здания в плане 12 х 9 м (исходя из модуля 3 м)
1.3 Форма кровли – четырехскатная (вальмовая) – т.е. все четыре ската имеют наклон щипцы расположены в 4,5 м от торцов здания, карнизные свесы 250 мм, выход крайнего листа за лобовую доску 150мм, кровля без мансардная, высота от конька до чердачного перекрытия 4,5 м
1.4 Материал кровли – волнистые асбестоцементные листы – размеров 1750 х 1125, площадь эффективного перекрытия 1,5 м2
смежное перекрытие по длине листа 250 мм (сверху и по низу листа) и боковое перекрытие 125 мм ( с двух сторон)
1.5 Уклон скатов кровли определим расчетно – исходя из проекции скатов на чердачное перекрытие
Для того чтобы немного усложнить задачу – предположим что конек кровли смещен в сторону главного фасада –
то есть находится на расстоянии 3+6 (если смотреть в плане)
1.6 Создаем формулы:
A – ширина здания в плане
L – длина здания в плане
H – заданная высота кровли
C – длина ската
K – величина карнизных свесов
S – площадь скатов здания
B1 – расстояние от стены до конька по скату 1 (проекция)
B2 – расстояние от стены до конька по скату 2 (проекция)
F1 – расстояние от стены до конька по боковому скату 1 (проекция)
F2 – расстояние от стены до конька по боковому скату 2 (проекция)
1.7 Выводим все это на листе – вводим формулы.
2. Проверяем все работает
3. Через поиск Яндекса находим нужную по теме картинку – вставляем ее в Photoshop на новый слой – прозрачность слоя задаем от 10 до 20%
сохраняем картинку на жесткий диск компьютера.
4. В Excel, через “Разметка страницы” -> “Вставить фон” = картинку вставляем на выбранный лист с программой
5. Проверяем и при необходимости правим созданную программу.
6. По образу и подобию – есть возможность создавать не сложные программы для повседневных вычислений
7. Есть возможность немного усложнить программу – тем самым расширив функционал. Делается это через создание полей с выпадающими списками – в них будет возможность выбрать заранее заданные значения. И еще как вариант – можно задать выбор данных, через применение функции “ЕСЛИ”
8. Программу лучше сохранить в двух вариантах – один “только для чтения” и второй “рабочий вариант” – так больше вероятность, что вы ее
не измените до не рабочего состояния, после первого же запуска. Как сделать только для чтения? В проводнике на файле программы – кликаем правой кнопкой мыши и в контекстном меню выбираем “Свойства”. В атрибутах файла ставим галочку в поле “Только чтение”
и нажимаем “Применить”. Работать лучше на копии – но там глочку только чтение необходимо снимать.
9. Для работы – необходимо подставить свои данные в не выделенные поля и получить результат.
Источник: pc.krognet.com
Как работать в Excel с таблицами для чайников: пошаговая инструкция
Программа Microsoft Excel удобна для составления таблиц и произведения расчетов. Рабочая область – это множество ячеек, которые можно заполнять данными. Впоследствии – форматировать, использовать для построения графиков, диаграмм, сводных отчетов.
Работа в Экселе с таблицами для начинающих пользователей может на первый взгляд показаться сложной. Она существенно отличается от принципов построения таблиц в Word. Но начнем мы с малого: с создания и форматирования таблицы. И в конце статьи вы уже будете понимать, что лучшего инструмента для создания таблиц, чем Excel не придумаешь.
Как создать таблицу в Excel для чайников
Работа с таблицами в Excel для чайников не терпит спешки. Создать таблицу можно разными способами и для конкретных целей каждый способ обладает своими преимуществами. Поэтому сначала визуально оценим ситуацию.
Посмотрите внимательно на рабочий лист табличного процессора:
Это множество ячеек в столбцах и строках. По сути – таблица. Столбцы обозначены латинскими буквами. Строки – цифрами. Если вывести этот лист на печать, получим чистую страницу. Без всяких границ.
Сначала давайте научимся работать с ячейками, строками и столбцами.
Как выделить столбец и строку
Чтобы выделить весь столбец, щелкаем по его названию (латинской букве) левой кнопкой мыши.
Для выделения строки – по названию строки (по цифре).
Чтобы выделить несколько столбцов или строк, щелкаем левой кнопкой мыши по названию, держим и протаскиваем.
Для выделения столбца с помощью горячих клавиш ставим курсор в любую ячейку нужного столбца – нажимаем Ctrl + пробел. Для выделения строки – Shift + пробел.
Как изменить границы ячеек
Если информация при заполнении таблицы не помещается нужно изменить границы ячеек:
- Передвинуть вручную, зацепив границу ячейки левой кнопкой мыши.
- Когда длинное слово записано в ячейку, щелкнуть 2 раза по границе столбца / строки. Программа автоматически расширит границы.
- Если нужно сохранить ширину столбца, но увеличить высоту строки, воспользуемся кнопкой «Перенос текста» на панели инструментов.
Для изменения ширины столбцов и высоты строк сразу в определенном диапазоне выделяем область, увеличиваем 1 столбец /строку (передвигаем вручную) – автоматически изменится размер всех выделенных столбцов и строк.
Примечание. Чтобы вернуть прежний размер, можно нажать кнопку «Отмена» или комбинацию горячих клавиш CTRL+Z. Но она срабатывает тогда, когда делаешь сразу. Позже – не поможет.
Чтобы вернуть строки в исходные границы, открываем меню инструмента: «Главная»-«Формат» и выбираем «Автоподбор высоты строки»
Для столбцов такой метод не актуален. Нажимаем «Формат» – «Ширина по умолчанию». Запоминаем эту цифру. Выделяем любую ячейку в столбце, границы которого необходимо «вернуть». Снова «Формат» – «Ширина столбца» – вводим заданный программой показатель (как правило это 8,43 – количество символов шрифта Calibri с размером в 11 пунктов). ОК.
Как вставить столбец или строку
Выделяем столбец /строку правее /ниже того места, где нужно вставить новый диапазон. То есть столбец появится слева от выделенной ячейки. А строка – выше.
Нажимаем правой кнопкой мыши – выбираем в выпадающем меню «Вставить» (или жмем комбинацию горячих клавиш CTRL+SHIFT+”=”).
Отмечаем «столбец» и жмем ОК.
Совет. Для быстрой вставки столбца нужно выделить столбец в желаемом месте и нажать CTRL+SHIFT+”=”.
Все эти навыки пригодятся при составлении таблицы в программе Excel. Нам придется расширять границы, добавлять строки /столбцы в процессе работы.
Пошаговое создание таблицы с формулами
- Заполняем вручную шапку – названия столбцов. Вносим данные – заполняем строки. Сразу применяем на практике полученные знания – расширяем границы столбцов, «подбираем» высоту для строк.
- Чтобы заполнить графу «Стоимость», ставим курсор в первую ячейку. Пишем «=». Таким образом, мы сигнализируем программе Excel: здесь будет формула. Выделяем ячейку В2 (с первой ценой). Вводим знак умножения (*). Выделяем ячейку С2 (с количеством). Жмем ВВОД.
- Когда мы подведем курсор к ячейке с формулой, в правом нижнем углу сформируется крестик. Он указываем на маркер автозаполнения. Цепляем его левой кнопкой мыши и ведем до конца столбца. Формула скопируется во все ячейки.
- Обозначим границы нашей таблицы. Выделяем диапазон с данными. Нажимаем кнопку: «Главная»-«Границы» (на главной странице в меню «Шрифт»). И выбираем «Все границы».
Теперь при печати границы столбцов и строк будут видны.
С помощью меню «Шрифт» можно форматировать данные таблицы Excel, как в программе Word.
Поменяйте, к примеру, размер шрифта, сделайте шапку «жирным». Можно установить текст по центру, назначить переносы и т.д.
Как создать таблицу в Excel: пошаговая инструкция
Простейший способ создания таблиц уже известен. Но в Excel есть более удобный вариант (в плане последующего форматирования, работы с данными).
Сделаем «умную» (динамическую) таблицу:
- Переходим на вкладку «Вставка» – инструмент «Таблица» (или нажмите комбинацию горячих клавиш CTRL+T).
- В открывшемся диалоговом окне указываем диапазон для данных. Отмечаем, что таблица с подзаголовками. Жмем ОК. Ничего страшного, если сразу не угадаете диапазон. «Умная таблица» подвижная, динамическая.
Примечание. Можно пойти по другому пути – сначала выделить диапазон ячеек, а потом нажать кнопку «Таблица».
Теперь вносите необходимые данные в готовый каркас. Если потребуется дополнительный столбец, ставим курсор в предназначенную для названия ячейку. Вписываем наименование и нажимаем ВВОД. Диапазон автоматически расширится.
Если необходимо увеличить количество строк, зацепляем в нижнем правом углу за маркер автозаполнения и протягиваем вниз.
Как работать с таблицей в Excel
С выходом новых версий программы работа в Эксель с таблицами стала интересней и динамичней. Когда на листе сформирована умная таблица, становится доступным инструмент «Работа с таблицами» – «Конструктор».
Здесь мы можем дать имя таблице, изменить размер.
Доступны различные стили, возможность преобразовать таблицу в обычный диапазон или сводный отчет.
Возможности динамических электронных таблиц MS Excel огромны. Начнем с элементарных навыков ввода данных и автозаполнения:
- Выделяем ячейку, щелкнув по ней левой кнопкой мыши. Вводим текстовое /числовое значение. Жмем ВВОД. Если необходимо изменить значение, снова ставим курсор в эту же ячейку и вводим новые данные.
- При введении повторяющихся значений Excel будет распознавать их. Достаточно набрать на клавиатуре несколько символов и нажать Enter.
- Чтобы применить в умной таблице формулу для всего столбца, достаточно ввести ее в одну первую ячейку этого столбца. Программа скопирует в остальные ячейки автоматически.
- Для подсчета итогов выделяем столбец со значениями плюс пустая ячейка для будущего итога и нажимаем кнопку «Сумма» (группа инструментов «Редактирование» на закладке «Главная» или нажмите комбинацию горячих клавиш ALT+”=”).
Если нажать на стрелочку справа каждого подзаголовка шапки, то мы получим доступ к дополнительным инструментам для работы с данными таблицы.
Иногда пользователю приходится работать с огромными таблицами. Чтобы посмотреть итоги, нужно пролистать не одну тысячу строк. Удалить строки – не вариант (данные впоследствии понадобятся). Но можно скрыть. Для этой цели воспользуйтесь числовыми фильтрами (картинка выше). Убираете галочки напротив тех значений, которые должны быть спрятаны.
Источник: exceltable.com
5 полезных функций Excel для начинающих программистов
Работу в табличном редакторе Excel тоже можно автоматизировать и запрограммировать. Вы сможете здорово помочь своим бухгалтерам с отчётами, маркетологам — с анализом рекламы, а сами научитесь разбираться в алгоритмах.
Мы разберём 5 полезных функций Excel. Если вы думаете, что Excel — это только про цифры и таблички, то вот вам видео, где в Excel запрограммировали настоящую 3D-игру.
Чтобы было проще разобраться, все формулы и примеры мы записали в Excel-файл, который можно скачать и использовать для экспериментов. Каждый пример — на отдельной вкладке снизу.
Это одна из самых важных функций в Excel. Как и в настоящем языке программирования, она проверяет какое-то условие, и если оно выполняется — пишет в ячейку что-то одно, а если нет — пишет что-то другое.
В общем виде она выглядит так:
В условии может быть что угодно: сравнение ячеек, другие формулы, сравнения и математические команды — всё, что вам нужно проверить.
На практике можно сделать, например, так: пусть Excel проверяет возраст, и пишет документ, который в этом возрасте удостоверяет личность. До 14 лет это свидетельство о рождении, а после — паспорт. Для этого используем такую команду в ячейке:
Программа проверит, что стоит в предыдущей ячейке, и если там число меньше 18, то напишет «Свидетельство о рождении», а если уже есть 18 лет, то «Паспорт»:
СЧЁТЕСЛИ и СЧЁТЕСЛИМН
Часто бывает нужно посчитать количество строк или заполненных ячеек по каким-то параметрам, например узнать количество работников старше 40 лет, или посчитать все строки, где есть слово «переменная». Для этого используют функцию СЧЁТЕСЛИ:
Диапазон — это группа ячеек, в которых мы будем что-то искать, а условие — это по какому параметру мы решим, что ячейка нам подходит.
Допустим мы ведём семейный бюджет и нам нужно узнать, сколько раз у нас были покупки больше 5000 рублей в одном чеке. Для этого используем такое:
Теперь посложнее. Если нам нужно посчитать количество ячеек по нескольким условиям одновременно, то используют функцию СЧЁТЕСЛИМН:
То, что в прямоугольных скобках — дополнительные условия, их может быть максимум 127, а сами скобки при этом ставить не нужно. Обратите внимание что для каждого нового условия можно задать свой диапазон. Функция проверит все условия, каждое в своём диапазоне, и если все условия одновременно выполняется — посчитает количество таких ячеек.
Для примера найдём все траты больше 5000 в одном чеке, за которые отвечает Миша:
Теперь у нас первый диапазон отвечает за сумму в чеке, а второй — за имена, поэтому и условия у каждого из них разные.
Функция возвращает максимальное значение из выбранного диапазона:
Давайте используем её, чтобы найти самую дорогую покупку в бюджете из предыдущего примера. Для этого запишем её так:
ПОИСКПОЗ
Поиск позиции часто используют, когда нужно найти положение определённого элемента в таблице:
Значение — то, что нам нужно найти в таблице, диапазон просмотра — где именно мы будем искать наше значение. Если в качестве диапазона укажем столбец с цифрами, то функция вернёт номер в этом столбце, где встречается нужная цифра.
Давайте найдём номер строки с максимальной покупкой:
Это число нам понадобится для следующей функции
ИНДЕКС
Эта функция возвращает значение конкретной ячейки в указанном диапазоне:
=ИНДЕКС(диапазон; номер_строки; [номер_столбца])
Если в диапазон ячеек входит только один столбец, достаточно будет указать только номер строки. Когда в выбранном диапазоне получается два столбца и больше, можно будет выбрать его номер, чтобы точно обозначить ячейку.
Попробуем в нашем примере с бюджетом найти того, кто сделал самую большую покупку. Мы уже знаем номер строки, в которой записана самая большая сумма, и на этой же строке записан тот, кто эту сумму потратил. Используем это:
Когда Excel встретит такую формулу, он возьмёт наш диапазон из второго столбца, затем возьмёт номер строки с самой большой покупкой (она у нас в ячейке Е10, нашли в прошлом примере), и определит, что самую крупную покупку сделал Миша.
Что дальше
Вы уже знаете достаточно для того, чтобы вести в Excel свой семейный или личный бюджет. Используйте наши формулы и анализируйте полученные данные. В следующей статье мы расскажем о том, как менять внешний вид ячеек и поговорим о новых функциях.
Источник: thecode.media