This isn't really specific to 802.15.4, but is rather a fundamental concept of how radio works. What you're really asking is, essentially, "what is modulation?"
1. Let's talk about radio using...what? DC?!
Modulation is one of those concepts that is very difficult to get a handle on, but also appears outwardly simple. You're probably used to thinking about digital signals. They make sense, they're easy. Take a GPIO pin, for example, with >3V (or whatever) as logic high, and <1.2V as logic low. Or just 5V and 0V. This makes sense, but also corrupts our understanding of how radio transmission works.
Let's look at this digital signal like a HAM would. It's more complex. It's not just a simple on or off. Instead, we have our signal, and the thing that is carrying it. In this case, that carrier is DC. It's modulating a DC carrier 'wave'. At least, we can choose to look at it that way.
The carrier is just that - the thing that carries the signal. Or put another way, the carrier is what is being manipulated, or rather, modulated, to convey the information that makes up our signal. The signal is the actual 0s and 1s we want to send. Don't confuse the world signal to mean the physical current, or in the case of a radio, the physical radio waves that an antenna picks up. That is not the signal. The signal is the information we are somehow imprinting on the carrier via modulation.
For our digital signal, a straightforward DC signal carries it. Imagine it is a constant 5V potential. If we did nothing, it would simply be 5V and always 5V and great as power supply but terrible for communication.
So, we modulate it. We decide that we are going to pull the voltage down, to 0V or nearly so, at times we want to represent a '0', and let it return to 5V at times we want to convey a 1. Simple, right?
Well, no. That's not enough. We need to know how long we might pull down the signal, or let it remain high, per bit. Otherwise, how are we to know if something was '0' vs '00'. They will look the same, the only difference is one will be low for longer. This is where modulation gets complicated.
Even for DC, digital logic, there are many solutions to this. One is using an additional channel that conveys timing information - a clock -, which divides up our signal channel into nice little bits and to quote the peasant from blizzard's first game, 'Job Done'.
There are ways to do this by encoding timing information in the signal line itself, as is done with RS232 (Serial ports), or the PCIE bus. The breadth of modulation schemes is huge.
So how does this relate to radio?
Radio is exactly the same as the above, the only difference is the carrier isn't DC - its a wave. An electromagnetic wave. And, just like the above digital logic signals, the simplest modulation is simply turning it on and off.
2. Flashlights - The poorman's fractional Petahertz radio
Imagine a flashlight. Or as I like to think of them, fractional Petahertz radios. They transmit electromagnetic waves, just like a radio. The waves are in generally in the 430-770THz band, but they're still the same stuff that a radio emits. Now, a very low datarate human modulation that has worked well is morse code. Rather than turning the flash light on or off to encode information, we encode information using symbols, which themselves are made up of sequences of being 'on' delimited by times off, and the relative lengths of the 'on' pulses and their order encode the information. In this way, no timing signal is needed, as the information relies purely on relative durations to other parts of itself.
Well, we do the same thing with radio. The radio frequency is just the 'color' of the 'light'. Keeping with our flashlight metaphor, we could map various RF frequencies or bands to colors on the rainbow, and we are blinking flash lights of those colors to communicate. This lets you blink many flashlights at once, so many that you see nothing but noise, all blending together to nearly white.
So you put on glasses with the correct color filter, which filters out all the other colors except the one you are interested in. Suddenly, you can see the flashlight that you want to see flashing, and not the others.
That's all radio is. We just can't see it, and the waves are generally large in wavelength (as big as mountains, to fractions of millimeters, unlike light which is hundreds of nanometers) and behave much more like waves (their quantized nature is mostly hidden) which is why antennas and what not can get kind of weird. But the reflector cup of a flashlight is, from one perspective, a high gain directional antenna for the light. Indeed, many radio antennas operate entirely on reflection, like a satellite dish.
So, the 2.4GHz band is simply the 'color' of the radio wave being used to carry (or rather, is the thing being controlled/manipulated/modulated to encode) our information. That 2MHz sine wave isn't a sine wive - its a series of sine half pulses, with the phase indicating a 1 or 0 of a chip. So it will not oscillate like a sine wave, but rather use snippets of one pieced together in a way that results in discontinuities. But that's not really important to your question.
The 2MHz is the rate that your blinking the flashlight. The 2.4GHz is the color of the light you're blinking. If you and a receiver agree upon a color, or in this case, frequency, then as long as you transmit at that frequency (or within a range of nearby frequencies, forming a band), and the receiver has an oscillator tuned to the same frequency as yours, then you can filter out everything else and look just for that 'color' of radio.
3. Now it gets hard.
It's really that simple. But this very important conceptual model is easily and quickly obscured by how complicated this can really get, especially when you start doing the math. Add in the extremely clever but also unintuitive modulation schemes we have now, and it gets even worse. The turning a signal on and off is one modulation, called ASK (amplitude shift keying) which simply encodes the signal by variation in amplitude of the signal (turning it on and off, or brightening and dimming it).
The radio you're looking at has several modulations used, but that part of the standard you quoted is talking about the offset quadrature modulation. This takes two different signals (remembering that the signal is NOT the radiowaves, just the information we want to send), each 2MHz, but out of phase. It multiplies them together, yielding a single signal. This, ultimately, is used to brighten and dim 'light' of the color '2.4GHz' at that lower 2MHz signal frequency. The output of PSK (phase shift keying) equivalent to AM (amplitude modulation) in terms of the actual radio waves. It's all just selectively brightening or dimming the 2.4GHz carrier.
Now, we usually use a 'band', which is a range of colors, and this whole chip thing is done entirely to spread the spectrum. It's designed to seem random to other radios, but is something predictable to our receiver, so by randomizing the color very quickly, it will cause much less interference issues for people using a constant color, or others using the same colors but randomized in a different way. The rate at which this occurs, or really, any scheme's fundamental method of modulating the color, or changing the color, or both, must be fixed. In your case, it is that fixed 2MHz rate that we are messing with the 2.4GHz colored transmitter. This, being the base that everything else is built upon, is the aptly named baseband. It has little to do with color (frequency) being used to carry it, and is simply the rate at which we are messing with the carrier. By 'little to do with' I mean, they are not intrinsically coupled, but usually the baseband is chosen for very good reasons, which will take into account the slice of the RF spectrum being utilized, and a hundred other considerations.
See? I said it got complicated fast :). And you can encode information in non-amplitude ways, like having a flashlight that is always on but changes its color instead to convey information. That's called frequency modulation. But that's a whole 'nother can of worms.
