Ограничения столбцов и таблиц
Последнее обновление: 26.11.2021
При определении столбцов и таблиц для них можно задать ограничения. Ограничения позволяют настроить поведение столбцов и таблиц. Ограничения столбцов указываются после типа столбца:
имя_столбца тип_столбца ограничения_столбца
Ограничения таблицы указываются после определения всех столбцов.
Рассмотрим, какие ограничения столбцов мы можем использовать.
PRIMARY KEY
Атрибут PRIMARY KEY задает первичный ключ таблицы. Первичный ключ уникально идентифицирует строку в таблице. Например:
CREATE TABLE users
(
id INTEGER PRIMARY KEY,
name TEXT,
age INTEGER
);
Здесь столбец id выступает в качестве первичного ключа, он будет уникально идентифицировать строку и его значение должно быть уникальным. То есть у нас не может
быть таблице users более одной строки, где в столбце id было бы одно и то же значение.
Установка первичного ключа на уровне таблицы:
CREATE TABLE users
(
id INTEGER,
name TEXT,
age INTEGER,
PRIMARY KEY(id)
);
Первичный ключ может быть составным. Такой ключ использовать сразу несколько столбцов, чтобы уникально идентифицировать строку в таблице. Например:
CREATE TABLE users
(
id INTEGER,
name TEXT,
age INTEGER,
PRIMARY KEY(id, name)
);
В данном случае в качестве первичного ключа выступает связка столбцов id и name. То есть в таблице users не может быть двух строк, где для обоих из этих полей одновременно были бы одни и те же значения.
AUTOINCREMENT
Ограничение AUTOINCREMENT позволяет указать, что значение столбца будет автоматически увеличиваться при добавлении новой строки.
Данное ограничение работает для столбцов, которые представляют тип INTEGER с ограничением PRIMARY KEY:
DROP TABLE users;
CREATE TABLE users
(
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT,
age INTEGER
);
В данном случае значение столбца id каждой новой добавленной строки будет увеличиваться на единицу.
UNIQUE
Ограничение UNIQUE указывает, что столбец может хранить только уникальные значения.
CREATE TABLE users
(
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT,
age INTEGER,
email TEXT UNIQUE
);
Также мы можем определить это ограничение на уровне таблицы:
CREATE TABLE users
(
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT,
age INTEGER,
email TEXT,
UNIQUE (name, email)
);
NULL и NOT NULL
По умолчанию любой столбец, если он не представляет первичный ключ, может принимать значение NULL, то есть фактически отсутствие формального
значения. Но если мы хотим запретить подобное поведение и установить, что столбец обязательно должен иметь какое-либо значение, то
для него следует установить ограничение NOT NULL:
CREATE TABLE users
(
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
age INTEGER
);
В данном случае столбец name не допускает значение NULL.
DEFAULT
Ограничение DEFAULT определяет значение по умолчанию для столбца.
Если при добавлении данных для столбца не будет предусмотрено значение, то для него будет использоваться значение по умолчанию.
CREATE TABLE users
(
id INTEGER PRIMARY KEY,
name TEXT,
age INTEGER DEFAULT 18
);
Здесь столбец age в качестве значения по умолчанию имеет число 18.
CHECK
Ограничение CHECK задает ограничение для диапазона значений, которые могут храниться в столбце. Для этого после CHECK
указывается в скобках условие, которому должен соответствовать столбец или несколько столбцов. Например, возраст пользователей не может быть меньше 0 или
больше 100:
CREATE TABLE users
(
id INTEGER PRIMARY KEY,
name TEXT NOT NULL CHECK(name !=''),
age INTEGER NOT NULL CHECK(age >0 AND age < 100)
);
Кроме проверки возраста здесь также проверяется, что столбец name не может иметь пустую строку в качестве значения (пустая строка не эквивалентна
значению NULL).
Для соединения условий используется ключевое слово AND. Условия можно задать в виде операций сравнения больше (>), меньше (<), не равно (!=).
Также CHECK можно использовать на уровне таблицы:
CREATE TABLE users
(
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
age INTEGER NOT NULL,
CHECK ((age >0 AND age < 100) AND (name !=''))
);
Оператор CONSTRAINT. Установка имени ограничений
С помощью оператора CONSTRAINT можно задать имя для ограничений. Они указываются после ключевого слова CONSTRAINT перед ограничениями на уровне таблицы:
CREATE TABLE users
(
id INTEGER,
name TEXT NOT NULL,
email TEXT NOT NULL,
age INTEGER NOT NULL,
CONSTRAINT users_pk PRIMARY KEY(id),
CONSTRAINT user_email_uq UNIQUE(email),
CONSTRAINT user_age_chk CHECK(age >0 AND age < 100)
);
В данном случае ограничение для PRIMARY KEY называется users_pk, для UNIQUE — user_phone_uq, а для CHECK — user_age_chk. Смысл установки
имен ограничений заключается в том, что впоследствии через эти имена мы сможем управлять ограничениями — удалять или изменять их.
НазадВперед
Как разделить текст через запятую в Google Таблицах
В этом случае Google Таблицы не так умны, как Microsoft Excel. Хотя вы можете разделить все тексты через запятую, вы не можете сохранить исходный столбец. Если вас это устраивает, выполните следующие действия.
- Выделите все ячейки в вашей электронной таблице.
- Перейдите в Данные> Разделить тексты на столбцы.
- Если хотите, измените разделитель.
Сначала вам нужно выделить все ячейки, содержащие значения, разделенные запятыми. После этого разверните Данные меню и выберите Разделить текст на столбцы вариант.
По умолчанию он автоматически определяет разделитель или ограничитель. Если вы используете что-то уникальное или оно не выполняет свою работу безупречно, вы можете расширить Разделитель раскрывающийся список и выберите что-нибудь еще в соответствии с вашими требованиями.
Вот и все!
Как создавать базу данных и вставлять различные данные
Создание базы данных в SQLite – это очень просто, но процесс требует того, чтобы вы немного разбирались в том, что такое SQL. Давайте взглянем на код, который создаст базу данных для хранения музыкальных альбомов:
Python
import sqlite3
conn = sqlite3.connect(«mydatabase.db») # или :memory: чтобы сохранить в RAM
cursor = conn.cursor()
# Создание таблицы
cursor.execute(«»»CREATE TABLE albums
(title text, artist text, release_date text,
publisher text, media_type text)
«»»)
|
1 2 3 4 5 6 7 8 9 10 |
importsqlite3 conn=sqlite3.connect(«mydatabase.db»)# или :memory: чтобы сохранить в RAM cursor=conn.cursor() cursor.execute(«»»CREATE TABLE albums (title text, artist text, release_date text, «»») |
Сначала нам нужно импортировать модуль sqlite3 и создать связь с базой данных. Вы можете передать название файла или просто использовать специальную строку “:memory:” для создания базы данных в памяти. В нашем случае, мы создаем его на диске в файле под названием mydatabase.db.
Далее мы создаем объект cursor, который позволяет нам взаимодействовать с базой данных и добавлять записи, помимо всего прочего. Здесь мы используем синтаксис SQL для создания таблицы под названием альбомы с пятью следующими полями: title, artist, release_date, publisher и media_type. SQLite поддерживает только пять типов данных: null, integer, real, text и blob. Давайте напишем этот код и вставим кое-какие данные в нашей новой таблице. Запомните, если вы запускаете команду CREATE TABLE, при этом база данных уже существует, вы получите сообщение об ошибке.
Python
# Вставляем данные в таблицу
cursor.execute(«»»INSERT INTO albums
VALUES (‘Glow’, ‘Andy Hunter’, ‘7/24/2012’,
‘Xplore Records’, ‘MP3’)»»»
)
# Сохраняем изменения
conn.commit()
# Вставляем множество данных в таблицу используя безопасный метод «?»
albums = [(‘Exodus’, ‘Andy Hunter’, ‘7/9/2002’, ‘Sparrow Records’, ‘CD’),
(‘Until We Have Faces’, ‘Red’, ‘2/1/2011’, ‘Essential Records’, ‘CD’),
(‘The End is Where We Begin’, ‘Thousand Foot Krutch’, ‘4/17/2012’, ‘TFKmusic’, ‘CD’),
(‘The Good Life’, ‘Trip Lee’, ‘4/10/2012’, ‘Reach Records’, ‘CD’)]
cursor.executemany(«INSERT INTO albums VALUES (?,?,?,?,?)», albums)
conn.commit()
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
# Вставляем данные в таблицу cursor.execute(«»»INSERT INTO albums VALUES (‘Glow’, ‘Andy Hunter’, ‘7/24/2012’, ) conn.commit() albums=(‘Exodus’,’Andy Hunter’,’7/9/2002′,’Sparrow Records’,’CD’), (‘Until We Have Faces’,’Red’,’2/1/2011′,’Essential Records’,’CD’), (‘The End is Where We Begin’,’Thousand Foot Krutch’,’4/17/2012′,’TFKmusic’,’CD’), (‘The Good Life’,’Trip Lee’,’4/10/2012′,’Reach Records’,’CD’) cursor.executemany(«INSERT INTO albums VALUES (?,?,?,?,?)»,albums) conn.commit() |
Здесь мы использовали команду INSERT INTO SQL чтобы вставить запись в нашу базу данных
Обратите внимание на то, что каждый объект находится в одинарных кавычках. Это может усложнить работу, если вам нужно вставить строчки, которые содержат одинарные кавычки
В любом случае, чтобы сохранить запись в базе данных, нам нужно создать её. Следующая часть кода показывает, как добавить несколько записей за раз при помощи метода курсора executemany. Обратите внимание на то, что мы используем знаки вопроса (?), вместо строк замещения (%) чтобы вставить значения. Обратите внимание, что использование строки замещения не безопасно, так как может стать причиной появления атаки инъекций SQL . Использование знака вопроса намного лучше, а использование SQLAlchemy тем более, так как он делаете все необходимое, чтобы уберечь вас от правки встроенных одинарных кавычек на то, что SQLite в состоянии принимать.
Подсчет уникальных текстовых значений.
Если ваш список содержит как числа так и текст, и вы хотите посчитать только уникальные текстовые строки, добавьте функцию ЕТЕКСТ() в формулу массива, описанную выше:
Функция ЕТЕКСТ возвращает ИСТИНА, если исследуемое содержимое ячейки является текстом, и ЛОЖЬ в противоположном случае. Поскольку звездочка (*) в формулах массива работает как оператор И, то функция ЕСЛИ возвращает 1, только если рассматриваемое одновременно текстовое и уникальное, в противном случае получаем 0. И после того, как функция СУММ сложит все числа, вы получите количество уникальных текстовых значений в указанном диапазоне.
Не забывайте нажимать , чтобы правильно ввести формулу массива, и вы получите результат, подобный этому:
Рис3
Как вы можете видеть на скриншоте выше, мы получили общее количество уникальных текстовых значений, исключая пустые ячейки, числа, логические выражения ИСТИНА и ЛОЖЬ, а также ошибки.
Выборка всех данных
Чтобы извлечь данные из БД выполним инструкцию SELECT, а затем воспользуемся методом fetchall() объекта курсора для сохранения значений в переменной. При этом переменная будет являться списком, где каждая строка из БД будет отдельным элементом списка. Далее будет выполняться перебор значений переменной и печатать значений.
Код будет таким:
Также можно использовать fetchall() в одну строку:
Если нужно извлечь конкретные данные из БД, воспользуйтесь предикатом WHERE. Например, выберем идентификаторы и имена тех сотрудников, чья зарплата превышает 800. Для этого заполним нашу таблицу большим количеством строк, а затем выполним запрос.
Можете использовать оператор INSERT для заполнения данных или ввести их вручную в программе браузера БД.
Теперь, выберем имена и идентификаторы тех сотрудников, у кого зарплата больше 800:
В приведенном выше операторе SELECT вместо звездочки (*) были указаны атрибуты id и name.
Важно помнить!
Теги <thead> и <tfoot> нужны не всегда. Бывают таблицы без шапки и подвала.
Если нет смысла группировать основную часть таблицы, то можно обойтись и без тега <tbody>, но мы рекомендуем не делать так. Тег даёт больше контроля над структурой таблицы и стилизацией, а также приучает действовать последовательно.
К тому же браузер всё равно подставит этот тег сам, а всегда полагаться на браузеры — значит оставить место для возможных ошибок.
Пример:
| 1 | Мавзалеев И. В. | 10.09.1992 |
| 2 | Киреева А. Ю. | 02.05.1996 |
| 3 | Корнеев И. Ю. | 09.10.1990 |
| 4 | Тресков В. А. | 25.03.1993 |
| 5 | Ибрагимов А. Е. | 15.10.1994 |
| 6 | Борисенко Д. С. | 10.10.1991 |
В таблице выше шесть строк, каждая из которых содержит три ячейки. Здесь нет важных для смысла признаков, по которым можно сгруппировать содержимое таблицы. Поэтому будет достаточно тега <caption> (придумаем его и скроем) и тега <tbody>, а вот теги <thead> и <tfoot> можно опустить.
Выделение цветом уникальных значений в столбце.
Всякий раз, когда вам нужно выделить что-либо в Excel на основе определенного условия, перейдите прямо к функции условного форматирования. Более подробная информация и примеры приведены ниже.
Самый быстрый и простой способ выделить уникальные значения в Excel — применить встроенное правило условного форматирования:
- Выберите столбец данных, в котором вы хотите выделить уникальные.
- На вкладке Главная в группе Стили щелкните Условное форматирование > Правила выделения ячеек > Повторяющиеся значения …
- В диалоговом окне « Повторяющиеся значения » выберите «Уникальный в левом поле и выберите желаемое форматирование в правом поле, затем нажмите « ОК» .
Совет. Если вас не устраивает какой-либо из предопределенных форматов, щелкните «Пользовательский формат …» (последний элемент в раскрывающемся списке) и установите цвет заливки и / или шрифта по своему вкусу.
Как видите, выделение уникальных значений в Excel — самая простая задача, которую можно себе представить. Однако встроенное правило Excel работает только для элементов, которые появляются в списке только один раз. Если вам нужно выделить различные значения — уникальные и первые вхождения дубликатов — то придется создать собственное правило на основе формулы.
Вам также потребуется создать настраиваемое правило для выделения уникальных строк на основе значений в одном или нескольких столбцах.
Как создать правило для условного форматирования уникальных значений?
Чтобы выделить уникальные или различные значения в столбце, выберите диапазон ячеек без заголовка столбца (вы же не хотите, чтобы заголовок выделялся, не так ли?) Затем создайте правило условного форматирования с помощью формулы.
Чтобы создать правило условного форматирования на основе формулы, выполните следующие действия:
- Перейдите на вкладку «Главная » и щелкните « Условное форматирование» > « Новое правило» > «Использовать формулу», чтобы с ее помощью определить, какие ячейки нужно форматировать .
- Введите формулу в поле «Форматировать значения …».
- Нажмите кнопку «Формат …» и выберите нужный цвет заливки и/или цвет шрифта.
- Наконец, нажмите кнопку ОК , чтобы применить правило.
Более подробные инструкции см. в статье: Как создать правила условного форматирования Excel на основе другого значения ячейки .А теперь поговорим о том, какие формулы использовать и в каких случаях.
Выделяем цветом отдельные уникальные значения.
Чтобы выделить значения, которые появляются в списке только один раз, используйте следующую формулу:
Где A2 — первая, а A10 — последняя ячейка диапазона.
Чтобы выделить все различные значения в столбце, то есть встречающиеся хотя бы однажды, используйте это выражение:
Где A2 — самая верхняя ячейка диапазона.
Как выделить строку с уникальным значением в одном столбце.
Чтобы выделить целые строки на основе уникальных значений в определенном столбце, используйте формулы, которые мы использовали в предыдущем примере, но применяйте правило ко всей таблице, а не к одному столбцу.
На следующем скриншоте показано, как выглядит правило, выделяющее строки на основе уникальных значений в столбце A:
Как видите, формула
та же самая, что и раньше, но строка в диапазоне выделена вся.
А можно использовать и такое выражение:
Результат будет таким же.
Как выделить уникальные строки?
Если вы хотите выделить строки на основе значений в двух или более столбцах, используйте функцию СЧЁТЕСЛИМН, которая позволяет указать несколько критериев в одной формуле.
Чтобы выделить уникальные строки:
Чтобы выделить различные строки:
Знакомство с семантической вёрсткой
Семантический подход к вёрстке подразумевает использование HTML-тегов в соответствии с их семантикой (предназначением), а его суть заключается в верности выбора тегов и их взаимного расположения.
Семантические теги передают смысл (или обозначают важность) содержащегося в них контента. Семантический подход — противоположность визуальному, при котором важно только то, как HTML-страница выглядит
Семантический подход — противоположность визуальному, при котором важно только то, как HTML-страница выглядит. Почему семантика так важна
Почему семантика так важна
Она повышает доступность контента. Тогда его лучше понимают:
- поисковые роботы (чем понятнее для них контент сайта, тем корректнее он представлен в поисковой выдаче и тем проще его найти пользователям);
- браузеры и помощники для пользователей с ограниченными возможностями (например, программы, которые читают информацию с экрана, — скринридеры).
Семантически верно размеченный контент может выглядеть абсолютно так же, как и свёрстанный без учёта семантики. Это касается любых элементов на HTML-странице.
Так, можно использовать для всех них тег <div>, но он не обладает семантикой, никак не обозначает смысл своего содержимого. Поэтому мы применяем для заголовков теги H1… H6, для таблицы — <table>, <caption>, <thead>, <tbody>, <tfoot>, <th>. И так далее.
Для оформления страниц при семантической вёрстке применяют каскадные таблицы стилей (CSS).
Рассмотрим, какие теги отвечают за вёрстку таблиц, когда и зачем нужен каждый.
ВПР – сравниваем два списка ключевиков и находим упущенные фразы
Сложность: средняя.
Функция ВПР (или VLOOKUP) применяется для сравнения данных из двух диапазонов и вывода нужных значений (или поиска несоответствий).
Синтаксис:
=ВПР(запрос; диапазон поиска; номер_столбца; )
Применение
У нас запущены рекламные кампании в Яндекс.Директе и Google Ads. Задача – найти ключевые слова, которые используются в Google Ads, но не добавлены в кампании Директа.
Для этого выгружаем в Google Таблицу ключевики с Google Ads (столбец А) и Директа (столбец Е).
С помощью функции ВПР произведем поиск ключевиков из первого списка по второму списку. В параметрах функции указываем:
- ячейку, содержимое которой будем искать во втором столбце;
- диапазон, по которому будем производить поиск;
- номер столбца в диапазоне данных, по которому производим поиск. С ячеек этого столбца функция будет возвращать значения при совпадении поиска;
- отсортирован ли диапазон поиска (этот параметр необязателен, но мы указываем значение «0» – не отсортирован).
Функция будет последовательно брать значение ячейки в первом столбце и искать совпадение с ним в столбце Е. Если совпадение найдено, функция выведет это значение в ячейке столбца С. Если совпадений нет, отобразит «#Н/Д». Это значит, что данное ключевое слово используется в Google Ads, но отсутствует в Яндекс.Директе.
После этого нам останется лишь отфильтровать данные по значению «#Н/Д» в столбце С и добавить все полученные ключевые слова в Яндекс.Директ.
IMPORTRANGE – подтягиваем данные из других таблиц или листов
Сложность: средняя.
Функция IMPORTRANGE предназначена для импорта данных из одной Google-таблицы в другую.
Синтаксис:
=IMPORTRANGE(url_таблицы; диапазон )
С помощью этой функции можно собирать в одном месте данные из разных таблиц или листов: консолидированные отчеты, сводки и т.д.
Применение
У нас есть две таблицы: одна – с отчетом по контекстной рекламе, другая – по таргетированной. Мы хотим, чтобы данные отчета по таргетированной рекламе отображались на одном листе с отчетом по контексту.
Чтобы подтянуть данные из второй таблицы, используем функцию IMPORTRANGE. Первым аргументом функции указываем url таблицы, с которой будем брать данные. Вторым аргументом – название листа в этой таблице и диапазон данных.
Если у вас открыт доступ к таблице, данные сразу подтянутся на нужный лист. Если доступа нет, отобразится уведомление с предложением запросить доступ.
Прелесть такого способа в том, что при изменении данных в исходной таблице функция подтянет изменения и обновит данные в сводном листе.
Подсчет различных чисел.
Чтобы посчитать различные числовые значения (числа, даты и время), используйте функцию ЕЧИСЛО:
Считаем, сколько имеется различных чисел в диапазоне A2: A10:
Результат вы можете посмотреть ниже.
Это достаточно простое и элегантное решение, но работает оно гораздо медленнее, чем выражения, которые используют функцию ЧАСТОТА для подсчета уникальных значений. Если у вас большие наборы данных, то целесообразно переключиться на формулу, основанную на расчёте частот.
И вот еще один способ подсчета чисел:
Применительно к примеру ниже:
=СУММ(—(ЧАСТОТА(A2:A10; A2:A10)>0))
Как видите, здесь игнорируются записи, в которых имеются буквы.
Пошагово разберём, как это работает.
Функция ЧАСТОТА возвращает массив цифр, которые соответствуют интервалам, заданным имеющимися числами. В этом случае мы сравниваем один и тот же набор чисел для массива данных и для массива интервалов.
Результатом является то, что ЧАСТОТА() возвращает массив, который представляет собой счетчик для каждого числового значения в массиве данных.
Это работает, потому что ЧАСТОТА() возвращает ноль для любых чисел, которые ранее уже появились в списке. Ноль возвращается и для текстовых данных. Поэтому полученный массив выглядит следующим образом:
{3:0:0:2:0:0}
Как видите, обрабатываются только числа. Ячейки A7:A10 игнорируются, потому что там текст. А функция ЧАСТОТА() работает только с числами.
Теперь каждое из этих чисел проверяем на условие «больше нуля».
Получаем:
{ИСТИНА:ЛОЖЬ:ЛОЖЬ:ИСТИНА:ЛОЖЬ:ЛОЖЬ}
Теперь превращаем ИСТИНА и ЛОЖЬ в 1 и 0 соответственно. Делаем это при помощи двойного отрицания. Проще говоря, это двойной минус, который не меняет величину числа, но позволяет получить реальные числа, когда это вообще возможно:
{1:0:0:1:0:0}
А теперь функция СУММ складывает всё и получаем результат: 2.
GOOGLETRANSLATE – переводим ключевики с русского на английский (или любой другой язык)
Сложность: низкая.
Функция GOOGLETRANSLATE – Google Переводчик внутри Google Таблиц.
Синтаксис:
GOOGLETRANSLATE(текст; ; )
Применение
Мы хотим протестировать контекстную рекламу на англоязычном рынке. Чтобы не собирать отдельно англоязычную семантику, можно перевести текущую семантику из кампаний для Рунета с помощью функции GOOGLETRANSLATE.
Для этого выгружаем в Google Таблицу список ключевых слов. В соседнем столбце прописываем формулу для перевода с русского на английский:
=GOOGLETRANSLATE(A1;»ru»;»en»)
Затем выделяем столбец с переводом, копируем и вставляем в отдельный столбец как значения (Ctrl+Shift+V).
С помощью этой функции можно переводить на любые языки, поддерживаемые Google.
Фильтр для уникальных значений.
Чтобы просмотреть только уникальные или различные значения в списке, отфильтруйте их, выполнив следующие действия.
- Примените одну из приведенных выше формул для определения уникальных или различных ячеек или строк.
- Выберите диапазон и нажмите кнопку «Фильтр на вкладке «Данные».
- Щелкните стрелку фильтрации в заголовке столбца, содержащего формулу, и выберите то, что хотите просмотреть:
Как выбрать уникальные из фильтра.
Если у вас относительно небольшой список уникальных, вы можете просто выбрать их обычным способом с помощью мыши при нажатой клавише Ctrl. Если отфильтрованный список содержит сотни или тысячи строк, то для экономии времени вы можете использовать один из следующих способов.
Чтобы быстро выбрать весь получившийся список, включая заголовки столбцов, отфильтруйте уникальные значения, щелкните любую ячейку в получившемся списке, а затем нажмите .
Чтобы выбрать уникальные значения без заголовков столбцов, отфильтруйте их, выберите первую ячейку с данными и нажмите Ctrl + Shift + End, чтобы расширить выделение до последней ячейки.
Примечание. В некоторых редких случаях, в основном в очень больших книгах, рекомендованные выше комбинации клавиш могут выбирать как видимые, так и невидимые ячейки. Чтобы исправить это, нажмите сначала либо или же , а затем нажмите для выбора только видимых ячеек, игнорируя скрытые строки.
Если вам сложно запомнить такое количество комбинаций, используйте этот визуальный способ: выделите весь список, затем перейдите на вкладку «Главная» > «Найти и выделить» > «Выделить группу ячеек» и выберите «Только видимые ячейки.
Как скопировать уникальные значения в другое место?
Чтобы скопировать список на новое место, сделайте следующее:
- Выберите отфильтрованные значения с помощью мыши или вышеупомянутых комбинаций клавиш.
- Нажмите для копирования выбранных значений.
- Выберите верхнюю левую ячейку в целевом диапазоне (она может находиться на том же или другом листе) и нажмите Ctrl + V , чтобы вставить данные.
Извлечь уникальные значения из диапазона.
Формулы, которые мы описывали выше, позволяют сформировать список значений из данных определенного столбца. Но часто речь идет о нескольких столбцах, то есть о диапазоне данных. К примеру, вы получили несколько списков товаров из различных файлов и расположили их в соседних столбцах.
Используем формулу массива
Здесь A2:C9 обозначает диапазон, из которого вы хотите извлечь уникальные значения. E1 – это первая ячейка столбца, в который вы хотите поместить результат. $2:$9 указывает на строки, содержащие данные, которые вы хотите использовать. $A:$C указывает на столбцы, из которых вы берёте исходные данные. Пожалуйста, измените их на свои собственные.
Нажмите , а затем перетащите маркер заполнения, чтобы вывести уникальные значения, пока не появятся пустые ячейки.
Как видите, извлекаются все уникальные и первые вхождения дубликатов.
Как извлечь значения, игнорируя пустые ячейки
Если исходный список содержит пустые ячейки, формула, которую мы только что обсудили, вернет ноль для каждой пустой строки, что может быть проблемой. Это вы и наблюдаете на скриншоте чуть выше. Чтобы исправить это, сделаем несколько небольших корректировок.
Формула массива для извлечения различных значений, исключая пустые ячейки:
Аналогичным образом вы можете получить список различных значений, исключая пустые ячейки и ячейки с числами:
Напоминаем, что в приведенных выше формулах A2: A13 – это исходный список, а B1 – ячейка прямо над первой позицией формируемого списка.
На этом скриншоте показан результат отбора:
Быть может, кому-то будет полезна еще одна формула –
Она работает с числами и текстом, игнорирует пустые ячейки.
Стиль Q & A
Ну, после нескольких часов исследования и борьбы с проблемой, я обнаружил, что есть два способа сделать это, в зависимости от структуры вашей таблицы и от того, есть ли у вас ограничения внешних ключей, активированные для поддержания целостности. Я хотел бы поделиться этим в чистом формате, чтобы сэкономить время людям, которые могут быть в моей ситуации.
Вариант 1: вы можете позволить себе удаление строки
Другими словами, у вас нет внешнего ключа или, если он у вас есть, ваш движок SQLite настроен так, что исключений целостности нет. Путь к этому — ВСТАВИТЬ OR ЗАМЕНИТЬ. Если вы пытаетесь вставить/обновить проигрыватель, идентификатор которого уже существует, движок SQLite удалит эту строку и вставит данные, которые вы предоставляете. Теперь возникает вопрос: что делать, чтобы сохранить старый идентификатор?
Допустим, мы хотим UPSERT с данными user_name = ‘steven’ и age = 32.
Посмотрите на этот код:
Хитрость в объединении. Он возвращает идентификатор пользователя ‘steven’, если таковой имеется, и в противном случае он возвращает новый свежий идентификатор.
Вариант 2: Вы не можете позволить себе удаление строки
После изучения предыдущего решения я понял, что в моем случае это может привести к уничтожению данных, поскольку этот идентификатор работает как внешний ключ для другой таблицы. Кроме того, я создал таблицу с предложением ON DELETE CASCADE, что означало бы, что она будет удалять данные без вывода сообщений. Dangerous.
Итак, я сначала подумал о предложении IF, но SQLite имеет только CASE. И этот КЕЙС не может быть использован (или, по крайней мере, мне это не удалось) для выполнения одного ОБНОВЛЕНИЯ запроса, если существует (выберите id из игроков, где имя пользователя = ‘steven’) и INSERT, если это не так. Нет идти.
И вот, наконец, я с успехом использовал грубую силу. Логика заключается в том, что для каждого UPSERT, который вы хотите выполнить, сначала выполните INSERT OR IGNORE, чтобы убедиться, что есть строка с нашим пользователь, а затем выполните запрос UPDATE с точно такими же данными, которые вы пытались вставить.
Те же данные, что и раньше: user_name = ‘steven’ и age = 32.
И это все!
Правка
Как прокомментировал Энди, попытка вставить сначала, а затем обновить может привести к срабатыванию триггеров чаще, чем ожидалось. На мой взгляд, это не проблема безопасности данных, но это правда, что запуск ненужных событий не имеет большого смысла. Следовательно, улучшенное решение будет:
